Hi Tom
this is great Worked perfectly as I need, but I have other case for this code,

in my qoute file , I have some spaces in the row.

this code it's not working for items after space

example.

from A4 TILL A11 NO Space in BetwEen code is working update here

IN A12 TILL A14 THIS IS AREA ONLY SPACE

IN A15 TILL A18 I HAVE OTHER ITEMS ( THIS PROBLEM THE CODE WORKING UPDATE
A11) AND NOT WORKING FOR ITEM IN A12 TILL A14

CAN YOU PLEASE HELP ME FOR THIS CASE, BECAUSE IN ALL MY QUOTE I HAVE SPACE
BETWEEN.

BEST REGARDS

ABDUL KADER

"Tom Ogilvy" wrote:

There was a stray Parenthesis in the formula. Here is the revision:


Sub BuildFormulas()
Dim rng As Range, cell As Range
Dim s As String, s1 As String
Set rng = Range("B2", Range("B2").End(xlDown))
s = "=VLOOKUP(AYYY,'C:\MyPrices\[XXX.xls]Price List'!$A$9:$B$15,2,FALSE)"
For Each cell In rng
s1 = Application.Substitute(s, "XXX", cell.Value)
s1 = Application.Substitute(s1, "YYY", cell.Row)
cell.Offset(0, 2).Formula = s1
Next
End Sub

Worked perfectly for me.

You would just make you sheet where you want the prices returned the active
sheet.

Do alt+F11 to get to the vbe, then in the VBE, do Insert=Module and paste
in the macro.

then go back to Excel and do Tools=Macro=Macros, select BuildFormulas and
click Run.

--
Regards,
Tom Ogilvy