Visual Basic to retrieve cell location of any number

sjk

New Member
Joined
Apr 8, 2002
Messages
14
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.
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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).
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,957
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top