VBA search multiple sheets

[sp00kster]

I Could really use some help with converting an Excel formula into VB. Frankly I just don't know enough about VB to even get started and my current worksheets have ran out of realestate - thus I'm going to have to add a VB form search. Let me first put some insight into this. I have 12 identical laid out worksheets labeled from January thru December but with just different data. I need to be able to search all sheets in the range (B5:B2400) for a numerical match and if found return the left cell text value found in the range (A5:A2400) and if nothing / #N/A then "No records found". I tid bit of my working Excel formula is (the remaining months would be nested in this as well):

=IFERROR(INDEX(October!A5:A2400,MATCH(J16,October!B5:B2400,0)),IFERROR(INDEX(November!A5:A2400,MATCH(J16,November!B5:B2400,0)),INDEX(December!A5:A2400,MATCH(J16,December!B5:B2400,0))))

Tried piecing this together but not working

Private Sub CommandButton1_Click()

row_number = 5
Do
DoEvents
row_number = row_number + 1
rec_search = Sheets("January").Range("B" & row_number)
    If rec_search = TextBox1.Text Then
        TextBox2.Text = Sheets("January").Range("A" & row_number)
        'Else: TextBox2.Text = "Cloud Record Not Found"
    End If
Loop Until rec_search = ""

End Sub

Thanks for any help it is greatly appreciated

 

[AlphaFrog]

Private Sub CommandButton1_Click()
    Dim ws As Worksheet, v As Variant
    TextBox2.Text = "Cloud Record Not Found"
    For Each ws In Worksheets
        v = Application.Match(Val(TextBox1.Text), ws.Range("B5:B2400"), 0)
        If Not IsError(v) Then
            TextBox2.Text = ws.Range("A5:A2400")(v).Value
            Exit For
        End If
    Next ws
End Sub

 

[sp00kster]

Private Sub CommandButton1_Click()
    Dim ws As Worksheet, v As Variant
    TextBox2.Text = "Cloud Record Not Found"
    For Each ws In Worksheets
        v = Application.Match(Val(TextBox1.Text), ws.Range("B5:B2400"), 0)
        If Not IsError(v) Then
            TextBox2.Text = ws.Range("A5:A2400")(v).Value
            Exit For
        End If
    Next ws
End Sub

That is so perfect - than you so so very much!!

 

[sp00kster]

Private Sub CommandButton1_Click()
    Dim ws As Worksheet, v As Variant
    TextBox2.Text = "Cloud Record Not Found"
    For Each ws In Worksheets
        v = Application.Match(Val(TextBox1.Text), ws.Range("B5:B2400"), 0)
        If Not IsError(v) Then
            TextBox2.Text = ws.Range("A5:A2400")(v).Value
            Exit For
        End If
    Next ws
End Sub

You have already helped so much but was wondering if there is anyway to show what row number it was found on for my textbox.3? Thanks in advance

 

[AlphaFrog]

v is the index number of the match in Range("B5:B2400")
So if it matched B5, the index number (v) would be 1, B6 = 2, B7 = 3 ...etc.

So below would be the row number because the search range starts at row 5

TextBox3.Text = 4 + v

 

[sp00kster]

Thank you again for your help. I read and test for hours on end and do fairly well when I can specify the sheet name but this looping through all the sheets is like reading Greek. I am now attempting to how to select the row on the found sheet and go to the first instance. But I will not bother you with this and make a new post once I exhausted all my attempts and thank you again. You are a life saver

 

[AlphaFrog]

Thank you again for your help. I read and test for hours on end and do fairly well when I can specify the sheet name but this looping through all the sheets is like reading Greek. I am now attempting to how to select the row on the found sheet and go to the first instance. But I will not bother you with this and make a new post once I exhausted all my attempts and thank you again. You are a life saver

ws is the found sheet.

    For Each ws In Worksheets
        v = Application.Match(Val(TextBox1.Text), ws.Range("B5:B2400"), 0)
        If Not IsError(v) Then
            TextBox2.Text = ws.Range("A5:A2400")(v).Value
            ws.Select
            ws.Rows(4 + v).Select
            Exit For
        End If
    Next ws

 

[sp00kster]

You rock!!

 

[sp00kster]

I have found that the original search range (b5:b2400) at times may have duplicate records. How can I using another command button (CommandButton3_Click()("Next")) to search for the next record for as many times that would press the "Next" record button. Currently it stops at the first matching record found

Thanks again for your help

 

[AlphaFrog]

Private Sub CommandButton3_Click()
    
    Dim rng As Range
    
    Set rng = Range("B5:B2400").Find(TextBox1.Text, Range("B" & ActiveCell.Row), xlValues, xlWhole, xlByRows, xlNext, False)
    
    If Not rng Is Nothing Then
        If rng.Row <> ActiveCell.Row Then
            TextBox2.Text = rng.Offset(, -1).Value
            rng.EntireRow.Select
        Else
            MsgBox "No other match found.", vbInformation, ""
        End If
    End If
    
End Sub