Forum Discussion
Excel VBA - Write Function() into cell using a variable for row number?
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 obtai...
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>