Results 1 to 15 of 15
  1. #1
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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?

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts
    Quote Originally Posted by Don_Sadler View Post
    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.
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by JohnBF View Post
    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.)

  4. #4
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Don_Sadler View Post
    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

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts
    Quote Originally Posted by Don_Sadler View Post
    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?
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by JohnBF View Post
    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

  7. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts
    Quote Originally Posted by Don_Sadler View Post
    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.
    -John ... I float in liquid gardens
    UTC -7ąDS

  8. #8
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by JohnBF View Post
    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

  9. #9
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by JohnBF View Post
    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

  10. #10
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts
    When I get that Object required error, the code that is highlighted is:

    For Each rngarea In myRange.Areas

  11. #11
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  12. #12
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  13. #13
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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)
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  14. #14
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by pieterse View Post
    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.

  15. #15
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    rngCell iis a Range object. Search help for that keyword.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •