CODEWITHSUNDEEP
excelvba
Jeremy Rowler
Jeremy Rowler

Reputation: 387

How can I search a column through only one specific table in excel?

I'm working on a VBA code for excel that is supposed to search through a specific column to find the part number we are looking for and extract some data from that row. This is an growing database, so we need to search until the last row for the part number. In other parts of the spreadsheet, I used this code

'           Finds HW part number in Sheet1
'Get "Sheet1" Index
    for i=1 to objWorkbook.WorkSheets.Count
        if InStr(objWorkbook.WorkSheets(i).Name,"Sheet1") > 0 Then
            sheet1 = i
        End If
    Next

    objWorkbook.WorkSheets(sheet1).Activate

'Get Last Row index in Sheet1
intRow = 4
Do Until objWorkbook.WorkSheets(sheet1).Cells(intRow, 4).Value = ""
    intRow = intRow + 1
Loop
last_row = intRow

    'Find HW Part number location
    For j = 3 to last_row
        If Instr(objWorkbook.WorkSheets(my).Cells(j, 4).Value, hw_part_number) > 0 Then
            hw_reference_row = j
        End If
    Next

I had to change some of the variables and comments due to confidentiality policies. But this is basically what I used in other parts of the code.

The only problem is that with this new sheet I am working on, I can't search for the last row because there are multiple tables in the same sheet. If it were up to me, I would seperate the tables into different worksheets. But its not up to me. :(

So to find the "last_row" I used to search for the first empty space, But since there are multiple tables, there are empty spaces in between them and that wouldn't work. Temporarily, I have replaced

For j = 3 to last_row

with

For j = 3 to 800

Just temporarily until I can find out a way to search until the last row. The tables do not interfere with each other. Meaning I search for the part number, and it will never show up in an earlier in that same column in a different table. So I'm searching through it all, even though I know it won't show up in the first couple hundred rows.

So my question is, Is there any way to search just through that specific table in the worksheet? And to search every row in that worksheet?

Upvotes: 1

Views: 10468

Answers (1)

tbur
tbur

Reputation: 2454

Sub FindIt()
Dim partnumber As Integer
partnumber = 12345
Dim aCel As Range
Set aCell = Sheets(1).Range("YourTableName[ColumnHeadingName]").Find(What:=partnumber, LookIn:=xlValues, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)
    If Not aCell Is Nothing Then
        MsgBox "Value Found in Cell " & aCell.Address
    End If
End Sub

Upvotes: 2

Related Questions