Search multiple sheets with vba

Rivie

New Member
Joined
Jul 29, 2020
Messages
11
Office Version
  1. 2016
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,544
Office Version
  1. 2013
Platform
  1. Windows
See if this works for you
VBA Code:
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

New Member
Joined
Jul 29, 2020
Messages
11
Office Version
  1. 2016
Platform
  1. Windows
See if this works for you
VBA Code:
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!
 

Rivie

New Member
Joined
Jul 29, 2020
Messages
11
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

See if this works for you
VBA Code:
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

Well-known Member
Joined
Feb 7, 2012
Messages
12,544
Office Version
  1. 2013
Platform
  1. Windows
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

New Member
Joined
Jul 29, 2020
Messages
11
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Feb 7, 2012
Messages
12,544
Office Version
  1. 2013
Platform
  1. Windows
Thanks for the feedback,
regards, JLG
 

Watch MrExcel Video

Forum statistics

Threads
1,114,520
Messages
5,548,529
Members
410,845
Latest member
OldSwimmer1650
Top