Hi all

Can you please help me for this Issue?

I have many Price list files, and I have other quote file.

I am make new quote for any items for Example

Item# brand Qty Price

15004 Helix 10 ??
54055 Matrix 5 ??

My question is :

How can I tell excel to open the excel file and get name file name from
brand and search for the item in that file and give me the price for that
items


Helix.xls
Matrix.xls

Etc.


Many Thanks to everybody

sName = "C:\Myfolder\Helix.xls"
set bk = workbooks.open(sname)
for each sh in bk.worksheets
set rng = sh.columns(2).Find(What:=sBrandName)
if not rng is nothing then
msgbox "Found at " & rng.address(external:=true)
myprice = rng.offset(0,2)
exit for
end if
Next

--
Regards,
Tom Ogilvy


"Akader" wrote:

Hi all

Can you please help me for this Issue?

I have many Price list files, and I have other quote file.

I am make new quote for any items for Example

Item# brand Qty Price

15004 Helix 10 ??
54055 Matrix 5 ??

My question is :

How can I tell excel to open the excel file and get name file name from
brand and search for the item in that file and give me the price for that
items


Helix.xls
Matrix.xls

Etc.


Many Thanks to everybody

Thanks Tom
sorry this don't work i need to get the Price form file name already added
in B2 each line have diffrent file name

B2 Helix
B3 Matrix
B4 Edegs

and the Item# in

A2 969
A3 598
A4 699

I need to get the Price from Each file Automaticaly and insert the Result in
each colum

E2
E3
E4


Vlookup Example:

VLOOKUP(A7;'[?????]Price List'!$A$9:$B$15;2;FALSE)

I need to Replace file name ????? with the file in B2 - B3 - B4 for each line.

I don't know if this clear to you

Thanks

"Tom Ogilvy" wrote:

sName = "C:\Myfolder\Helix.xls"
set bk = workbooks.open(sname)
for each sh in bk.worksheets
set rng = sh.columns(2).Find(What:=sBrandName)
if not rng is nothing then
msgbox "Found at " & rng.address(external:=true)
myprice = rng.offset(0,2)
exit for
end if
Next

--
Regards,
Tom Ogilvy


"Akader" wrote:

Hi all

Can you please help me for this Issue?

I have many Price list files, and I have other quote file.

I am make new quote for any items for Example

Item# brand Qty Price

15004 Helix 10 ??
54055 Matrix 5 ??

My question is :

How can I tell excel to open the excel file and get name file name from
brand and search for the item in that file and give me the price for that
items


Helix.xls
Matrix.xls

Etc.


Many Thanks to everybody

You would use Indirect in that case, but Indirect doesn't work with closed
files, so the files would have to all be open.

=VLOOKUP(A7;Indirect("'[" & B7 & "]Price List'!$A$9:$B$15");2;FALSE)

--
Regards,
Tom Ogilvy



"Akader" wrote:

Thanks Tom
sorry this don't work i need to get the Price form file name already added
in B2 each line have diffrent file name

B2 Helix
B3 Matrix
B4 Edegs

and the Item# in

A2 969
A3 598
A4 699

I need to get the Price from Each file Automaticaly and insert the Result in
each colum

E2
E3
E4


Vlookup Example:

VLOOKUP(A7;'[?????]Price List'!$A$9:$B$15;2;FALSE)

I need to Replace file name ????? with the file in B2 - B3 - B4 for each line.

I don't know if this clear to you

Thanks

"Tom Ogilvy" wrote:

sName = "C:\Myfolder\Helix.xls"
set bk = workbooks.open(sname)
for each sh in bk.worksheets
set rng = sh.columns(2).Find(What:=sBrandName)
if not rng is nothing then
msgbox "Found at " & rng.address(external:=true)
myprice = rng.offset(0,2)
exit for
end if
Next

--
Regards,
Tom Ogilvy


"Akader" wrote:

Hi all

Can you please help me for this Issue?

I have many Price list files, and I have other quote file.

I am make new quote for any items for Example

Item# brand Qty Price

15004 Helix 10 ??
54055 Matrix 5 ??

My question is :

How can I tell excel to open the excel file and get name file name from
brand and search for the item in that file and give me the price for that
items


Helix.xls
Matrix.xls

Etc.


Many Thanks to everybody

Tom many thanks

that exactly what I need, but can you help me more to take the result
without opening the file.,
it's not easy to open all files manually some time I am working on many
price list file in the same time maybe 20 file or more,

Also there is one more think in this code if the file not opened the price
which already saved before in quote file it's giving error till I open the
price list files,

Is there any way to keep result saved in the quote file and not to change
the price till I make refresh to the cell????, same as if I am using only
VLOOKUP without INDIRECT
Asking me first When opening the file if I need to update the link,

But with INDIRECT command not asking this question

So please if you dont mind find me other code to get the Result without
opening all files.


Regards

Abdul Kader





"Tom Ogilvy" wrote:

You would use Indirect in that case, but Indirect doesn't work with closed
files, so the files would have to all be open.

=VLOOKUP(A7;Indirect("'[" & B7 & "]Price List'!$A$9:$B$15");2;FALSE)

--
Regards,
Tom Ogilvy



"Akader" wrote:

Thanks Tom
sorry this don't work i need to get the Price form file name already added
in B2 each line have diffrent file name

B2 Helix
B3 Matrix
B4 Edegs

and the Item# in

A2 969
A3 598
A4 699

I need to get the Price from Each file Automaticaly and insert the Result in
each colum

E2
E3
E4


Vlookup Example:

VLOOKUP(A7;'[?????]Price List'!$A$9:$B$15;2;FALSE)

I need to Replace file name ????? with the file in B2 - B3 - B4 for each line.

I don't know if this clear to you

Thanks

"Tom Ogilvy" wrote:

sName = "C:\Myfolder\Helix.xls"
set bk = workbooks.open(sname)
for each sh in bk.worksheets
set rng = sh.columns(2).Find(What:=sBrandName)
if not rng is nothing then
msgbox "Found at " & rng.address(external:=true)
myprice = rng.offset(0,2)
exit for
end if
Next

--
Regards,
Tom Ogilvy


"Akader" wrote:

Hi all

Can you please help me for this Issue?

I have many Price list files, and I have other quote file.

I am make new quote for any items for Example

Item# brand Qty Price

15004 Helix 10 ??
54055 Matrix 5 ??

My question is :

How can I tell excel to open the excel file and get name file name from
brand and search for the item in that file and give me the price for that
items


Helix.xls
Matrix.xls

Etc.


Many Thanks to everybody

assume all the files are in the directory C:\MyPrices

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 = Replace(s,"XXX",cell.Value)
s1 = Replace(s1,"YYY",cell.row)
cell.offset(0,2).Formula = s1
Next
End Sub

--
regards,
Tom Ogilvy



"Akader" wrote in message
...
Tom many thanks

that exactly what I need, but can you help me more to take the result
without opening the file.,
it's not easy to open all files manually some time I am working on many
price list file in the same time maybe 20 file or more,

Also there is one more think in this code if the file not opened the price
which already saved before in quote file it's giving error till I open the
price list files,

Is there any way to keep result saved in the quote file and not to change
the price till I make refresh to the cell????, same as if I am using only
VLOOKUP without INDIRECT
Asking me first When opening the file if I need to update the link,

But with INDIRECT command not asking this question

So please if you don't mind find me other code to get the Result without
opening all files.


Regards

Abdul Kader





"Tom Ogilvy" wrote:

You would use Indirect in that case, but Indirect doesn't work with
closed
files, so the files would have to all be open.

=VLOOKUP(A7;Indirect("'[" & B7 & "]Price List'!$A$9:$B$15");2;FALSE)

--
Regards,
Tom Ogilvy



"Akader" wrote:

Thanks Tom
sorry this don't work i need to get the Price form file name already
added
in B2 each line have diffrent file name

B2 Helix
B3 Matrix
B4 Edegs

and the Item# in

A2 969
A3 598
A4 699

I need to get the Price from Each file Automaticaly and insert the
Result in
each colum

E2
E3
E4


Vlookup Example:

VLOOKUP(A7;'[?????]Price List'!$A$9:$B$15;2;FALSE)

I need to Replace file name ????? with the file in B2 - B3 - B4 for
each line.

I don't know if this clear to you

Thanks

"Tom Ogilvy" wrote:

sName = "C:\Myfolder\Helix.xls"
set bk = workbooks.open(sname)
for each sh in bk.worksheets
set rng = sh.columns(2).Find(What:=sBrandName)
if not rng is nothing then
msgbox "Found at " & rng.address(external:=true)
myprice = rng.offset(0,2)
exit for
end if
Next

--
Regards,
Tom Ogilvy


"Akader" wrote:

Hi all

Can you please help me for this Issue?

I have many Price list files, and I have other quote file.

I am make new quote for any items for Example

Item# brand Qty Price

15004 Helix 10 ??
54055 Matrix 5 ??

My question is :

How can I tell excel to open the excel file and get name file name
from
brand and search for the item in that file and give me the price
for that
items


Helix.xls
Matrix.xls

Etc.


Many Thanks to everybody

Dear Tom
can you please let me know how to run this code and how can I attached with
my cell?,

sorry for bothering your

"Tom Ogilvy" wrote:

assume all the files are in the directory C:\MyPrices

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 = Replace(s,"XXX",cell.Value)
s1 = Replace(s1,"YYY",cell.row)
cell.offset(0,2).Formula = s1
Next
End Sub

--
regards,
Tom Ogilvy



"Akader" wrote in message
...
Tom many thanks

that exactly what I need, but can you help me more to take the result
without opening the file.,
it's not easy to open all files manually some time I am working on many
price list file in the same time maybe 20 file or more,

Also there is one more think in this code if the file not opened the price
which already saved before in quote file it's giving error till I open the
price list files,

Is there any way to keep result saved in the quote file and not to change
the price till I make refresh to the cell????, same as if I am using only
VLOOKUP without INDIRECT
Asking me first When opening the file if I need to update the link,

But with INDIRECT command not asking this question

So please if you don't mind find me other code to get the Result without
opening all files.


Regards

Abdul Kader





"Tom Ogilvy" wrote:

You would use Indirect in that case, but Indirect doesn't work with
closed
files, so the files would have to all be open.

=VLOOKUP(A7;Indirect("'[" & B7 & "]Price List'!$A$9:$B$15");2;FALSE)

--
Regards,
Tom Ogilvy



"Akader" wrote:

Thanks Tom
sorry this don't work i need to get the Price form file name already
added
in B2 each line have diffrent file name

B2 Helix
B3 Matrix
B4 Edegs

and the Item# in

A2 969
A3 598
A4 699

I need to get the Price from Each file Automaticaly and insert the
Result in
each colum

E2
E3
E4


Vlookup Example:

VLOOKUP(A7;'[?????]Price List'!$A$9:$B$15;2;FALSE)

I need to Replace file name ????? with the file in B2 - B3 - B4 for
each line.

I don't know if this clear to you

Thanks

"Tom Ogilvy" wrote:

sName = "C:\Myfolder\Helix.xls"
set bk = workbooks.open(sname)
for each sh in bk.worksheets
set rng = sh.columns(2).Find(What:=sBrandName)
if not rng is nothing then
msgbox "Found at " & rng.address(external:=true)
myprice = rng.offset(0,2)
exit for
end if
Next

--
Regards,
Tom Ogilvy


"Akader" wrote:

Hi all

Can you please help me for this Issue?

I have many Price list files, and I have other quote file.

I am make new quote for any items for Example

Item# brand Qty Price

15004 Helix 10 ??
54055 Matrix 5 ??

My question is :

How can I tell excel to open the excel file and get name file name
from
brand and search for the item in that file and give me the price
for that
items


Helix.xls
Matrix.xls

Etc.


Many Thanks to everybody

I run this code , i get error there is no result i get ??

Also can you please let me know how to run this code and how can I attached
with my cell?,



"Akader" wrote:

Tom many thanks

that exactly what I need, but can you help me more to take the result
without opening the file.,
it's not easy to open all files manually some time I am working on many
price list file in the same time maybe 20 file or more,

Also there is one more think in this code if the file not opened the price
which already saved before in quote file it's giving error till I open the
price list files,

Is there any way to keep result saved in the quote file and not to change
the price till I make refresh to the cell????, same as if I am using only
VLOOKUP without INDIRECT
Asking me first When opening the file if I need to update the link,

But with INDIRECT command not asking this question

So please if you dont mind find me other code to get the Result without
opening all files.


Regards

Abdul Kader





"Tom Ogilvy" wrote:

You would use Indirect in that case, but Indirect doesn't work with closed
files, so the files would have to all be open.

=VLOOKUP(A7;Indirect("'[" & B7 & "]Price List'!$A$9:$B$15");2;FALSE)

--
Regards,
Tom Ogilvy



"Akader" wrote:

Thanks Tom
sorry this don't work i need to get the Price form file name already added
in B2 each line have diffrent file name

B2 Helix
B3 Matrix
B4 Edegs

and the Item# in

A2 969
A3 598
A4 699

I need to get the Price from Each file Automaticaly and insert the Result in
each colum

E2
E3
E4


Vlookup Example:

VLOOKUP(A7;'[?????]Price List'!$A$9:$B$15;2;FALSE)

I need to Replace file name ????? with the file in B2 - B3 - B4 for each line.

I don't know if this clear to you

Thanks

"Tom Ogilvy" wrote:

sName = "C:\Myfolder\Helix.xls"
set bk = workbooks.open(sname)
for each sh in bk.worksheets
set rng = sh.columns(2).Find(What:=sBrandName)
if not rng is nothing then
msgbox "Found at " & rng.address(external:=true)
myprice = rng.offset(0,2)
exit for
end if
Next

--
Regards,
Tom Ogilvy


"Akader" wrote:

Hi all

Can you please help me for this Issue?

I have many Price list files, and I have other quote file.

I am make new quote for any items for Example

Item# brand Qty Price

15004 Helix 10 ??
54055 Matrix 5 ??

My question is :

How can I tell excel to open the excel file and get name file name from
brand and search for the item in that file and give me the price for that
items


Helix.xls
Matrix.xls

Etc.


Many Thanks to everybody

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


"Akader" wrote in message
...
Dear Tom
can you please let me know how to run this code and how can I attached
with
my cell?,

sorry for bothering your

"Tom Ogilvy" wrote:

assume all the files are in the directory C:\MyPrices

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 = Replace(s,"XXX",cell.Value)
s1 = Replace(s1,"YYY",cell.row)
cell.offset(0,2).Formula = s1
Next
End Sub

--
regards,
Tom Ogilvy



"Akader" wrote in message
...
Tom many thanks

that exactly what I need, but can you help me more to take the result
without opening the file.,
it's not easy to open all files manually some time I am working on
many
price list file in the same time maybe 20 file or more,

Also there is one more think in this code if the file not opened the
price
which already saved before in quote file it's giving error till I open
the
price list files,

Is there any way to keep result saved in the quote file and not to
change
the price till I make refresh to the cell????, same as if I am using
only
VLOOKUP without INDIRECT
Asking me first When opening the file if I need to update the link,

But with INDIRECT command not asking this question

So please if you don't mind find me other code to get the Result
without
opening all files.


Regards

Abdul Kader





"Tom Ogilvy" wrote:

You would use Indirect in that case, but Indirect doesn't work with
closed
files, so the files would have to all be open.

=VLOOKUP(A7;Indirect("'[" & B7 & "]Price List'!$A$9:$B$15");2;FALSE)

--
Regards,
Tom Ogilvy



"Akader" wrote:

Thanks Tom
sorry this don't work i need to get the Price form file name
already
added
in B2 each line have diffrent file name

B2 Helix
B3 Matrix
B4 Edegs

and the Item# in

A2 969
A3 598
A4 699

I need to get the Price from Each file Automaticaly and insert the
Result in
each colum

E2
E3
E4


Vlookup Example:

VLOOKUP(A7;'[?????]Price List'!$A$9:$B$15;2;FALSE)

I need to Replace file name ????? with the file in B2 - B3 - B4
for
each line.

I don't know if this clear to you

Thanks

"Tom Ogilvy" wrote:

sName = "C:\Myfolder\Helix.xls"
set bk = workbooks.open(sname)
for each sh in bk.worksheets
set rng = sh.columns(2).Find(What:=sBrandName)
if not rng is nothing then
msgbox "Found at " & rng.address(external:=true)
myprice = rng.offset(0,2)
exit for
end if
Next

--
Regards,
Tom Ogilvy


"Akader" wrote:

Hi all

Can you please help me for this Issue?

I have many Price list files, and I have other quote file.

I am make new quote for any items for Example

Item# brand Qty Price

15004 Helix 10 ??
54055 Matrix 5 ??

My question is :

How can I tell excel to open the excel file and get name file
name
from
brand and search for the item in that file and give me the
price
for that
items


Helix.xls
Matrix.xls

Etc.


Many Thanks to everybody

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

Hi Any Solutions?

"Akader" wrote:

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: