Cell strings as cells

[theoryboy]

I am writing a VB application that builds a function call as a string
and pastes it into a cell. I can get get Excel to do one of two things:

(i) Evaluate it as a cell: look in that spot for values.

(ii) Evaluate it as a string giving a #name error.

As an example:

Sub Tester()
Dim myRng As Range, sCell As String
Dim myfunc As String
sCell = "F19"
Set myRng = Range(sCell)

' this one places the value from F19 into the call
myfunc = "=Sum(" + myRng + ")"
' this one places the string 'F19' into the call
myfunc = "=Sum(" + sCell + ")"

ActiveCell.FormulaR1C1 = myfunc
End Sub

So, assuming I have the value 39 in cell F19, I either get =SUM(39) in
the target cell, or =SUM('F19'). What I want is =SUM(F19).

Any ideas?

Peter

 

[Bob Phillips]

myfunc = "=Sum(" & sCell & ")"


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Tushar Mehta]

While the consensus is that one uses & for concatenation and reserves +
for arithmetic addition, that is not the issue here.

You are specifing F39 in a formula entered through FormulaR1C1! Use
the correct syntax and XL will buy it just A-OK.

ActiveCell.Formula = myfunc

or, for an absolute reference to F19,

sCell = "R19C6"
myfunc = "=Sum(" + sCell + ")"
ActiveCell.FormulaR1C1 = myfunc

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions