Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Visual Basic to retrieve cell location of any number

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  2. #2
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  3. #3
    New Member
    Join Date
    Apr 2002
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Posts
    363
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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



    It's never too late to learn something new.

    Ricky

  5. #5
    New Member
    Join Date
    Apr 2002
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  6. #6
    New Member
    Join Date
    Apr 2002
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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).

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •