Search multiple sheets with vba

[Rivie]

how do i build a code to do the following:
i have 2 sheets sheet 1- Box Index & sheet 2- sign out.
i need to have a code that will take the numbers from sheet 1 cells A2:A120 and search sheet 2 column B for the matching number. if it find said number for it to look over 2 cells (d column) to the right to see if there is a date entered. if no date entered i need the contents of the A column of that row to be copied to the B column of sheet 1 next to the number that is was looking for. if date is entered in the d column to continue to look through the B column for the number again, if no other occurrence then return the code ITP in the B Column of sheet 1. Basically i'm trying to make a quick look up page for my inventory sheet as if will be forever growing as items go in and out of my facility. we only have 120 clients and i need to know what item number the have and what i have in stock at the facility. this code does not have to run with every update i make to the inventory sheet. i think i could make a macro button to run it so that it doesn't slow down or crash excel. i know i'm asking a lot. I'm trying to learn

 

[JLGWhiz]

See if this works for you

Sub t()
Dim sh1 As Worksheet, sh2 As Worksheet, c As Range, fn As Range, adr As String
Set sh1 = Sheets("Box Index")
Set sh2 = Sheets("sign out")
    With sh1
        For Each c In .Range("A2:A120")
            Set fn = sh2.Range("B:B").Find(c.Value, , xlValues, xlWhole)
            If Not fn Is Nothing Then
                adr = fn.Address
                Do
                    If Not IsDate(fn.Offset(, 2)) Then
                        c.Offset(, 1) = fn.Offset(, -1)
                        Exit Do
                    End If
                    Set fn = sh2.Range("B:B").FindNext(fn)
                Loop While fn.Address <> adr
                If fn.Address = adr And c.Offset(, 1) = "" Then
                    c.Offset(, 1) = "ITP"
                End If
            End If
        Next
    End With
End Sub

 

[Rivie]

See if this works for you

Sub t()
Dim sh1 As Worksheet, sh2 As Worksheet, c As Range, fn As Range, adr As String
Set sh1 = Sheets("Box Index")
Set sh2 = Sheets("sign out")
    With sh1
        For Each c In .Range("A2:A120")
            Set fn = sh2.Range("B:B").Find(c.Value, , xlValues, xlWhole)
            If Not fn Is Nothing Then
                adr = fn.Address
                Do
                    If Not IsDate(fn.Offset(, 2)) Then
                        c.Offset(, 1) = fn.Offset(, -1)
                        Exit Do
                    End If
                    Set fn = sh2.Range("B:B").FindNext(fn)
                Loop While fn.Address <> adr
                If fn.Address = adr And c.Offset(, 1) = "" Then
                    c.Offset(, 1) = "ITP"
                End If
            End If
        Next
    End With
End Sub

THANK YOU THANK YOU THANK YOU!!! it works perfectly!

 

[JLGWhiz]

THANK YOU THANK YOU THANK YOU!!! it works perfectly!

You're welcome,
regards, JLG

 

[Rivie]

See if this works for you

Sub t()
Dim sh1 As Worksheet, sh2 As Worksheet, c As Range, fn As Range, adr As String
Set sh1 = Sheets("Box Index")
Set sh2 = Sheets("sign out")
    With sh1
        For Each c In .Range("A2:A120")
            Set fn = sh2.Range("B:B").Find(c.Value, , xlValues, xlWhole)
            If Not fn Is Nothing Then
                adr = fn.Address
                Do
                    If Not IsDate(fn.Offset(, 2)) Then
                        c.Offset(, 1) = fn.Offset(, -1)
                        Exit Do
                    End If
                    Set fn = sh2.Range("B:B").FindNext(fn)
                Loop While fn.Address <> adr
                If fn.Address = adr And c.Offset(, 1) = "" Then
                    c.Offset(, 1) = "ITP"
                End If
            End If
        Next
    End With
End Sub

So i got into the sheet today and it no longer seems to be working ( the code). i copied it just like to put it in. and nothing has been renamed i have added entries to the sign out sheet but its not updating any more? any ideas?

 

[JLGWhiz]

If the code was working but is no longer working then either the code was changed or the data the code addresses has changed in location or type. I cannot troubleshoot your issue from here. Open the vbeditor to the code and click anywhere inside the body of the code so the insertion point will appear there, then use the F8 function key to step through the code and see if you can detect the part that is not doing what you expect it to do. You might have to resize your editor screen so you can see both the worksheet and the code while you step through. You can check your variable values with the intellisense display as the code executes by hovering the mouse pointer over the variables to activate the pop-up display.

 

[Rivie]

If the code was working but is no longer working then either the code was changed or the data the code addresses has changed in location or type. I cannot troubleshoot your issue from here. Open the vbeditor to the code and click anywhere inside the body of the code so the insertion point will appear there, then use the F8 function key to step through the code and see if you can detect the part that is not doing what you expect it to do. You might have to resize your editor screen so you can see both the worksheet and the code while you step through. You can check your variable values with the intellisense display as the code executes by hovering the mouse pointer over the variables to activate the pop-up display.

I think i found the issue. someone had made a copy of the workbook and moved the original. i did not know that that would cause a problem. I'm starting to think this project is too big for me. thank you again for your help

 

[JLGWhiz]

Thanks for the feedback,
regards, JLG