CODEWITHSUNDEEP
vbaexcel
user2378040
user2378040

Reputation: 3

Inserting a formula containing variable into a cell using a macro

I have a vba macro that copies the current sheet and renames it with the current date that works fine, now I want it to put a formula in cell B22, the formula that I want is:

  Current Sheet       Previous Sheet
='08 May 2013'!B18 - '01-04-2013'!B18

I have been trying to get this code to work, but it is putting it in as a string not a formula i.e. = ('[08 May 2013 !R2C18] - [ 12 May 2013 !R2C18])

Dim sheet_name As String
Dim activeSheet_name As String
Dim shtName As Integer
Dim activeShtName As Integer
shtName = Sheets.Count - 1
activeShtName = Sheets.Count
sheet_name = Sheets(shtName).Name
activeSheet_name = Sheets(activeShtName).Name
Sheets(activeSheet_name).Select
Range("B22").Select
ActiveCell.Formula = " = ('[" & sheet_name & " !R2C18] - [  " & activeSheet_name & " !R2C18])"

Any help would be appreciated

Upvotes: 0

Views: 7030

Answers (1)

steveo40
steveo40

Reputation: 931

Your formula is wrong. Try it in Excel first, then copy the text of the formula and compare to the text that your last line of code above is producing. Change your VBA code to this:

ActiveCell.FormulaR1C1 = "='" & sheet_name & "'!R18C2-'" & activeSheet_name & "'!R18C2"

Note the use of FormulaR1C1. Basically you need to surround the sheet names with single quotes.

Upvotes: 2

Related Questions