Think you have a spread sheet similar to this. It contains value "100" in several cells.
So today I will teach you how to get the address of those cells from VBA code
First let's define the value we need to search.
|
Dim s1 As String |
Then assign the value we search to that variable.
|
s1 = "100" |
And it is a good practice to use objects where you can. It prevent occurrence of errors. Think you have worksheet call "Report". If you use conventional way you need to refer that sheet by worksheets("Report") at every where you needed to refer it. But if you use worksheet object you can refer it from very short name. So this will give neat look to your coding as well. So I prefer using objects. So use worksheet objects as follows.
|
Dim WS As Worksheet Set WS = Worksheets("sheet1") |
Then Set the range we need to search.
|
With WS.Range("A1:T23") |
Following line of code will give you the address of each cell having 100 in it.
|
MsgBox c.Address |
Loop will continue until it find a value for c except first value.
|
Loop While Not c Is Nothing And c.Address <> firstAddress |
So here is the full code. You can use this wherever you need by changing the sheet names, range and search values accordingly. Also you can pass the search value as a variable.
|
Dim s1 As String s1 = "100" Dim WS As Worksheet Set WS = Worksheets("sheet1") With WS.Range("A1:T23") Set c = .Find(s1) If Not c Is Nothing Then firstAddress = c.Address Do MsgBox c.Address Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address <> firstAddress End If End With |
With the above code you will get the cell address of cells having "100" to msgboxes.
