Vba if else statement

Last Edited By Krjb Donovan
Last Updated: Mar 05, 2014 10:02 PM GMT

Question

Mr. Ogilvy,

I see a lot of answers from you so I would like to ask you a question as you seem very knowledgeable in this field.

I am trying to make a simple if else statement in VBA. I just want to have predetermined cells test and if the cell above it is hidden then have it display "H1" and if the cell above it is not hidden then have it display "H2". Currently it is displaying H1 in these cells whether the ones above them are hidden or not. There are no errors or anything that pop up when run. My code is as follows:


With Range("B5, B9, B13, B17, B21, B25, B29, B33, B37, B41, B45, B49, B53, B57, B61, B65, B69, B73, B77, B81, B85, B89, B93, B97, B101")

      .Select
          For Each Cell In Range("B5, B9, B13, B17, B21, B25, B29, B33, B37, B41, B45, B49, B53, B57, B61, B65, B69, B73, B77, B81, B85, B89, B93, B97, B101")
              If Cells.Offset(-1, 0).Hidden = True Then
                  Cell.Value = "H1"
              ElseIf Cells.Offset(-1, 0).Hidden = False Then
                  Cell.Value = "H2"
              End If
          
          Next Cell

Answer

Jeff,

It appears you have a typo in your code. You use Cells instead of Cell. Also, the hidden property is a property of the entirerow or entirecolumn


With Range("B5, B9, B13, B17, B21, B25, B29, B33, B37, B41, B45, B49, B53, B57, B61, B65, B69, B73, B77, B81, B85, B89, B93, B97, B101")

     .Select
         For Each Cell In Range("B5, B9, B13, B17, B21, B25, B29, B33, B37, B41, B45, B49, B53, B57, B61, B65, B69, B73, B77, B81, B85, B89, B93, B97, B101")
             If Cell.Offset(-1, 0).EntireRow.Hidden = True Then
                 Cell.Value = "H1"
             ElseIf Cell.Offset(-1, 0).EntireRow.Hidden = False Then
                 Cell.Value = "H2"
             End If
         
         Next Cell


Another way you might do this

dim i as Long for i = 4 to 100 step 4

 if cells(i,"B").EntireRow.Hidden = True then
    cells(i + 1, "B").Value = "H1"
 else
    cells(i + 1, "B").Value = "H2"
 end if

next i

Advertisement

©2020 eLuminary LLC. All rights reserved.