Best way to insert values

Topic

New Reply

With several ranges… A1:A6, A8:A12, A22:A26 and so on… if I want to use VBA to test their current value and if they are not blank to insert 0 a value. In other words, this is a planning worksheet and I want to be able ‘clear’ it and start over by resetting a lot of cells to zero.

cells outside that range should be blank.

I am thinking that if the all inclusive range is A1:A110 then I could test each one for blank and if not blank insert zero as follows:

Sub Resetvalues()
ActiveSheet.Unprotect
Dim wsh As Worksheet
Dim btn As Shape
Dim i As Long
Set wsh = Worksheets(“EVENT-Work”)
Set btn = wsh.Shapes(“Rounded Rectangle 2”)
For i = 1 To 110
If IsEmpty(wsh.Range(“A” & i)) Then Next i
Value = “0”
Next i
End If
Application.ScreenUpdating = True
Range(“h3”).Select
ActiveSheet.Protect
End Sub

But this is not working for me as I get a Next without For error. Is there a VBA equivalent of ISBLANK()?

Also, if I wanted to apply the same thing to the same cells in columns H & M, could I do that all in the same IF Statement or must I do a separate IF for each column?

Reply | Quote

Replies

With several ranges… A1:A6, A8:A12, A22:A26 and so on… if I want to use VBA to test their current value and if they are not blank to insert 0 a value. this is a planning worksheet and I want to be able ‘clear’ it and start over by resetting a lot of cells to zero.

cells outside that range should be blank.

I am thinking that if the all inclusive range is A1:A110 then I could test each one for blank and if not blank insert zero as follows:

For i = 1 To 110
If IsEmpty(wsh.Range(“B” & i)) Then Next i
Value = “0”
Next i
End If
But this is not working for me as I get a Next withut For error. Is there a VBA equivalent of ISBLANK()?

Also, if I wanted to apply the same thing to the same cells in columns H & M, could I do that all in the same IF Statement or must I do a separate IF for each column

Don, I think your “Next i” and “End if” are in the wrong order.

You could try

With wsh.Range(“B” & i)
If .Value = vbEmpty then .Value = “0”
End With

To handle multiple noncontiguous areas, you could loop through the areas within the range, such as this untested air code which may have syntax errors:

myRange = Range(“B1:B100, H1:H100, M1:M100”)

with myRange
For Each rngArea In myRange.Areas
For Each rngCell in rngArea
With rngCell
If .Value = vbEmpty then .Value = “0”
…
I’m sure you can make sense of it.

Reply | Quote

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.)

Reply | Quote

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

Reply | Quote

I must have deleted a post inadvertently – I am still not quite used to this forum and the posting/editing procedures.

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

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

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
End With

Does this help?

Reply | Quote

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

Reply | Quote

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

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

For Each rngarea In myRange.Areas

Reply | Quote

When assigning a range to an object variable, you must use the Set keyword.
Also, the outter-most With construct isn’t used in your code. This comes down to:

Code:

Sub Resetvalues()
    ActiveSheet.Unprotect
    Dim wsh As Worksheet
    Set wsh = Worksheets("EVENT-Work")
    Set myRange = Range("B9:B110, H9:H110, O9:O110")
    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
    Application.ScreenUpdating = True
    ActiveSheet.Protect
End Sub

Alternatively you can use the SpecialCells method:

Code:

Sub Resetvalues()
    Dim rngCell As Range
    ActiveSheet.Unprotect
    Application.ScreenUpdating = False
    For Each rngCell In Range("B9:B110, H9:H110, O9:O110")
        With rngCell
            If .Value  vbEmpty Then .Value = "0"
        End With
    Next rngCell
    Application.ScreenUpdating = True
    ActiveSheet.Protect
End Sub

Reply | Quote

When assigning a range to an object variable, you must use the Set keyword.
Also, the outter-most With construct isn’t used in your code. This comes down to:

Code:

Sub Resetvalues()
    ActiveSheet.Unprotect
    Dim wsh As Worksheet
    Set wsh = Worksheets("EVENT-Work")
    Set myRange = Range("B9:B110, H9:H110, O9:O110")
    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
    Application.ScreenUpdating = True
    ActiveSheet.Protect
End Sub

Alternatively you can use the SpecialCells method:

Code:

Sub Resetvalues()
    Dim rngCell As Range
    ActiveSheet.Unprotect
    Application.ScreenUpdating = False
    For Each rngCell In Range("B9:B110, H9:H110, O9:O110")
        With rngCell
            If .Value  vbEmpty Then .Value = "0"
        End With
    Next rngCell
    Application.ScreenUpdating = True
    ActiveSheet.Protect
End Sub

Where can I learn more about how to use rngCell? The VBA help does not have anything about rngCell.

Reply | Quote

Thank you all for your help. There are, it seems, several ways to do what I needed. I looked in another resource and got yet another answer… see below.

Code:

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

Reply | Quote

I think you will find my second suggestion is the most efficient (but given you have not very many cells to process, the difference is tiny)

Reply | Quote

rngCell iis a Range object. Search help for that keyword.

Reply | Quote