Evaluation of variable before setting it as a formula.

[Question_123]

Hi group,

I am trying to set a formula in a macro and im not having much luck
with it. If I hard code the directory path it works fine. However I
dont want to hard code it if i dont have to. When I debug the macro
myRange is corect, but the formula did not translate myRange to the
value of myRange in the VLOOKUP. Any help would be appreciated.

Thanks.

Sub Make_Reports()

Dim iReport As Long
Dim lRow As Long
Dim i As Integer
Dim X As Integer
Dim wsReport As Worksheet
Dim wsData As Worksheet
Dim myRange As Variant

iReport = 1
lRow = 1
Set wsData = ActiveSheet
Application.ScreenUpdating = False

Do While Not IsEmpty(wsData.Cells(lRow, 1))

'add new worksheet for new report
Set wsReport = Worksheets.Add
wsReport.Name = wsData.Cells(lRow, 2)

'copy data (26 rows * 9 columns)
wsData.Cells(lRow, 1).Resize(26, 9).Copy _
Destination:=wsReport.Range("A5")

'directory path.
myRange = Application.ActiveWorkbook.Path &
"\[open.xls]Summary!$A$1:$D$10"

'Additional formula to retrive opening values from another
workbook.

wsReport.Range("C6").Formula = "=VLOOKUP(C3,myRange,3,FALSE)"

wsReport.Range("E6").Formula = _
"=VLOOKUP(C3,'C:\TESTEXCEL\[open.xls]Summary'!$A$1:$D$10,4,FALSE)"

'Increment Counter
lRow = lRow + 26

Loop

End Sub

 

[Juan Pablo González]

You need to concatenate the formula with your string variable, 'cause Excel
can't see it:

wsReport.Range("C6").Formula = "=VLOOKUP(C3," & myRange & ",3,FALSE)"

 

[Mr. Fitz]

I tried your suggestion and it would not work. So then I tried to creat
a variable and created the formula there, then i tried to make it work
.. and it failed. Any more suggestions.

This is what I did. I am using 97 if that helps.

Dim myFormula As Variant
..
..
..
'directory path.
myRange = Application.ActiveWorkbook.Path &
"\[open.xls]Summary!$B$7:$D$10"
myFormula = "=VLOOKUP(C3," & myRange & ",3,FALSE)"

'Additional formula to retrive opening values from another workbook.
wsReport.Range("C6").Formula = myFormula

This gives me runtime error. 1004. Application defined or
object-defined error.

Thanks.



You need to concatenate the formula with your string variable, 'cause
Excel
can't see it:

wsReport.Range("C6").Formula = "=VLOOKUP(C3," & myRange & ",3,FALSE)"


--
Regards

Juan Pablo González

Hi group,

I am trying to set a formula in a macro and im not having much luck
with it. If I hard code the directory path it works fine. However I
dont want to hard code it if i dont have to. When I debug the macro
myRange is corect, but the formula did not translate myRange to the
value of myRange in the VLOOKUP. Any help would be appreciated.

Thanks.

Sub Make_Reports()

Dim iReport As Long
Dim lRow As Long
Dim i As Integer
Dim X As Integer
Dim wsReport As Worksheet
Dim wsData As Worksheet
Dim myRange As Variant

iReport = 1
lRow = 1
Set wsData = ActiveSheet
Application.ScreenUpdating = False

Do While Not IsEmpty(wsData.Cells(lRow, 1))

'add new worksheet for new report
Set wsReport = Worksheets.Add
wsReport.Name = wsData.Cells(lRow, 2)

'copy data (26 rows * 9 columns)
wsData.Cells(lRow, 1).Resize(26, 9).Copy _
Destination:=wsReport.Range("A5")

'directory path.
myRange = Application.ActiveWorkbook.Path &
"\[open.xls]Summary!$A$1:$D$10"

'Additional formula to retrive opening values from another
workbook.

wsReport.Range("C6").Formula = "=VLOOKUP(C3,myRange,3,FALSE)"

wsReport.Range("E6").Formula = _
"=VLOOKUP(C3,'C:\TESTEXCEL\[open.xls]Summary'!$A$1:$D$10,4,FALSE)"

'Increment Counter
lRow = lRow + 26

Loop

End Sub

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!