Visual Basic to retrieve cell location of any number

[sjk]

I have an array of data, 40 columns wide and 50 rows high, each cell having a number. I am starting to learn some VBA, and I wrote a few lines of code that will tell me if a specific number resides in the data. (Using For Col and For Row and then testing each row, col value against the input cell). How can you have the worksheet indicate the cell reference or row/column location of the number that has been found? I would like to have the location information entered in a cell(s) on the worksheet that contains the data. I also would ideally like to be able to determine if there are multiple occurrences of a number - maybe that should be a separate post. Thanks in advance, SJK.

[Mark O'Brien]

Looping through the cells using For...Next is fairly lengthy. Dave Hawley will tell you so.

Have a look in VBA help for the Find and FindNext methods. This may solve your problem.

You may be able to find some decent examples on this site if you do a search for "Find". If not, I would actually advise having a look at Dave Hawley's website. (Search for Dave Hawley then click his link in his signature.)

I hope this helps. It doesn't directly address you problem, but this is the best way to go forward.

Repost if you've got any problems.

[sjk]

Mark: Thanks for the response. I had thought that my "find" method may not be the most efficient - but it works! - Probably slow for huge amounts of data. (I tried using some sort of search or find command, unsuccessfully). Besides finding a better way to find a value, I still would like have the code that would give me the cell location or row/col coordinates.

[Ricky Morris]

the address of the cell that meet your criterion can be known by using the address property. the following example is from Excel help:

With Worksheets(1).Range("a1:a500")
Set c = .Find(2, lookin:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Interior.Pattern = xlPatternGray50
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With

[sjk]

Ricky: Thanks for the info - your solution worked great - never thought of that kind of approach. One problem is that if I am search for a number like "37" the macro will find all numbers that have those 2 digits (373, 237, etc.). What command/code will find the exact match to the number I am seeking? Lot to learn, SJK.

[sjk]

Answer to my own question: I simply had to insert the LookAt phrase into the .Find command (remembering that recording macros can be useful in showing command structure).