Help entering R1C1 formula using VBA

[garyalan59]

Using Excel 2007 and VBA. I need to enter formulas into multiple cells of a worksheet referencing cells on another worksheet in the same workbook. The cells in the source worksheet are not contiguous, so copy/paste an existing formula on the destination worksheet will not work. I recorded a macro as a starting point, but when I alter the macro to loop through all the existing cells where I need the formula, the code throws a "Run-time error '1004': Application-defined or object-defined error."

The functional line in the recorded macro looks like this:
ActiveCell.FormulaR1C1 = "=IF('Time Cards'!R[6]C[-6]="""","""",IF('Time Cards'!R[6]C[-6]=""N"",""N"",'Time Cards'!R[6]C))"

The altered code is as follows:

Sub EnterFormula()
i = 6
j = -6
[FONT=&quot] [/FONT]k = 1
[FONT=&quot] [/FONT]l = 1

[FONT=&quot] [/FONT]For k = 5 To 24
[FONT=&quot] [/FONT][FONT=&quot] [/FONT]For l = 9 To 25
[FONT=&quot] [/FONT][FONT=&quot] [/FONT][FONT=&quot] [/FONT]Cells(k, l).Activate
[FONT=&quot] [/FONT][FONT=&quot] [/FONT][FONT=&quot] [/FONT]ActiveCell.FormulaR1C1 = _
[FONT=&quot] [/FONT][FONT=&quot] [/FONT][FONT=&quot] [/FONT][FONT=&quot] [/FONT]"=IF('Time Cards'!RC[j]="""","""",IF('Time Cards'!RC[j]=""N"",""N"",'Time Cards'!RC[j+6]))"
[FONT=&quot] [/FONT][FONT=&quot] [/FONT][FONT=&quot] [/FONT]i = i + 17
[FONT=&quot] [/FONT][FONT=&quot] [/FONT]Next
[FONT=&quot] [/FONT][FONT=&quot] [/FONT]i = 6
[FONT=&quot] [/FONT][FONT=&quot] [/FONT]j = j + 8
[FONT=&quot] [/FONT]Next
End Sub


it is the ActiveCell.FormulaR1C1 line that throws the error.

Any assistance is most welcome. Otherwise, I'll be entering formulas one at a time for a week...

 

[ScottUlmer]

it looks like you are trying to have the vba insert a formula with a quotation mark ("). What you need to do is & """" &. and example of what you need to do is =" &""""& N &""""& ","

 

[dk]

Hi

You need to move the variable references (i and j) outside of the string something like this:

ActiveCell.FormulaR1C1 = _
"=IF('Time Cards'!R[" & i & "]C[" & j & "]="""","""",IF('Time Cards'!R[" & i & "]C[" & j & "]=""N"",""N"",'Time Cards'!R[" & i & "]C[" & j + 6 & "]))"

Your previous code is putting the literal value "i" rather than a number which is what it needs.

HTH
DK

 

[garyalan59]

dk - thank you. I knew it must be something simple like that, just having a brain block or something.