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.

On Apr 21, 1:03*am, Sinner wrote:
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.

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.