Hello all,
I have a little predicament where I had a large spreadsheet that
would take values from other sheets and insert them into the last row.
Everything worked fine, but the person organizing the little project
changed the requirements on me last second and decided it would be
better to have a formula in each cell referencing the files rather than
have the values. The easiest way to do this would be to make a string
representing the formula (it requires concatenation of three different
things) and then put that into the the cell. However, this is where
things get tricky for me. I vaguely know of a eval() method to make a
formula from a string, but Im not sure if this would work in a
lastrow.offset(1,0) = <insert code here type of situation.

Heres a snippet of the original code that would need to be changed

Dim lastrow as object
Dim surveyWorkbook as Workbook, currentWorkbook as Workbook
Dim surveyWS as Worksheet

..
..
..
With currentWorkbook
Set Lastrow = Sheet1.Range("a65536").End(xlUp)
'write the data to the new sheets
Lastrow.Offset(1, 0) = surveyWorkbook.Name
Lastrow.Offset(1, 1) = surveyWS.Range("b4").Value
Lastrow.Offset(1, 2) = surveyWS.Range("b5").Value

End With


I would have a ready made concatonated string with the formula, the
last row set, and now all that would remain would be to find a way to
get that formula into all the correct cells. If you know of a way to do
this. Or if the answer is so simple its staring me in the face, feel
free to be the first to let me know.(with a solution of course)

Thank you for any help in advance,
Rob

something along the lines of

Lastrow.Offset(1, 1).formula = "='" & surveyWS.name & "'!b4"


"RoVo" wrote:

Hello all,
I have a little predicament where I had a large spreadsheet that
would take values from other sheets and insert them into the last row.
Everything worked fine, but the person organizing the little project
changed the requirements on me last second and decided it would be
better to have a formula in each cell referencing the files rather than
have the values. The easiest way to do this would be to make a string
representing the formula (it requires concatenation of three different
things) and then put that into the the cell. However, this is where
things get tricky for me. I vaguely know of a eval() method to make a
formula from a string, but Im not sure if this would work in a
lastrow.offset(1,0) = <insert code here type of situation.

Heres a snippet of the original code that would need to be changed

Dim lastrow as object
Dim surveyWorkbook as Workbook, currentWorkbook as Workbook
Dim surveyWS as Worksheet

..
..
..
With currentWorkbook
Set Lastrow = Sheet1.Range("a65536").End(xlUp)
'write the data to the new sheets
Lastrow.Offset(1, 0) = surveyWorkbook.Name
Lastrow.Offset(1, 1) = surveyWS.Range("b4").Value
Lastrow.Offset(1, 2) = surveyWS.Range("b5").Value

End With


I would have a ready made concatonated string with the formula, the
last row set, and now all that would remain would be to find a way to
get that formula into all the correct cells. If you know of a way to do
this. Or if the answer is so simple its staring me in the face, feel
free to be the first to let me know.(with a solution of course)

Thank you for any help in advance,
Rob

I think its some like this:

With currentWorkbook
Set Lastrow = Sheet1.Range("a65536").End(xlUp)
'write the data to the new sheets
Lastrow.Offset(1, 0) = surveyWorkbook.Name
Lastrow.Offset(1, 1).Formula = "surveyWS!b4"
Lastrow.Offset(1, 2).Formula = "surveyWS!b5"

End With