Object variable or withblock variable not set

  • Thread starter Thread starter davegb
  • Start date Start date
D

davegb

Wrote this:

Sub CountReasonContactCode()
Dim Wksht As Worksheet
Dim rReason As Range
Dim lRow As Long
Dim lCurRow As Long
Dim rCell As Range
Dim l16Rct As Long
Dim l16Act As Long
Dim l16BGct As Long
Dim lCt As Long
Dim lTotNameRow As Long

lRow = 107
lTotNameRow = 4
l16Rct = 0
l16Act = 0
l16BGct = 0


Do Until Wksht.Name = "Totals" <---Object Variable or Withblock
variable not set
For Each Wksht In ThisWorkbook.Worksheets

I tried setting wksht = to activeworkbook.worksheets, but that gave me
another error. Any suggestions?
Thanks again!
 
Hi Dave,
Do Until Wksht.Name = "Totals" <---Object Variable or Withblock
variable not set
For Each Wksht In ThisWorkbook.Worksheets
Click to expand...

If your intention is to operate on all worksheets except the Totals sheet,
try:

For Each Wksht In ThisWorkbook.Worksheets
If Wksht.Name <> "Totals" Then
' your processing code
End If
Next Wksht

If, alternatively, your intention is to stop processing when the iteration
reaches the Totals sheet, try:

For Each Wksht In ThisWorkbook.Worksheets
If Wksht.Name = "Totals" Then Exit For
' Your processing code
Next Wksht
 
Hi Dave

Try it like this:


For Each Wksht In ThisWorkbook.Worksheets
if Wksht.Name = "Totals" then
'do something
else
'do something else
end if
next Wksht

Hope this helps
Rowan
 
Norman said:
Hi Dave,


If your intention is to operate on all worksheets except the Totals sheet,
try:

For Each Wksht In ThisWorkbook.Worksheets
If Wksht.Name <> "Totals" Then
' your processing code
End If
Next Wksht

If, alternatively, your intention is to stop processing when the iteration
reaches the Totals sheet, try:

For Each Wksht In ThisWorkbook.Worksheets
If Wksht.Name = "Totals" Then Exit For
' Your processing code
Next Wksht
Click to expand...

Thanks to all of you for your help. I should have mentioned that there
are other sheets in the workbook I don't want the macro to do. I just
want it to do the ones to the left of Totals, not the ones to the
right. So I used the second variation you suggested, Norman. Works fine!
 
Back
Top