filling cells via Macro with a Vlookupfunction

[Azin05]

Hi Y' all

I'm currently trying to write a macro that will fill a range of cells with
the same vlookup-formula. results should be visible in the sheet. The problem
however is that the worksheet in which the formula needs to lookup in ( the
table array) will vary daily. to solve this obstacle i created the following:

Dim currentfile As String
currentfile = ActiveWorkbook.Name
Workbooks.Open Filename:="S:\...\SAP Data\download.xls"

(the macro itself is stored in the file in which it needs to look and is
therefor already open and active( before the download.xls file is opened))

The vlookupfunction is as follows:

ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-13],'[planning pharma v1.005
11-07-07_macrobouw.xls]SAP'!R2C1:R509C1,1,0)"

and its the [planning pharma v1.005 11-07-07_macrobouw.xls] that should be
replaced with the currentfile dimension
so far I've tried:
'[&currentfile&]
'[&currentfile]
'[currentfile]
'["&currentfile]

but all of these give either an error in VBA or when F8-ing myself through
it the macro want me to locate the file.

any body got a clue on how I can integrate the dim into the formula?

2nd question concers the R2C1:R509C1 section.
this table will not be limited to the current number of rows but wil expand
and therefor should be a flexible array. ( just stating R32000 is a but crude
solution, but it would work) I'm a bit lost here as well, any one got an idea?

Many thanks in advance

Azin05

 

[Joel]

I often have problem like this especially when you are using the single
quotes. You have spaces in the file name which require the single quotes.

I would build my formula string in pieces as follows

Myformula = "=VLOOKUP(RC[-13],'[" & currentfile & "SAP'!R2C1:R509C1,1,0)"
ActiveCell.FormulaR1C1 = Myformula

 

[Azin05]

HI joel,
thanks for the comment, however, i'm not using the single quotes because I
like to, only because this is the line I recorded.
If theres a different solution I'm keen to find it cause it still causes
errors.
if Myformula read "=1+5" i get 6 in the cell in the worksheet. so the
definition works.
its something else in the
"=VLOOKUP(RC[-13],'[" & currentfile & "]SAP'!R2C1:R509C1,1,0)"
thats causing the run-time error '1104': application-defined or
object-defined error

got any more good ideas?
thanks in advance

Azin05

I often have problem like this especially when you are using the single
quotes. You have spaces in the file name which require the single quotes.

I would build my formula string in pieces as follows

Myformula = "=VLOOKUP(RC[-13],'[" & currentfile & "SAP'!R2C1:R509C1,1,0)"
ActiveCell.FormulaR1C1 = Myformula

Hi Y' all

I'm currently trying to write a macro that will fill a range of cells with
the same vlookup-formula. results should be visible in the sheet. The problem
however is that the worksheet in which the formula needs to lookup in ( the
table array) will vary daily. to solve this obstacle i created the following:

Dim currentfile As String
currentfile = ActiveWorkbook.Name
Workbooks.Open Filename:="S:\...\SAP Data\download.xls"

(the macro itself is stored in the file in which it needs to look and is
therefor already open and active( before the download.xls file is opened))

The vlookupfunction is as follows:

ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-13],'[planning pharma v1.005
11-07-07_macrobouw.xls]SAP'!R2C1:R509C1,1,0)"

and its the [planning pharma v1.005 11-07-07_macrobouw.xls] that should be
replaced with the currentfile dimension
so far I've tried:
'[¤tfile&]
'[¤tfile]
'[currentfile]
'["¤tfile]

but all of these give either an error in VBA or when F8-ing myself through
it the macro want me to locate the file.

any body got a clue on how I can integrate the dim into the formula?

2nd question concers the R2C1:R509C1 section.
this table will not be limited to the current number of rows but wil expand
and therefor should be a flexible array. ( just stating R32000 is a but crude
solution, but it would work) I'm a bit lost here as well, any one got an idea?

Many thanks in advance

Azin05

 

[Joel]

I ran this code below using an empty workbook "a B c".xls: and did not get
any errors. I tried a few things and finally got your error. It only occurs
if currentfile is null. If you take the code below and commentt out the
assignment of currentfile you will get the error. if currentt file is
deffine like below the code work great.

Sub test()

currentfile = "l:\temp\a b c.xls"
myf = "=VLOOKUP(RC[-13],'[" & currentfile & "]SAP'!R2C1:R509C1,1,0)"
ActiveCell.FormulaR1C1 = myf

End Sub

HI joel,
thanks for the comment, however, i'm not using the single quotes because I
like to, only because this is the line I recorded.
If theres a different solution I'm keen to find it cause it still causes
errors.
if Myformula read "=1+5" i get 6 in the cell in the worksheet. so the
definition works.
its something else in the
"=VLOOKUP(RC[-13],'[" & currentfile & "]SAP'!R2C1:R509C1,1,0)"
thats causing the run-time error '1104': application-defined or
object-defined error

got any more good ideas?
thanks in advance

Azin05

I often have problem like this especially when you are using the single
quotes. You have spaces in the file name which require the single quotes.

I would build my formula string in pieces as follows

Myformula = "=VLOOKUP(RC[-13],'[" & currentfile & "SAP'!R2C1:R509C1,1,0)"
ActiveCell.FormulaR1C1 = Myformula

Hi Y' all

I'm currently trying to write a macro that will fill a range of cells with
the same vlookup-formula. results should be visible in the sheet. The problem
however is that the worksheet in which the formula needs to lookup in ( the
table array) will vary daily. to solve this obstacle i created the following:

Dim currentfile As String
currentfile = ActiveWorkbook.Name
Workbooks.Open Filename:="S:\...\SAP Data\download.xls"

(the macro itself is stored in the file in which it needs to look and is
therefor already open and active( before the download.xls file is opened))

The vlookupfunction is as follows:

ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-13],'[planning pharma v1.005
11-07-07_macrobouw.xls]SAP'!R2C1:R509C1,1,0)"

and its the [planning pharma v1.005 11-07-07_macrobouw.xls] that should be
replaced with the currentfile dimension
so far I've tried:
'[¤tfile&]
'[¤tfile]
'[currentfile]
'["¤tfile]

but all of these give either an error in VBA or when F8-ing myself through
it the macro want me to locate the file.

any body got a clue on how I can integrate the dim into the formula?

2nd question concers the R2C1:R509C1 section.
this table will not be limited to the current number of rows but wil expand
and therefor should be a flexible array. ( just stating R32000 is a but crude
solution, but it would work) I'm a bit lost here as well, any one got an idea?

Many thanks in advance

Azin05

 

[Azin05]

thanks a lot joel,

it seems to be working now

regards

I ran this code below using an empty workbook "a B c".xls: and did not get
any errors. I tried a few things and finally got your error. It only occurs
if currentfile is null. If you take the code below and commentt out the
assignment of currentfile you will get the error. if currentt file is
deffine like below the code work great.

Sub test()

currentfile = "l:\temp\a b c.xls"
myf = "=VLOOKUP(RC[-13],'[" & currentfile & "]SAP'!R2C1:R509C1,1,0)"
ActiveCell.FormulaR1C1 = myf

End Sub

HI joel,
thanks for the comment, however, i'm not using the single quotes because I
like to, only because this is the line I recorded.
If theres a different solution I'm keen to find it cause it still causes
errors.
if Myformula read "=1+5" i get 6 in the cell in the worksheet. so the
definition works.
its something else in the
"=VLOOKUP(RC[-13],'[" & currentfile & "]SAP'!R2C1:R509C1,1,0)"
thats causing the run-time error '1104': application-defined or
object-defined error

got any more good ideas?
thanks in advance

Azin05

I often have problem like this especially when you are using the single
quotes. You have spaces in the file name which require the single quotes.

I would build my formula string in pieces as follows

Myformula = "=VLOOKUP(RC[-13],'[" & currentfile & "SAP'!R2C1:R509C1,1,0)"
ActiveCell.FormulaR1C1 = Myformula

:

Hi Y' all

I'm currently trying to write a macro that will fill a range of cells with
the same vlookup-formula. results should be visible in the sheet. The problem
however is that the worksheet in which the formula needs to lookup in ( the
table array) will vary daily. to solve this obstacle i created the following:

Dim currentfile As String
currentfile = ActiveWorkbook.Name
Workbooks.Open Filename:="S:\...\SAP Data\download.xls"

(the macro itself is stored in the file in which it needs to look and is
therefor already open and active( before the download.xls file is opened))

The vlookupfunction is as follows:

ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-13],'[planning pharma v1.005
11-07-07_macrobouw.xls]SAP'!R2C1:R509C1,1,0)"

and its the [planning pharma v1.005 11-07-07_macrobouw.xls] that should be
replaced with the currentfile dimension
so far I've tried:
'[¤tfile&]
'[¤tfile]
'[currentfile]
'["¤tfile]

but all of these give either an error in VBA or when F8-ing myself through
it the macro want me to locate the file.

any body got a clue on how I can integrate the dim into the formula?

2nd question concers the R2C1:R509C1 section.
this table will not be limited to the current number of rows but wil expand
and therefor should be a flexible array. ( just stating R32000 is a but crude
solution, but it would work) I'm a bit lost here as well, any one got an idea?

Many thanks in advance

Azin05