| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Addin Auto_Open code executes only in debug mode | Excel Programming | |||
| Code for Error handling using F5 from debug mode | Excel Programming | |||
| Macro Works but not in Debug Step mode | Excel Programming | |||
| Excel 2000 Code works except in debug mode | Excel Programming | |||
| My VBA code works in the step though mode but not at full speed | Excel Programming | |||
Linear Mode
