Code only works in Debug mode. Why?


A

Ayo

Can someone tell me why the code below only works when I run it in debug
mode, but gives me all zero when I try to run it directly from the
commandbutton click event?

Sub Regional()
Dim c As Range, c1 As Range, rngBO As Range, rngReg As Range
Dim BOReportWS As Worksheet, Regws As Worksheet
Dim BOReport_lastRow As Integer, Regws_lastRow As Integer, i As Integer
Dim startRow As Integer, endRow As Integer, Regws_startRow As Integer,
Regws_endRow As Integer
Dim AAVM_sRow As Integer, AAVM_eRow As Integer
Dim regionName As String

Worksheets("Regional Summaries").Visible = True
Set Regws = Worksheets("Regional Summaries")
Regws_lastRow = Regws.Range("B65536").End(xlUp).Row
Set BOReportWS = Worksheets("BO Download")
BOReport_lastRow = BOReportWS.Range("A65536").End(xlUp).Row

For i = 1 To 4
Select Case i
Case 1
regionName = "CENTRAL"
startRow = firstRow(regionName)
endRow = lastRow(startRow)
Regws_startRow = 4
Regws_endRow = Regional_lastRow(4)
Case 2
regionName = "NORTHEAST"
startRow = firstRow(regionName)
endRow = lastRow(startRow)
Regws_startRow = Regws_endRow + 1
Regws_endRow = Regional_lastRow(Regws_startRow)
Case 3
regionName = "SOUTH"
startRow = firstRow(regionName)
endRow = lastRow(startRow)
Regws_startRow = Regws_endRow + 1
Regws_endRow = Regional_lastRow(Regws_startRow)
Case 4
regionName = "WEST"
startRow = firstRow(regionName)
endRow = lastRow(startRow)
Regws_startRow = Regws_endRow + 1
Regws_endRow = Regws_lastRow
End Select

Set rngReg = BOReportWS.Range("A" & startRow & ":A" & endRow)
AAVM_sRow = Regws_startRow

For Each c In Regws.Range("B" & Regws_startRow & ":B" &
Regws_endRow).Cells
If c.Row <> Regws_endRow Then
c.Offset(0, 2).Value = Evaluate("=SUMPRODUCT(--('BO Download'!$D$" &
startRow & ":$D$" & endRow & "=C" & c.Row & "),--('BO Download'!$E$" &
startRow & ":$E$" & endRow & "=B" & c.Row & ")" & ")")
'c.Offset(0, 2).Value = Evaluate("=SUMPRODUCT(--('BO Download'!$D$"
& startRow & ":$D$" & endRow & "=" & c.Offset(0, 1) & "),--('BO
Download'!$E$" & startRow & ":$E$" & endRow & "=" & c & "))")
'c.Offset(0, 3) = Evaluate("=SUMPRODUCT(--('BO Download'!$D$" &
startRow & ":$D$" & endRow & "<>" & "" & "),--('BO Download'!$E$" & startRow
& ":$E$" & endRow & "<>" & "") & ")"
c.Offset(0, 4) = Evaluate("=SUMPRODUCT(--('BO Download'!$D$" &
startRow & ":$D$" & endRow & "=C" & c.Row & "),--('BO Download'!$E$" &
startRow & ":$E$" & endRow & "=B" & c.Row & "),--('BO Download'!$H$" &
startRow & ":$H$" & endRow & "=""A""))")
c.Offset(0, 5) = Evaluate("=SUMPRODUCT(--('BO Download'!$D$" &
startRow & ":$D$" & endRow & "=C" & c.Row & "),--('BO Download'!$E$" &
startRow & ":$E$" & endRow & "=B" & c.Row & "),--('BO Download'!$L$" &
startRow & ":$L$" & endRow & "=""A""))")
c.Offset(0, 6) = Evaluate("=SUMPRODUCT(--('BO Download'!$D$" &
startRow & ":$D$" & endRow & "=C" & c.Row & "),--('BO Download'!$E$" &
startRow & ":$E$" & endRow & "=B" & c.Row & "),--('BO Download'!$N$" &
startRow & ":$N$" & endRow & "=""A""))")
'c.Offset(0, 7) = cnt("D",c.Value, 2, startRow, endRow)
c.Offset(0, 8) = Evaluate("=SUMPRODUCT(--('BO Download'!$D$" &
startRow & ":$D$" & endRow & "=C" & c.Row & "),--('BO Download'!$E$" &
startRow & ":$E$" & endRow & "=B" & c.Row & "),--('BO Download'!$P$" &
startRow & ":$P$" & endRow & "=""A""))")
'c.Offset(0, 9) = cnt("D",c.Value, 2, startRow, endRow)
'c.Offset(0, 10) = cnt("D",c.Value, 2, startRow, endRow)
c.Offset(0, 11) = Evaluate("=SUMPRODUCT(--('BO Download'!$D$" &
startRow & ":$D$" & endRow & "=C" & c.Row & "),--('BO Download'!$E$" &
startRow & ":$E$" & endRow & "=B" & c.Row & "),--('BO Download'!$R$" &
startRow & ":$R$" & endRow & "=""A"")" & ")")
c.Offset(0, 12) = Evaluate("=SUMPRODUCT(--('BO Download'!$D$" &
startRow & ":$D$" & endRow & "=C" & c.Row & "),--('BO Download'!$E$" &
startRow & ":$E$" & endRow & "=B" & c.Row & "),--('BO Download'!$T$" &
startRow & ":$T$" & endRow & "=""A"")" & ")")
c.Offset(0, 13) = Evaluate("=SUMPRODUCT(--('BO Download'!$D$" &
startRow & ":$D$" & endRow & "=C" & c.Row & "),--('BO Download'!$E$" &
startRow & ":$E$" & endRow & "=B" & c.Row & "),--('BO Download'!$X$" &
startRow & ":$X$" & endRow & "=""A"")" & ")")
c.Offset(0, 14) = Evaluate("=SUMPRODUCT(--('BO Download'!$D$" &
startRow & ":$D$" & endRow & "=C" & c.Row & "),--('BO Download'!$E$" &
startRow & ":$E$" & endRow & "=B" & c.Row & "),--('BO Download'!$Z$" &
startRow & ":$Z$" & endRow & "=""A"")" & ")")
c.Offset(0, 15) = Evaluate("=SUMPRODUCT(--('BO Download'!$D$" &
startRow & ":$D$" & endRow & "=C" & c.Row & "),--('BO Download'!$E$" &
startRow & ":$E$" & endRow & "=B" & c.Row & "),--('BO Download'!$AB$" &
startRow & ":$AB$" & endRow & "=""A"")" & ")")

ElseIf c.Row = Regws_endRow Then
If Right(c.Value, 7) = "VENDORS" Or Right(c.Value, 6) = "VENDOR" Then
AAVM_eRow = REGrng_eRow(AAVM_sRow)
c.Value = Evaluate("=SUMPRODUCT((B" & AAVM_sRow & ":B" &
AAVM_eRow & "<>"""")/COUNTIF(B" & AAVM_sRow & ":B" & AAVM_eRow & ",B" &
AAVM_sRow & ":B" & AAVM_eRow & "&""""))")
If c.Value < 2 Then
c.Value = c.Value & " VENDOR"
Else
c.Value = c.Value & " VENDORS"
End If
AAVM_sRow = AAVM_eRow + 2
End If
c.Offset(0, 2) = Application.WorksheetFunction.CountA(rngReg)
'c.Offset(0, 3) =
Application.WorksheetFunction.CountIf(BOReportWS.Range("H" & startRow & ":H"
& endRow), "A")
c.Offset(0, 4) =
Application.WorksheetFunction.CountIf(BOReportWS.Range("H" & startRow & ":H"
& endRow), "A")
c.Offset(0, 5) =
Application.WorksheetFunction.CountIf(BOReportWS.Range("L" & startRow & ":L"
& endRow), "A")
c.Offset(0, 6) =
Application.WorksheetFunction.CountIf(BOReportWS.Range("N" & startRow & ":N"
& endRow), "A")
'c.Offset(0, 7) = cnt("E",c.Value, 2, startRow, endRow)
c.Offset(0, 8) =
Application.WorksheetFunction.CountIf(BOReportWS.Range("P" & startRow & ":p"
& endRow), "A")
'c.Offset(0, 9) = cnt(c.Value, 2, startRow, endRow)
'c.Offset(0, 10) = cnt(c.Value, 2, startRow, endRow)
c.Offset(0, 11) =
Application.WorksheetFunction.CountIf(BOReportWS.Range("R" & startRow & ":R"
& endRow), "A")
c.Offset(0, 12) =
Application.WorksheetFunction.CountIf(BOReportWS.Range("T" & startRow & ":T"
& endRow), "A")
c.Offset(0, 13) =
Application.WorksheetFunction.CountIf(BOReportWS.Range("X" & startRow & ":X"
& endRow), "A")
c.Offset(0, 14) =
Application.WorksheetFunction.CountIf(BOReportWS.Range("Z" & startRow & ":Z"
& endRow), "A")
c.Offset(0, 15) =
Application.WorksheetFunction.CountIf(BOReportWS.Range("AB" & startRow &
":AB" & endRow), "A")
End If
Next c
Next i
Worksheets("Regional Summaries").Visible = False
End Sub
 
Ad

Advertisements

D

Dave Peterson

You've started a few threads based on the same question--and you've had some
responses at your other threads that you haven't said are good or bad.
 
P

Peter T

It looks like Ayo is using the web interface and probably cannot see any
replies that were sent from a Newsreader.

Regards,
Peter T
 
P

Peter T

There's still a problem with the newsgroups. Yesterday old NNTP posts seemed
to be trickling through again to the web interface, but they seemed to have
stopped again.

See thread "Visibility of newsgroup postings" from 19-Oct

Regards,
Peter T
 
D

Dave Peterson

I've seen that thread.

I understand how people who don't visit the newsgroups often use the web
interface.

For regulars (who aren't blocked by overzealous IT folks and their firewalls),
I'm don't understand why those regulars don't use a newsreader.



Peter said:
There's still a problem with the newsgroups. Yesterday old NNTP posts seemed
to be trickling through again to the web interface, but they seemed to have
stopped again.

See thread "Visibility of newsgroup postings" from 19-Oct

Regards,
Peter T
 
Ad

Advertisements

P

Peter T

I've just checked again and our earlier posts (here) seem to have recently
arrived to the web interface, a significant improvement!

When I first started looking into this I was surprised to find out how many
use the web interface, roughly 50%, and who may not have seen the NNTP
replies.

Regards,
Peter T
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top