ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Code only works in Debug mode. Why? (https://www.excelbanter.com/excel-programming/435218-code-only-works-debug-mode-why.html)

Ayo
Code only works in Debug mode. Why?
 
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.R ange("H" & startRow & ":H"
& endRow), "A")
c.Offset(0, 4) =
Application.WorksheetFunction.CountIf(BOReportWS.R ange("H" & startRow & ":H"
& endRow), "A")
c.Offset(0, 5) =
Application.WorksheetFunction.CountIf(BOReportWS.R ange("L" & startRow & ":L"
& endRow), "A")
c.Offset(0, 6) =
Application.WorksheetFunction.CountIf(BOReportWS.R ange("N" & startRow & ":N"
& endRow), "A")
'c.Offset(0, 7) = cnt("E",c.Value, 2, startRow, endRow)
c.Offset(0, 8) =
Application.WorksheetFunction.CountIf(BOReportWS.R ange("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.R ange("R" & startRow & ":R"
& endRow), "A")
c.Offset(0, 12) =
Application.WorksheetFunction.CountIf(BOReportWS.R ange("T" & startRow & ":T"
& endRow), "A")
c.Offset(0, 13) =
Application.WorksheetFunction.CountIf(BOReportWS.R ange("X" & startRow & ":X"
& endRow), "A")
c.Offset(0, 14) =
Application.WorksheetFunction.CountIf(BOReportWS.R ange("Z" & startRow & ":Z"
& endRow), "A")
c.Offset(0, 15) =
Application.WorksheetFunction.CountIf(BOReportWS.R ange("AB" & startRow &
":AB" & endRow), "A")
End If
Next c
Next i
Worksheets("Regional Summaries").Visible = False
End Sub

Dave Peterson
Code only works in Debug mode. Why?
 
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.



Ayo wrote:

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.R ange("H" & startRow & ":H"
& endRow), "A")
c.Offset(0, 4) =
Application.WorksheetFunction.CountIf(BOReportWS.R ange("H" & startRow & ":H"
& endRow), "A")
c.Offset(0, 5) =
Application.WorksheetFunction.CountIf(BOReportWS.R ange("L" & startRow & ":L"
& endRow), "A")
c.Offset(0, 6) =
Application.WorksheetFunction.CountIf(BOReportWS.R ange("N" & startRow & ":N"
& endRow), "A")
'c.Offset(0, 7) = cnt("E",c.Value, 2, startRow, endRow)
c.Offset(0, 8) =
Application.WorksheetFunction.CountIf(BOReportWS.R ange("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.R ange("R" & startRow & ":R"
& endRow), "A")
c.Offset(0, 12) =
Application.WorksheetFunction.CountIf(BOReportWS.R ange("T" & startRow & ":T"
& endRow), "A")
c.Offset(0, 13) =
Application.WorksheetFunction.CountIf(BOReportWS.R ange("X" & startRow & ":X"
& endRow), "A")
c.Offset(0, 14) =
Application.WorksheetFunction.CountIf(BOReportWS.R ange("Z" & startRow & ":Z"
& endRow), "A")
c.Offset(0, 15) =
Application.WorksheetFunction.CountIf(BOReportWS.R ange("AB" & startRow &
":AB" & endRow), "A")
End If
Next c
Next i
Worksheets("Regional Summaries").Visible = False
End Sub

--

Dave Peterson

Peter T
Code only works in Debug mode. Why?
 
It looks like Ayo is using the web interface and probably cannot see any
replies that were sent from a Newsreader.

Regards,
Peter T

"Dave Peterson" wrote in message
...
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.



Ayo wrote:

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.R ange("H" & startRow &
":H"
& endRow), "A")
c.Offset(0, 4) =
Application.WorksheetFunction.CountIf(BOReportWS.R ange("H" & startRow &
":H"
& endRow), "A")
c.Offset(0, 5) =
Application.WorksheetFunction.CountIf(BOReportWS.R ange("L" & startRow &
":L"
& endRow), "A")
c.Offset(0, 6) =
Application.WorksheetFunction.CountIf(BOReportWS.R ange("N" & startRow &
":N"
& endRow), "A")
'c.Offset(0, 7) = cnt("E",c.Value, 2, startRow, endRow)
c.Offset(0, 8) =
Application.WorksheetFunction.CountIf(BOReportWS.R ange("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.R ange("R" & startRow &
":R"
& endRow), "A")
c.Offset(0, 12) =
Application.WorksheetFunction.CountIf(BOReportWS.R ange("T" & startRow &
":T"
& endRow), "A")
c.Offset(0, 13) =
Application.WorksheetFunction.CountIf(BOReportWS.R ange("X" & startRow &
":X"
& endRow), "A")
c.Offset(0, 14) =
Application.WorksheetFunction.CountIf(BOReportWS.R ange("Z" & startRow &
":Z"
& endRow), "A")
c.Offset(0, 15) =
Application.WorksheetFunction.CountIf(BOReportWS.R ange("AB" & startRow &
":AB" & endRow), "A")
End If
Next c
Next i
Worksheets("Regional Summaries").Visible = False
End Sub

--

Dave Peterson



Dave Peterson
Code only works in Debug mode. Why?
 
If that's true, then it's another reason not to use that web interface.

Peter T wrote:

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

Regards,
Peter T

"Dave Peterson" wrote in message
...
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.



Ayo wrote:

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.R ange("H" & startRow &
":H"
& endRow), "A")
c.Offset(0, 4) =
Application.WorksheetFunction.CountIf(BOReportWS.R ange("H" & startRow &
":H"
& endRow), "A")
c.Offset(0, 5) =
Application.WorksheetFunction.CountIf(BOReportWS.R ange("L" & startRow &
":L"
& endRow), "A")
c.Offset(0, 6) =
Application.WorksheetFunction.CountIf(BOReportWS.R ange("N" & startRow &
":N"
& endRow), "A")
'c.Offset(0, 7) = cnt("E",c.Value, 2, startRow, endRow)
c.Offset(0, 8) =
Application.WorksheetFunction.CountIf(BOReportWS.R ange("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.R ange("R" & startRow &
":R"
& endRow), "A")
c.Offset(0, 12) =
Application.WorksheetFunction.CountIf(BOReportWS.R ange("T" & startRow &
":T"
& endRow), "A")
c.Offset(0, 13) =
Application.WorksheetFunction.CountIf(BOReportWS.R ange("X" & startRow &
":X"
& endRow), "A")
c.Offset(0, 14) =
Application.WorksheetFunction.CountIf(BOReportWS.R ange("Z" & startRow &
":Z"
& endRow), "A")
c.Offset(0, 15) =
Application.WorksheetFunction.CountIf(BOReportWS.R ange("AB" & startRow &
":AB" & endRow), "A")
End If
Next c
Next i
Worksheets("Regional Summaries").Visible = False
End Sub

--

Dave Peterson

--

Dave Peterson

Peter T
Code only works in Debug mode. Why?
 
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

"Dave Peterson" wrote in message
...
If that's true, then it's another reason not to use that web interface.

Peter T wrote:

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

Regards,
Peter T

"Dave Peterson" wrote in message
...
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.



Ayo wrote:

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.R ange("H" & startRow
&
":H"
& endRow), "A")
c.Offset(0, 4) =
Application.WorksheetFunction.CountIf(BOReportWS.R ange("H" & startRow
&
":H"
& endRow), "A")
c.Offset(0, 5) =
Application.WorksheetFunction.CountIf(BOReportWS.R ange("L" & startRow
&
":L"
& endRow), "A")
c.Offset(0, 6) =
Application.WorksheetFunction.CountIf(BOReportWS.R ange("N" & startRow
&
":N"
& endRow), "A")
'c.Offset(0, 7) = cnt("E",c.Value, 2, startRow, endRow)
c.Offset(0, 8) =
Application.WorksheetFunction.CountIf(BOReportWS.R ange("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.R ange("R" & startRow
&
":R"
& endRow), "A")
c.Offset(0, 12) =
Application.WorksheetFunction.CountIf(BOReportWS.R ange("T" & startRow
&
":T"
& endRow), "A")
c.Offset(0, 13) =
Application.WorksheetFunction.CountIf(BOReportWS.R ange("X" & startRow
&
":X"
& endRow), "A")
c.Offset(0, 14) =
Application.WorksheetFunction.CountIf(BOReportWS.R ange("Z" & startRow
&
":Z"
& endRow), "A")
c.Offset(0, 15) =
Application.WorksheetFunction.CountIf(BOReportWS.R ange("AB" & startRow
&
":AB" & endRow), "A")
End If
Next c
Next i
Worksheets("Regional Summaries").Visible = False
End Sub

--

Dave Peterson

--

Dave Peterson



Dave Peterson
Code only works in Debug mode. Why?
 
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 T wrote:

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

"Dave Peterson" wrote in message
...
If that's true, then it's another reason not to use that web interface.

Peter T wrote:

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

Regards,
Peter T

"Dave Peterson" wrote in message
...
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.



Ayo wrote:

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.R ange("H" & startRow
&
":H"
& endRow), "A")
c.Offset(0, 4) =
Application.WorksheetFunction.CountIf(BOReportWS.R ange("H" & startRow
&
":H"
& endRow), "A")
c.Offset(0, 5) =
Application.WorksheetFunction.CountIf(BOReportWS.R ange("L" & startRow
&
":L"
& endRow), "A")
c.Offset(0, 6) =
Application.WorksheetFunction.CountIf(BOReportWS.R ange("N" & startRow
&
":N"
& endRow), "A")
'c.Offset(0, 7) = cnt("E",c.Value, 2, startRow, endRow)
c.Offset(0, 8) =
Application.WorksheetFunction.CountIf(BOReportWS.R ange("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.R ange("R" & startRow
&
":R"
& endRow), "A")
c.Offset(0, 12) =
Application.WorksheetFunction.CountIf(BOReportWS.R ange("T" & startRow
&
":T"
& endRow), "A")
c.Offset(0, 13) =
Application.WorksheetFunction.CountIf(BOReportWS.R ange("X" & startRow
&
":X"
& endRow), "A")
c.Offset(0, 14) =
Application.WorksheetFunction.CountIf(BOReportWS.R ange("Z" & startRow
&
":Z"
& endRow), "A")
c.Offset(0, 15) =
Application.WorksheetFunction.CountIf(BOReportWS.R ange("AB" & startRow
&
":AB" & endRow), "A")
End If
Next c
Next i
Worksheets("Regional Summaries").Visible = False
End Sub

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

Peter T
Code only works in Debug mode. Why?
 
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

"Dave Peterson" wrote in message
...
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 T wrote:

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

"Dave Peterson" wrote in message
...
If that's true, then it's another reason not to use that web interface.

Peter T wrote:

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

Regards,
Peter T

"Dave Peterson" wrote in message
...
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.



Ayo wrote:

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.R ange("H" &
startRow
&
":H"
& endRow), "A")
c.Offset(0, 4) =
Application.WorksheetFunction.CountIf(BOReportWS.R ange("H" &
startRow
&
":H"
& endRow), "A")
c.Offset(0, 5) =
Application.WorksheetFunction.CountIf(BOReportWS.R ange("L" &
startRow
&
":L"
& endRow), "A")
c.Offset(0, 6) =
Application.WorksheetFunction.CountIf(BOReportWS.R ange("N" &
startRow
&
":N"
& endRow), "A")
'c.Offset(0, 7) = cnt("E",c.Value, 2, startRow, endRow)
c.Offset(0, 8) =
Application.WorksheetFunction.CountIf(BOReportWS.R ange("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.R ange("R" &
startRow
&
":R"
& endRow), "A")
c.Offset(0, 12) =
Application.WorksheetFunction.CountIf(BOReportWS.R ange("T" &
startRow
&
":T"
& endRow), "A")
c.Offset(0, 13) =
Application.WorksheetFunction.CountIf(BOReportWS.R ange("X" &
startRow
&
":X"
& endRow), "A")
c.Offset(0, 14) =
Application.WorksheetFunction.CountIf(BOReportWS.R ange("Z" &
startRow
&
":Z"
& endRow), "A")
c.Offset(0, 15) =
Application.WorksheetFunction.CountIf(BOReportWS.R ange("AB" &
startRow
&
":AB" & endRow), "A")
End If
Next c
Next i
Worksheets("Regional Summaries").Visible = False
End Sub

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson




All times are GMT +1. The time now is 02:09 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com