Solved: VBA code to insert a formula

[Marcster]

What's the VBA code to insert a formula in cells I2:I500?.

Thanks,

Marcster.

[austenr]

Sub InsertFormula()

Application.Calculation = xlCalculationManual

Range("A1").FormulaR1C1 = "Your formula here"

Range("A1").Copy Destination:=Range("I2:I500")

Range("I2:I500").PasteSpecial Paste:=xlPasteFormulas

Application.Calculation = xlCalculationAutomatic

End Sub

[Bob Phillips]

What's the VBA code to insert a formula in cells I2:I500?.

Thanks,

Marcster.

Range("I2:I500").Formula = yourformula

[mvidas]

Hey Marcster,

Although it could change depending on the actual formula being entered...[vba]Range("I2:I500").Formula = "=1+1"[/vba]Matt

[mvidas]

Austenr,

Why turn off calculation before entering the formula?

Also, you might find it easier to use a format like[vba]Sub InsertFormula()
With Range("I2:I500")
.FormulaR1C1 = "Your formula here"
.Value = .Value
End If
End Sub[/vba]Matt

[Marcster]

Thanks for all the replies guys .

Another thread solved.


Marcster.

[Bob Phillips]

Also, you might find it easier to use a format like[vba]Sub InsertFormula()
With Range("I2:I500")
.FormulaR1C1 = "Your formula here"
.Value = .Value
End If
End Sub[/vba]Matt

He isn't pasting values.

[mvidas]

Ha! Goes to show you that reading isnt exactly overrated

I suppose I just dont get it then

[austenr]

OOPS....

[Aaron Blood]

Along those lines...

I built something for myself that instantly converts any cell formula to the R1C1 VBA syntax. Granted, not particularly difficult, but handy automating the drudge task; particularly if you're dealing with a lot of double quotes in the formulas that have to be doubled up for VBA usage.

Anyway... I figured a few of the reg'lars over here might appreciate it. It's something I keep on a button in my own toolbox.

[VBA]
Sub Get_VBA_Formula()

Dim VBA_Formula As String, n$, x$, msg As String
Dim i As Integer

VBA_Formula = ActiveCell.FormulaR1C1Local

'double quote substitution
For i = 1 To Len(VBA_Formula)
n$ = Mid(VBA_Formula, i, 1)
If n$ = """" Then
x$ = x$ & """"""
Else
x$ = x$ & n$
End If
Next i

'Post formula to InputBox
VBA_Formula = """" & x$ & """"
msg = "Cell Formula to VBA Conversion" & vbCrLf & vbCrLf & ActiveCell.Formula
Application.SendKeys ("^c")
VBA_Formula = InputBox(msg, "Get VBA Formula", VBA_Formula)

End Sub
[/VBA]

Note: after you press the OK button on the input box that pops up, the formula is automatically copied to the clipboard. So all you need to do is close the inputbox and go paste into your VBA FormulaR1C1 line.

[Ken Puls]

I built something for myself that instantly converts any cell formula to the R1C1 VBA syntax. Granted, not particularly difficult, but handy automating the drudge task; particularly if you're dealing with a lot of double quotes in the formulas that have to be doubled up for VBA usage.

LOL!

And here I always do it the hard way... I like it!

[austenr]

Since we are dealing with formulas and some of us are more slow than others (me), how can i suppress the #N/A using iserror inthis formula:

=INDEX($A:$A,MATCH(C1,$A:$A,0))

Thanks

[mvidas]

Austenr,

=IF(ISERROR(INDEX($A:$A,MATCH(C1,$A:$A,0))),"",INDEX($A:$A,MATCH(C1,$A:$A,0 )))

Btw - Me saying "reading isnt exactly overrated" earlier wasn't negative towards you, I was saying I apparently hadnt fully read your code and just assumed you were pasting the values based on glancing at it .. my fault, not yours

[austenr]

Thats ok. Thanks for clearing up the formula. So in escence, you need to do the INDEX and MATCH 2 times with a " " seperator. Now I see.

[Bob Phillips]

Thats ok. Thanks for clearing up the formula. So in escence, you need to do the INDEX and MATCH 2 times with a " " seperator. Now I see.

No, he is just testing to see whether that formula errors, if so, out put a space, otherwise return the form ula result.

[Cass]

I have also problem with inserting formula
[VBA]
Sheets("Koond").Range("H10:H" & lRow + 8).Formula = "=SUM(abileht!A10:A30)"
'Work but
Sheets("Koond").Range("H10:H" & lRow + 8).Formula = "=SUMIF(abileht!$I$2:$I$600;C10;abileht!$C$2:$C$600)"
'Dosn't work and got run-time error 1004
'(NB! in my location is inside formula ; not ,)
[/VBA]

[Ken Puls]

Hi Cass,

That's interesting... I've never seen the ; as a separator before. I copied your code, changed the ; to , and ran it with no issue here. I wonder if the VBE needs it written in English to interpret it. Have you tried this?

[vba]Sheets("Koond").Range("H10:H" & lrow + 8).Formula = "=SUMIF(abileht!$I$2:$I$600,C10,abileht!$C$2:$C$600)"[/vba]

Also, you're sure that you're sheets are named correctly and that "Koond" is not a chart sheet, yes?

[Cass]

Wonderful All the sheets name is correct. If i change the separators , then it's work. But if i look now formula in worksheet there are ; separators

[Ken Puls]

Great! Glad it worked!

It's interesting to me that MS would change the formula condition separator in the Excel interface. I see you're from Estonia, what language Excel are you using?

[Cass]

Great! Glad it worked!

It's interesting to me that MS would change the formula condition separator in the Excel interface. I see you're from Estonia, what language Excel are you using?

English version Office and Windows but regional settings in OS are Estonian and there are the differences