CODEWITHSUNDEEP
excelvba
NCC
NCC

Reputation: 819

Search for a Cell in Excel using VBA

Problem: I would like to find a value of a cell next to or below the cell content a text value for a workbook.

Example: In Sheet2, I have two cells stand random (assume its index is unknown and total is not a defined name)

enter image description here

I want to search for the value "200" stands next to the total and put it in sheet 2 (an active cell). In case, there are multiple cell that contains the word "Total" list all of them and if possible, put the name of the sheet that contains the cell that I am looking for. Value 200 Sheet2

enter image description here

My Approach: 1. User input

enter image description here

  1. Go to each cell and search for it. This will take time if search for the whole limitation of cell in excel. So the search only limit to 100 columns x 10000 rows.

  2. After find its index, offset to 1 columns to get the value

  3. Write the result ActiveCell.Value = Search_Value. Then continue to search for the rest of sheets. Offset 1 coloum and 1 row to write the second value...

Searching is a very difficult concept, and I truly have no idea how to do the search part. Please help

Upvotes: 1

Views: 10958

Answers (1)

SeanC
SeanC

Reputation: 15923

With Worksheets(1).Range("a1:a500")
    counter=0
    Set c = .Find("Total", lookin:=xlValues)
    If Not c Is Nothing Then
        firstAddress = c.Address
        Do
            counter=counter+1
            Worksheets(2).range("A1").offset(counter,0)=c.offset(0,1)
            Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
End With

firstaddress holds the location of the first cell found (so we know when to stop); firstaddress.offset(0,1) will give you the value you are trying to save, so setting worksheet(2).range("a1").offset(counter,0) will list all the values it finds on the 2nd tab, from a1 down to however many it finds in the range

Upvotes: 3

Related Questions