i have a excel sheet that regularly updated. Thus, for certain values i wanted to use/refer, the cell ID or index is not fixed, but certain nearby other/intermidate (like section title, name, school, gender) inputs (can be number or text) are fixed. Therefore, i have to find the nearby intermediate cell ID by using cell values/input, then down or right few cells (let us say one cell) to get my target cell value/index. Then i can use the target cell id/index/address for other purpose. i followed this to create my own function. The whole worksheet (sheet1) is used for search and find.
Function FindCellIndex(SearchString)
Dim SearchRange As Range, cl As Range
Dim FirstFound As String
Application.FindFormat.Clear
Set cl = ActiveWorkbook.Worksheets("Sheet1").Cells.Find(What:=SearchString, _
After:=ActiveWorkbook.Worksheets("Sheet1").Cells(1, 1), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
If Not cl Is Nothing Then
' if found, remember location
FirstFound = cl.Address
' format found cell
Do
cl.Font.Bold = True
cl.Interior.ColorIndex = 3
' find next instance
Set cl = ActiveWorkbook.Worksheets("Sheet1").Cells.FindNext(After:=cl)
' repeat until back where we started
Loop Until FirstFound = cl.Address
End If
End Function
Checking the function
Sub Search()
myid=FindCellIndex("David")
MsgBox myid
End Sub
When i run the above code, the message box showing nothing. It seems it is empty.
I am trying to extract cell ID/addresss by cell values, but the function i wrote is not working properly.