Using VBA, I set a MATCH formula down a range of cells in Col B. (Thank you, Stephan!) On this sheet, Cols A and B are normally hidden. The formula writes in fine, whether the columns are hidden or not. Then I want to run down the same range and check for errors returned by the formula. I use the following code:
If the columns are visible, this works fine and only returns errors where I have deliberately changed values to induce an error to test my code. But if the columns are hidden, cll.Text (and so var) and cll.Offset(0, 1).Text are blank.
Is this just a property of hidden columns / cells?? Or is there something else I need in my code?
(Yes, I can work around it by unhiding the columns to run the macro and then re-hiding at the end. But I'd rather know if my code is off first.)
VBA Code:
'Check for errors and advise
Set rng = wks.Range("B6:B" & rw)
strE = ""
For Each cll In rng.Cells
If cll.Row > rw Then
Exit For
Else
var = cll.Text
Debug.Print cll.Address & ": " & var
Debug.Print Len(cll.Text)
Debug.Print Len(var)
If IsNumeric(var) = False Then
''Debug.Print "Error!!"
strE = strE & "TIR No. " & cll.Offset(0, 1).Text & _
" not found in this tracker on TIR sheet " & _
cll.Offset(0, -1).Text & "." & vbCrLf
End If
End If
Next cll
If the columns are visible, this works fine and only returns errors where I have deliberately changed values to induce an error to test my code. But if the columns are hidden, cll.Text (and so var) and cll.Offset(0, 1).Text are blank.
Is this just a property of hidden columns / cells?? Or is there something else I need in my code?
(Yes, I can work around it by unhiding the columns to run the macro and then re-hiding at the end. But I'd rather know if my code is off first.)