Reputation: 128
How can i pass a variable to cells.find and get the cell address as result back?
For Each cellName In activeListBoxelements
Cells.Find(What:=cellName, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Select
CellAddr = Selection.Address(False, False, xlR1C1)
MsgBox (CellAddr)
Next cellName
What am i doing wrong here.
thanks
Upvotes: 0
Views: 1762
Reputation: 149335
You need to check if the .Find
is returning anything and then display the address.
Is this what you are trying? (Untested)
Dim aCell As Range, cellName As Range, activeListBoxelements As Range
Dim CellAddr As String
'
'~~> Rest of the code
'
For Each cellName In activeListBoxelements
Set aCell = Cells.Find(What:=cellName.Value, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, _
SearchFormat:=False)
'~~> Check if found
If Not aCell Is Nothing Then
CellAddr = aCell.Address(False, False, xlR1C1)
MsgBox CellAddr
Set aCell = Nothing
End If
Next cellName
Upvotes: 1