How to use VBA to read data on one worksheet and input data on another

[ostrdevi]

I would like to try use VBA code that (1) will read an input that is put into A1, (2) search through the second sheet that is kind of like a master sheet of a bunch of different information, (3) find the value on the mastersheet that matches the value in A1 and find the Tare weight associated with the can value in A1. (4) then take the tare weight from the sheet 2 "mastersheet" and put it into B1, (5) and then move the information in A1 and B1 down one row so that the next can value to be scanned appears in A1 and then the code repeats it again? Here is the screenshot of the mastersheet and what the name of the different worksheets are. The worksheet titled ULD COUNT is the worksheet we would use everytime we had to do a count. The mastersheet is where all of the cans value information is.

 

[Max1616]

Hello again! Give this a try:

Sub Lookup()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim LookUpVal As String
Dim lrow As Long
Dim i As Long
Dim j As Long
Dim iLoopAmt As Integer
Dim StartColumn As Integer
Dim jlrow As Long
Dim Destlrow As Long


Set ws1 = Sheets("ULD COUNT")
Set ws2 = Sheets("MasterSheet")

LookUpVal = ws1.Range("A1")

iLoopAmt = Application.CountIf(ws2.Range("1:1"), "TARE")

If iLoopAmt = 0 Then Exit Sub

For i = 1 To iLoopAmt
    StartClm = (i * 5) - 4
    jlrow = ws2.Cells(Rows.Count, StartClm).End(xlUp).Row

    For j = 2 To jlrow
        If ws2.Cells(j, StartClm) = LookUpVal Then
            Destlrow = ws1.Cells(Rows.Count, 1).End(xlUp).Row 'Find last row in ws1
            
            ws1.Range("B1") = ws2.Cells(j, StartClm + 3) 'Populate TARE value
            ws1.Range(Cells(1, 1), Cells(Destlrow, 2)).Cut ws1.Range("A2") 'Move everything down a row
            Exit Sub
        End If
    Next j
Next i

End Sub