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