Object variable or withblock variable not set

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!
 
N

Norman Jones

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

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
 
R

Rowan Drummond

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
 
D

davegb

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

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!
 

Ask a Question

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

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

Ask a Question

Top