I'm trying to modify a bit of code I have to cycle through a column and if the cell is empty, pick a value along the row. - The modification is to allow for the fact that in this case it's not a fixed column but driven by the ActiveCell.
I have two issues. I have been playing around with defining the range. What I have now doesn't throw up an error but it doesn't seem to work either because the result is the same many times over.
- Issue 1 seems to be that the For Each function doesn't cycle through the range.
- Issue 2 is that the value it gives me is one it shouldn't, i.e. the cell isn't empty.
It's a very short bit of code and I would have thought fairly simply - so two issues is quite impressive!
Thanks!
Code
Dim myRange As Range
Dim myEmail As String
For Each myRange In sh2.Range(ActiveCell, Cells(ActiveCell.Row + 20, ActiveCell.Column)) 'sh2.Range(ActiveCell, ActiveCell.Offset(20, 0))
If IsEmpty(myRange) = True Then
myEmail = myEmail & sh2.Cells(r, 2).Value & ", " & sh2.Cells(r, 1).Value & ";"
End If
Next myRange
This is my original code. The columns are fixed so I work with offset to find the email address.