Replies

When I get that Object required error, the code that is highlighted is:

For Each rngarea In myRange.Areas

Be sure to close “With myRange” with an “End With”; I didn’t in the above example, so I have edited it.

I had already added that End With and now I have error that says Run-time Error ‘424’:Object Required. the code in use now is:

Code:

Sub Resetvalues()
ActiveSheet.Unprotect
  Dim wsh As Worksheet
  Set wsh = Worksheets("EVENT-Work")
myRange = Range("B9:B110, H9:H110, O9:O110")
With myRange
  For Each rngarea In myRange.Areas
    For Each rngcell In rngarea
      With rngcell
        If .Value  vbEmpty Then .Value = "0"
      End With
    Next rngcell
Next rngarea
End With
Application.ScreenUpdating = True
ActiveSheet.Protect
End Sub

Be sure to close “With myRange” with an “End With”; I didn’t in the above example, so I have edited it.

I had already added that End With and now I have error that says Run-time Error ‘424’:Object Required. the code in use now is:

Sub Resetvalues()
ActiveSheet.Unprotect
Dim wsh As Worksheet
Set wsh = Worksheets(“EVENT-Work”)
myRange = Range(“B9:B110, H9:H110, O9:O110”)
With myRange
For Each rngarea In myRange.Areas
For Each rngcell In rngarea
With rngcell
If .Value vbEmpty Then .Value = “0”
End With
Next rngcell
Next rngarea
End With
Application.ScreenUpdating = True
ActiveSheet.Protect
End Sub

No, the mistake was mine, see your private messages.

You still need to use the Next statements in the loops:

Code:

With myRange
  For Each rngarea In myRange.Areas
    For Each rngcell In rngarea
      With rngcell
        If .Value  vbEmpty Then .Value = "0"
      End With
  Next rngcell
Next rngarea

Does this help?

I think it is getting closer although now I get a Compile Error: Expected End With

You read the post before I cleaned up some edits, see my revision.

When you use a “For Each Item in Parent.Items” loop, you don’t need the counter, see the VBA Help. (As an added bonus, VBA automatically releases the Item object so you don’t have to set it to = Nothing to close your Sub.)

And how about If .Value vbEmpty

(or you might try If .Value vbNull, don’t remember the exact difference.)

(Edit: did you delete a post, or am I sleep deprived?)

I must have deleted a post inadvertently – I am still not quite used to this forum and the posting/editing procedures. I tried that code and kept getting errors… finally, I am using the following yet I continue to get a For without next error… I went through several variations and continued to get End With errors, or expected End With errors. I am confused about why it is not looking and where/when End With and For/Next are needed.

Sub Resetvalues()
ActiveSheet.Unprotect
Dim wsh As Worksheet
Dim btn As Shape
Set wsh = Worksheets(“EVENT-Work”)
myRange = Range(“B9:B110, H9:H110, 09:O110”)

With myRange
For Each rngArea In myRange.Areas
For Each rngCell In rngArea
With rngCell
If .Value = vbEmpty Then .Value = “0”
End With
Application.ScreenUpdating = True
Range(“h3”).Select
ActiveSheet.Protect
End Sub

I don’t have to have a For i= 1 to n statement?

Also, what I want to do is to assign a zero only if the cell is not empty… so is there a NotvbEmpty?

You read the post before I cleaned up some edits, see my revision.

When you use a “For Each Item in Parent.Items” loop, you don’t need the counter, see the VBA Help. (As an added bonus, VBA automatically releases the Item object so you don’t have to set it to = Nothing to close your Sub.)

And how about If .Value vbEmpty

(or you might try If .Value vbNull, don’t remember the exact difference.)

(Edit: did you delete a post, or am I sleep deprived)
(Further Edit – apologies, Don, I mistakenly hit the Edit button on your post, thinking I was replying.)

Don,

I click on the shape and then type the name directly into the names box at the upper left hand corner.

Doe this work for you?

Yes, this does work now – it didn’t before, though… I must have done something wrong. Thanks for the fast response.

Thank you so much – of course those are the correct formulas to do it – why didn’t I see that initially? I must have a circular reference issue in my head.

Thank you so much – of course those are the correct formulas to do it – why didn’t I see that initially? I must have a circular reference issue in my head.

Thank you so much – of course those are the correct formulas to do it – why didn’t I see that initially? I must have a circular reference issue in my head.

Thank you so much – of course those are the correct formulas to do it – why didn’t I see that initially? I must have a circular reference issue in my head.

Thank you so much – of course those are the correct formulas to do it – why didn’t I see that initially? I must have a circular reference issue in my head.

Thank you so much – of course those are the correct formulas to do it – why didn’t I see that initially? I must have a circular reference issue in my head.

Thank you so much – of course those are the correct formulas to do it – why didn’t I see that initially? I must have a circular reference issue in my head.

Thank you so much – of course those are the correct formulas to do it – why didn’t I see that initially? I must have a circular reference issue in my head.