Lookup help needed

[Sinner]

How do I modify the receipt number so that it can loop through all the
receipt numbers in columnG of sheet1 and yield result.
------------------------------------------------------------------------------------
Option Explicit
Sub Testme()

Dim MstrWks As Worksheet
Dim StockNumWks As Worksheet
Dim FormRng As Range
Dim VLookUpAddr As String
Dim LastRow As Long
Dim i As Variant
Dim Receipt As Variant

Set MstrWks = Worksheets("sheet1")
Set StockNumWks = Worksheets("sheet2")

With MstrWks
LastRow = .Cells(.Rows.Count, "G").End(xlUp).Row
Set FormRng = .Range("P2" & LastRow)
End With


VLookUpAddr = StockNumWks.Range("C:F").Address(external:=True)


With FormRng
'turn calculation to manual before plopping in the formulas
Application.Calculation = xlManual
.Formula = "=vlookup(" & Receipt & "," & VLookUpAddr & ",
4,false)"
'back to automatic
Application.Calculation = xlAutomatic


'convert to values
.Copy
.PasteSpecial Paste:=xlPasteValues


'remove those marching ants/marquee
Application.CutCopyMode = False


'get rid of no match and empty cells that came back as 0's
.Replace what:="#n/a", replacement:="", _
lookat:=xlWhole, searchorder:=xlByRows, _
MatchCase:=False
.Replace what:="0", replacement:="", _
lookat:=xlWhole, searchorder:=xlByRows, _
MatchCase:=False
End With

End Sub

 

[Sinner]

How do I modify the receipt number so that it can loop through all the
receipt numbers in columnG of sheet1 and yield result.
---------------------------------------------------------------------------­---------
Option Explicit
Sub Testme()

    Dim MstrWks As Worksheet
    Dim StockNumWks As Worksheet
    Dim FormRng As Range
    Dim VLookUpAddr As String
    Dim LastRow As Long
    Dim i As Variant
    Dim Receipt As Variant

    Set MstrWks = Worksheets("sheet1")
    Set StockNumWks = Worksheets("sheet2")

    With MstrWks
        LastRow = .Cells(.Rows.Count, "G").End(xlUp).Row
        Set FormRng = .Range("P2" & LastRow)
    End With

    VLookUpAddr = StockNumWks.Range("C:F").Address(external:=True)

    With FormRng
        'turn calculation to manual before plopping in the formulas
        Application.Calculation = xlManual
        .Formula = "=vlookup(" & Receipt & "," & VLookUpAddr &",
4,false)"
        'back to automatic
        Application.Calculation = xlAutomatic

        'convert to values
        .Copy
        .PasteSpecial Paste:=xlPasteValues

        'remove those marching ants/marquee
        Application.CutCopyMode = False

        'get rid of no match and empty cells that came back as 0's
        .Replace what:="#n/a", replacement:="", _
            lookat:=xlWhole, searchorder:=xlByRows, _
            MatchCase:=False
        .Replace what:="0", replacement:="", _
            lookat:=xlWhole, searchorder:=xlByRows, _
            MatchCase:=False
    End With

End Sub

One more thing,

It is obvious form the code that I have two sheets.
I want to add sheet3 and get values from that for values that are in
columnD of sheet1.

Like:
sheet1 columnG with sheet2 table(C:F) and result in columnP of sheet1
sheet1 columnD with sheet3 table(A:B) and result in columnQ of sheet1

Should be able to do with same macro.
Thx.