PC Review
Home Forums Reviews Articles Register
Member Login:

PC Review > Forums > Newsgroups > Microsoft Excel > Microsoft Excel Programming > Code only works in Debug mode. Why?

Reply
Thread Tools Rate Thread

Code only works in Debug mode. Why?

 
 
Ayo
Guest
Posts: n/a
 
      20th Oct 2009
Can someone tell me why the code below only works when I run it in debug
mode, but gives me all zero when I try to run it directly from the
commandbutton click event?

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

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

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

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

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

ElseIf c.Row = Regws_endRow Then
If Right(c.Value, 7) = "VENDORS" Or Right(c.Value, 6) = "VENDOR" Then
AAVM_eRow = REGrng_eRow(AAVM_sRow)
c.Value = Evaluate("=SUMPRODUCT((B" & AAVM_sRow & ":B" &
AAVM_eRow & "<>"""")/COUNTIF(B" & AAVM_sRow & ":B" & AAVM_eRow & ",B" &
AAVM_sRow & ":B" & AAVM_eRow & "&""""))")
If c.Value < 2 Then
c.Value = c.Value & " VENDOR"
Else
c.Value = c.Value & " VENDORS"
End If
AAVM_sRow = AAVM_eRow + 2
End If
c.Offset(0, 2) = Application.WorksheetFunction.CountA(rngReg)
'c.Offset(0, 3) =
Application.WorksheetFunction.CountIf(BOReportWS.Range("H" & startRow & ":H"
& endRow), "A")
c.Offset(0, 4) =
Application.WorksheetFunction.CountIf(BOReportWS.Range("H" & startRow & ":H"
& endRow), "A")
c.Offset(0, 5) =
Application.WorksheetFunction.CountIf(BOReportWS.Range("L" & startRow & ":L"
& endRow), "A")
c.Offset(0, 6) =
Application.WorksheetFunction.CountIf(BOReportWS.Range("N" & startRow & ":N"
& endRow), "A")
'c.Offset(0, 7) = cnt("E",c.Value, 2, startRow, endRow)
c.Offset(0, 8) =
Application.WorksheetFunction.CountIf(BOReportWS.Range("P" & startRow & ":P"
& endRow), "A")
'c.Offset(0, 9) = cnt(c.Value, 2, startRow, endRow)
'c.Offset(0, 10) = cnt(c.Value, 2, startRow, endRow)
c.Offset(0, 11) =
Application.WorksheetFunction.CountIf(BOReportWS.Range("R" & startRow & ":R"
& endRow), "A")
c.Offset(0, 12) =
Application.WorksheetFunction.CountIf(BOReportWS.Range("T" & startRow & ":T"
& endRow), "A")
c.Offset(0, 13) =
Application.WorksheetFunction.CountIf(BOReportWS.Range("X" & startRow & ":X"
& endRow), "A")
c.Offset(0, 14) =
Application.WorksheetFunction.CountIf(BOReportWS.Range("Z" & startRow & ":Z"
& endRow), "A")
c.Offset(0, 15) =
Application.WorksheetFunction.CountIf(BOReportWS.Range("AB" & startRow &
":AB" & endRow), "A")
End If
Next c
Next i
Worksheets("Regional Summaries").Visible = False
End Sub
 
Reply With Quote
 
 
 
 
Dave Peterson
Guest
Posts: n/a
 
      21st Oct 2009
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.Range("H" & startRow & ":H"
> & endRow), "A")
> c.Offset(0, 4) =
> Application.WorksheetFunction.CountIf(BOReportWS.Range("H" & startRow & ":H"
> & endRow), "A")
> c.Offset(0, 5) =
> Application.WorksheetFunction.CountIf(BOReportWS.Range("L" & startRow & ":L"
> & endRow), "A")
> c.Offset(0, 6) =
> Application.WorksheetFunction.CountIf(BOReportWS.Range("N" & startRow & ":N"
> & endRow), "A")
> 'c.Offset(0, 7) = cnt("E",c.Value, 2, startRow, endRow)
> c.Offset(0, 8) =
> Application.WorksheetFunction.CountIf(BOReportWS.Range("P" & startRow & ":P"
> & endRow), "A")
> 'c.Offset(0, 9) = cnt(c.Value, 2, startRow, endRow)
> 'c.Offset(0, 10) = cnt(c.Value, 2, startRow, endRow)
> c.Offset(0, 11) =
> Application.WorksheetFunction.CountIf(BOReportWS.Range("R" & startRow & ":R"
> & endRow), "A")
> c.Offset(0, 12) =
> Application.WorksheetFunction.CountIf(BOReportWS.Range("T" & startRow & ":T"
> & endRow), "A")
> c.Offset(0, 13) =
> Application.WorksheetFunction.CountIf(BOReportWS.Range("X" & startRow & ":X"
> & endRow), "A")
> c.Offset(0, 14) =
> Application.WorksheetFunction.CountIf(BOReportWS.Range("Z" & startRow & ":Z"
> & endRow), "A")
> c.Offset(0, 15) =
> Application.WorksheetFunction.CountIf(BOReportWS.Range("AB" & startRow &
> ":AB" & endRow), "A")
> End If
> Next c
> Next i
> Worksheets("Regional Summaries").Visible = False
> End Sub

--

Dave Peterson
 
Reply With Quote
 
Peter T
Guest
Posts: n/a
 
      21st Oct 2009
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.Range("H" & startRow &
>> ":H"
>> & endRow), "A")
>> c.Offset(0, 4) =
>> Application.WorksheetFunction.CountIf(BOReportWS.Range("H" & startRow &
>> ":H"
>> & endRow), "A")
>> c.Offset(0, 5) =
>> Application.WorksheetFunction.CountIf(BOReportWS.Range("L" & startRow &
>> ":L"
>> & endRow), "A")
>> c.Offset(0, 6) =
>> Application.WorksheetFunction.CountIf(BOReportWS.Range("N" & startRow &
>> ":N"
>> & endRow), "A")
>> 'c.Offset(0, 7) = cnt("E",c.Value, 2, startRow, endRow)
>> c.Offset(0, 8) =
>> Application.WorksheetFunction.CountIf(BOReportWS.Range("P" & startRow &
>> ":P"
>> & endRow), "A")
>> 'c.Offset(0, 9) = cnt(c.Value, 2, startRow, endRow)
>> 'c.Offset(0, 10) = cnt(c.Value, 2, startRow, endRow)
>> c.Offset(0, 11) =
>> Application.WorksheetFunction.CountIf(BOReportWS.Range("R" & startRow &
>> ":R"
>> & endRow), "A")
>> c.Offset(0, 12) =
>> Application.WorksheetFunction.CountIf(BOReportWS.Range("T" & startRow &
>> ":T"
>> & endRow), "A")
>> c.Offset(0, 13) =
>> Application.WorksheetFunction.CountIf(BOReportWS.Range("X" & startRow &
>> ":X"
>> & endRow), "A")
>> c.Offset(0, 14) =
>> Application.WorksheetFunction.CountIf(BOReportWS.Range("Z" & startRow &
>> ":Z"
>> & endRow), "A")
>> c.Offset(0, 15) =
>> Application.WorksheetFunction.CountIf(BOReportWS.Range("AB" & startRow &
>> ":AB" & endRow), "A")
>> End If
>> Next c
>> Next i
>> Worksheets("Regional Summaries").Visible = False
>> End Sub
>
> --
>
> Dave Peterson
 
Reply With Quote
 
Dave Peterson
Guest
Posts: n/a
 
      21st Oct 2009
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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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.Range("H" & startRow &
> >> ":H"
> >> & endRow), "A")
> >> c.Offset(0, 4) =
> >> Application.WorksheetFunction.CountIf(BOReportWS.Range("H" & startRow &
> >> ":H"
> >> & endRow), "A")
> >> c.Offset(0, 5) =
> >> Application.WorksheetFunction.CountIf(BOReportWS.Range("L" & startRow &
> >> ":L"
> >> & endRow), "A")
> >> c.Offset(0, 6) =
> >> Application.WorksheetFunction.CountIf(BOReportWS.Range("N" & startRow &
> >> ":N"
> >> & endRow), "A")
> >> 'c.Offset(0, 7) = cnt("E",c.Value, 2, startRow, endRow)
> >> c.Offset(0, 8) =
> >> Application.WorksheetFunction.CountIf(BOReportWS.Range("P" & startRow &
> >> ":P"
> >> & endRow), "A")
> >> 'c.Offset(0, 9) = cnt(c.Value, 2, startRow, endRow)
> >> 'c.Offset(0, 10) = cnt(c.Value, 2, startRow, endRow)
> >> c.Offset(0, 11) =
> >> Application.WorksheetFunction.CountIf(BOReportWS.Range("R" & startRow &
> >> ":R"
> >> & endRow), "A")
> >> c.Offset(0, 12) =
> >> Application.WorksheetFunction.CountIf(BOReportWS.Range("T" & startRow &
> >> ":T"
> >> & endRow), "A")
> >> c.Offset(0, 13) =
> >> Application.WorksheetFunction.CountIf(BOReportWS.Range("X" & startRow &
> >> ":X"
> >> & endRow), "A")
> >> c.Offset(0, 14) =
> >> Application.WorksheetFunction.CountIf(BOReportWS.Range("Z" & startRow &
> >> ":Z"
> >> & endRow), "A")
> >> c.Offset(0, 15) =
> >> Application.WorksheetFunction.CountIf(BOReportWS.Range("AB" & startRow &
> >> ":AB" & endRow), "A")
> >> End If
> >> Next c
> >> Next i
> >> Worksheets("Regional Summaries").Visible = False
> >> End Sub
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson
 
Reply With Quote
 
Peter T
Guest
Posts: n/a
 
      21st Oct 2009
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> > 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.Range("H" & startRow
>> >> &
>> >> ":H"
>> >> & endRow), "A")
>> >> c.Offset(0, 4) =
>> >> Application.WorksheetFunction.CountIf(BOReportWS.Range("H" & startRow
>> >> &
>> >> ":H"
>> >> & endRow), "A")
>> >> c.Offset(0, 5) =
>> >> Application.WorksheetFunction.CountIf(BOReportWS.Range("L" & startRow
>> >> &
>> >> ":L"
>> >> & endRow), "A")
>> >> c.Offset(0, 6) =
>> >> Application.WorksheetFunction.CountIf(BOReportWS.Range("N" & startRow
>> >> &
>> >> ":N"
>> >> & endRow), "A")
>> >> 'c.Offset(0, 7) = cnt("E",c.Value, 2, startRow, endRow)
>> >> c.Offset(0, 8) =
>> >> Application.WorksheetFunction.CountIf(BOReportWS.Range("P" & startRow
>> >> &
>> >> ":P"
>> >> & endRow), "A")
>> >> 'c.Offset(0, 9) = cnt(c.Value, 2, startRow, endRow)
>> >> 'c.Offset(0, 10) = cnt(c.Value, 2, startRow, endRow)
>> >> c.Offset(0, 11) =
>> >> Application.WorksheetFunction.CountIf(BOReportWS.Range("R" & startRow
>> >> &
>> >> ":R"
>> >> & endRow), "A")
>> >> c.Offset(0, 12) =
>> >> Application.WorksheetFunction.CountIf(BOReportWS.Range("T" & startRow
>> >> &
>> >> ":T"
>> >> & endRow), "A")
>> >> c.Offset(0, 13) =
>> >> Application.WorksheetFunction.CountIf(BOReportWS.Range("X" & startRow
>> >> &
>> >> ":X"
>> >> & endRow), "A")
>> >> c.Offset(0, 14) =
>> >> Application.WorksheetFunction.CountIf(BOReportWS.Range("Z" & startRow
>> >> &
>> >> ":Z"
>> >> & endRow), "A")
>> >> c.Offset(0, 15) =
>> >> Application.WorksheetFunction.CountIf(BOReportWS.Range("AB" & startRow
>> >> &
>> >> ":AB" & endRow), "A")
>> >> End If
>> >> Next c
>> >> Next i
>> >> Worksheets("Regional Summaries").Visible = False
>> >> End Sub
>> >
>> > --
>> >
>> > Dave Peterson
>
> --
>
> Dave Peterson
 
Reply With Quote
 
Dave Peterson
Guest
Posts: n/a
 
      21st Oct 2009
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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> >> news:(E-Mail Removed)...
> >> > 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.Range("H" & startRow
> >> >> &
> >> >> ":H"
> >> >> & endRow), "A")
> >> >> c.Offset(0, 4) =
> >> >> Application.WorksheetFunction.CountIf(BOReportWS.Range("H" & startRow
> >> >> &
> >> >> ":H"
> >> >> & endRow), "A")
> >> >> c.Offset(0, 5) =
> >> >> Application.WorksheetFunction.CountIf(BOReportWS.Range("L" & startRow
> >> >> &
> >> >> ":L"
> >> >> & endRow), "A")
> >> >> c.Offset(0, 6) =
> >> >> Application.WorksheetFunction.CountIf(BOReportWS.Range("N" & startRow
> >> >> &
> >> >> ":N"
> >> >> & endRow), "A")
> >> >> 'c.Offset(0, 7) = cnt("E",c.Value, 2, startRow, endRow)
> >> >> c.Offset(0, 8) =
> >> >> Application.WorksheetFunction.CountIf(BOReportWS.Range("P" & startRow
> >> >> &
> >> >> ":P"
> >> >> & endRow), "A")
> >> >> 'c.Offset(0, 9) = cnt(c.Value, 2, startRow, endRow)
> >> >> 'c.Offset(0, 10) = cnt(c.Value, 2, startRow, endRow)
> >> >> c.Offset(0, 11) =
> >> >> Application.WorksheetFunction.CountIf(BOReportWS.Range("R" & startRow
> >> >> &
> >> >> ":R"
> >> >> & endRow), "A")
> >> >> c.Offset(0, 12) =
> >> >> Application.WorksheetFunction.CountIf(BOReportWS.Range("T" & startRow
> >> >> &
> >> >> ":T"
> >> >> & endRow), "A")
> >> >> c.Offset(0, 13) =
> >> >> Application.WorksheetFunction.CountIf(BOReportWS.Range("X" & startRow
> >> >> &
> >> >> ":X"
> >> >> & endRow), "A")
> >> >> c.Offset(0, 14) =
> >> >> Application.WorksheetFunction.CountIf(BOReportWS.Range("Z" & startRow
> >> >> &
> >> >> ":Z"
> >> >> & endRow), "A")
> >> >> c.Offset(0, 15) =
> >> >> Application.WorksheetFunction.CountIf(BOReportWS.Range("AB" & startRow
> >> >> &
> >> >> ":AB" & endRow), "A")
> >> >> End If
> >> >> Next c
> >> >> Next i
> >> >> Worksheets("Regional Summaries").Visible = False
> >> >> End Sub
> >> >
> >> > --
> >> >
> >> > Dave Peterson
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson
 
Reply With Quote
 
Peter T
Guest
Posts: n/a
 
      21st Oct 2009
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> > 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" <(E-Mail Removed)> wrote in message
>> >> news:(E-Mail Removed)...
>> >> > 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.Range("H" &
>> >> >> startRow
>> >> >> &
>> >> >> ":H"
>> >> >> & endRow), "A")
>> >> >> c.Offset(0, 4) =
>> >> >> Application.WorksheetFunction.CountIf(BOReportWS.Range("H" &
>> >> >> startRow
>> >> >> &
>> >> >> ":H"
>> >> >> & endRow), "A")
>> >> >> c.Offset(0, 5) =
>> >> >> Application.WorksheetFunction.CountIf(BOReportWS.Range("L" &
>> >> >> startRow
>> >> >> &
>> >> >> ":L"
>> >> >> & endRow), "A")
>> >> >> c.Offset(0, 6) =
>> >> >> Application.WorksheetFunction.CountIf(BOReportWS.Range("N" &
>> >> >> startRow
>> >> >> &
>> >> >> ":N"
>> >> >> & endRow), "A")
>> >> >> 'c.Offset(0, 7) = cnt("E",c.Value, 2, startRow, endRow)
>> >> >> c.Offset(0, 8) =
>> >> >> Application.WorksheetFunction.CountIf(BOReportWS.Range("P" &
>> >> >> startRow
>> >> >> &
>> >> >> ":P"
>> >> >> & endRow), "A")
>> >> >> 'c.Offset(0, 9) = cnt(c.Value, 2, startRow, endRow)
>> >> >> 'c.Offset(0, 10) = cnt(c.Value, 2, startRow, endRow)
>> >> >> c.Offset(0, 11) =
>> >> >> Application.WorksheetFunction.CountIf(BOReportWS.Range("R" &
>> >> >> startRow
>> >> >> &
>> >> >> ":R"
>> >> >> & endRow), "A")
>> >> >> c.Offset(0, 12) =
>> >> >> Application.WorksheetFunction.CountIf(BOReportWS.Range("T" &
>> >> >> startRow
>> >> >> &
>> >> >> ":T"
>> >> >> & endRow), "A")
>> >> >> c.Offset(0, 13) =
>> >> >> Application.WorksheetFunction.CountIf(BOReportWS.Range("X" &
>> >> >> startRow
>> >> >> &
>> >> >> ":X"
>> >> >> & endRow), "A")
>> >> >> c.Offset(0, 14) =
>> >> >> Application.WorksheetFunction.CountIf(BOReportWS.Range("Z" &
>> >> >> startRow
>> >> >> &
>> >> >> ":Z"
>> >> >> & endRow), "A")
>> >> >> c.Offset(0, 15) =
>> >> >> Application.WorksheetFunction.CountIf(BOReportWS.Range("AB" &
>> >> >> startRow
>> >> >> &
>> >> >> ":AB" & endRow), "A")
>> >> >> End If
>> >> >> Next c
>> >> >> Next i
>> >> >> Worksheets("Regional Summaries").Visible = False
>> >> >> End Sub
>> >> >
>> >> > --
>> >> >
>> >> > Dave Peterson
>> >
>> > --
>> >
>> > Dave Peterson
>
> --
>
> Dave Peterson
 
Reply With Quote
 
 
 
Reply

« RE: Returning the average of two ranges with a macro | Make VBA Enter Formula into cell rather than calculation »
Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Emailing Code works fine in debug mode, BUT NOT when uploaded .spider Microsoft ASP .NET 2 17th Jul 2007 04:46 PM
User Control access fails in debug mode, works in release mode rbg Microsoft C# .NET 0 17th Jan 2007 10:51 PM
User Control access fails in debug mode, works in release mode rbg Microsoft ASP .NET 0 17th Jan 2007 10:51 PM
Excel 2000 Code works except in debug mode Bob Smedley Microsoft Excel Programming 0 24th Jan 2006 01:21 AM
code only works in debug mode =?Utf-8?B?RGFCYXJ0bWFu?= Microsoft Access 2 15th Jul 2005 11:01 PM


Features
Forums Startup Database Reviews Articles FAQ / Guidelines About Us
 

Advertising
 

Newsgroups
Windows Vista Windows XP Microsoft Word Microsoft DotNet Windows 2000 AntiSpyware Microsoft Access Microsoft Excel Microsoft Outlook Hardware
 


All times are GMT +1. The time now is 01:17 AM.
Powered by vBulletin®. Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2010, Crawlability, Inc.
Contact Us - Archive - Privacy Statement - Top