KenCubs


I am creating a formula for an Excel cell in the macro. The address that I want to put in the formula is the evaluation of an expression in the macro (cell(x,y).address). How do I get VBA to put the value of the expression into the formula.

Right now, the resulting Excel cell looks like this: =sum(1,-cell(x,y).address) . I want: =sum(1,-a3)




Re: Putting formulas in Excel cells via a macro

Andy Pope


Hi,

Something like this maybe.

Sub Test()
Dim x,y

x = 3
y = 1

ActiveCell.Formula = "=sum(1,-" & Cells(x, y).Address(False, False) & ")"

End Sub






Re: Putting formulas in Excel cells via a macro

KenCubs

Thank you, it works perfectly. I had no idea that those concatenation operators cause the expression to be evaluated. Cubs are up 2-1 in the 9th as we speak. This fix will be tremendously helpful to me.

Thanks for the quick reply, Andy!