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:P" & 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

-------------------------
This is a modified of original.