Excel VBA - Write Function() into cell using a variable for row number?

[dlowrey2120]

Hello, thanks for looking.

I have a function GetSomething() that I want to write into a cell using VBA. The cell is found by using a variable "lastrow", representing the last row number + 1 to obtain an empty row.
I have had no problem writing values into cells. However, I want the Function to be in the cell so that it executes from the cell. Using ".Value" would execute the function immediately, because data in Col C has not yet been entered, so I used Formula instead.

The only parameter to GetSomething() is a value in column C on the same row. If typing it in manually, the cell would contain this:
=GetSomething(C19) or whatever row number.

Here is what I tried, that is not working.
'ActiveSheet.Cells(lastrow, "D").Formula = "=GetSomething("C" & lastrow)"
'ActiveSheet.Cells(lastrow, "D").Formula = GetSomething("C" & lastrow)
'ActiveSheet.Cells(lastrow, "D").Formula = "=GetSometh("C" & lastrow)"
'ActiveSheet.Cells(lastrow, "D").Formula = "=GetSomething("C", "& lastrow")"

I am stuck. Thanks for your time and help.
-DL

Macros and VBAing, Formulas and Functions

[#2]

ActiveSheet.Cells(lastrow, "D").Formula = "=GetSomething("C" & lastrow & ")"

[#3]

@dlowrey2120 The problem is in getting the quotes right – you need one pair for the start and end of each component string literal:

ActiveSheet.Cells(lastrow, "D").Formula = "=GetSomething(C" & lastrow & ")"

If you had a different formula that required quotes within the formula – which your example does not need – you would specify two consecutive quotes at each location where the formula needed a quote; e.g., to sum column B values greater than five in rows 2 through lastrow:

ActiveSheet.Cells(lastrow, "Z").Formula = "=SUMIF(B2:B" & lastrow & ","">5"")"

Note that referencing ActiveSheet multiple times in a VBA procedure can cause problems.  Realize what happens if the user clicks the tab for a different worksheet while the procedure is running.  It is better to capture a reference to the active sheet at the start of the procedure, and then use that reference elsewhere in the procedure.

Dim objInitialSheet  As Worksheet  'Of course, you might use a more meaningful name.
Set objInitialSheet = ActiveSheet
'...and then as needed...
objInitialSheet.Cells(lastrow, "C").Value = <whatever>
objInitialSheet.Cells(lastrow, "D").Formula = <whatever>