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...Range("I2:I500").Formula = "=1+1"Matt

[mvidas]

Austenr,

Why turn off calculation before entering the formula?

Also, you might find it easier to use a format likeSub InsertFormula()
With Range("I2:I500")
.FormulaR1C1 = "Your formula here"
.Value = .Value
End If
End SubMatt

[Marcster]

Thanks for all the replies guys :thumb.

Another thread solved.
:friends:

Marcster.

[Bob Phillips]

Also, you might find it easier to use a format likeSub InsertFormula()
With Range("I2:I500")
.FormulaR1C1 = "Your formula here"
.Value = .Value
End If
End SubMatt

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....:mkay

[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.


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


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 http://vbaexpress.com/forum/images/smilies/102.gif

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 ,)
http://vbaexpress.com/forum/images/smilies/think.gif

[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?

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

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

[Cass]

Wonderful :clap: 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:thumb

[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

[Ken Puls]

I think I'm even more surprised now than I was about finding out about the ; in the first place! :rotlaugh:

[Bob Phillips]

Wonderful :clap: 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:thumb

That is because VBA works in pure English (well actually, very impure English, as understood by Americans), but it translates to the local settings when writing to cells.

It gets even better with functions. If you have a foreign language version of Excel, and use

Activecell.Formula = "=SUM(A1:B1,G1)"

in VBA, it will translate, so in French it will show on the worksheet as

=SOMME(A1:B1;G1)

[Ken Puls]

Okay, so here's what I do understand about that and what I don't.

Learning VBA is learning a new language anyway. So not translating the English, I could sort of make an argument for, I guess. It would seem odd with functions, though, but I guess it makes it easier to deploy code worldwide.

I distinctly remember going to XL-Dennis's Swedish site and looking up a groupwise procedure once. I could read all the code, but couldn't understand a word of the comments. :rotlaugh:

I do understand naming the functions locally, although I am curious to know if it would translate it to English function names on opening in an English version. Ie, if you open that French built workbook on an English PC, does it change SOMME(A1:B1;G1) to SUM(A1:B1,G1)?

What mystifies me, though, is the ';'. Why would that be different in foreign language versions? I write and speak some French (although I haven't for many years), and I know that they use commas the same way we do. So why change it? That doesn't make any sense to me. I'm not expecting anyone to be able to answer that question, I'm just throwing it out there.

[Bob Phillips]

I distinctly remember going to XL-Dennis's Swedish site and looking up a groupwise procedure once. I could read all the code, but couldn't understand a word of the comments. :rotlaugh:

That's because Dennis didn't want us to know what it was doing http://vbaexpress.com/forum/images/smilies/001.gif


I do understand naming the functions locally, although I am curious to know if it would translate it to English function names on opening in an English version. Ie, if you open that French built workbook on an English PC, does it change SOMME(A1:B1;G1) to SUM(A1:B1,G1)?

Yes, because Excel doesn't store SUM, or even SOMME, but a token.


What mystifies me, though, is the ';'. Why would that be different in foreign language versions? I write and speak some French (although I haven't for many years), and I know that they use commas the same way we do. So why change it? That doesn't make any sense to me. I'm not expecting anyone to be able to answer that question, I'm just throwing it out there.

No they don't Ken. It's not the language form, but the number form. In French, a thousand and 26 is written as 1.026. 10.23 is written as 10,23. So the comma already has a usage. Imagine a formula like

=SUM(1,2)

in English that is 3, in French that is 1.2.

[Ken Puls]

No they don't Ken. It's not the language form, but the number form. In French, a thousand and 26 is written as 1.026. 10.23 is written as 10,23. So the comma already has a usage. Imagine a formula like

=SUM(1,2)

in English that is 3, in French that is 1.2.

:doh: I stand corrected. I was thinking in text, and had forgotten the numerical aspects. Many European countries use the , for decimal separators, don't they? The thousands being separated by a '.' I didn't know at all.

Thanks for the clarification, Bob! :)

FYI, on the formula aspect, I was hoping that was the case, and appreciate the confirmation.

[Cass]

How to make range variable

.Formula = "=SUMIF(sheet1!$A$5 :$A$6,'Sheet2'!B4,Sheet1!$B$5:$B$6)"

and next i is another range

.Formula = "=SUMIF(sheet1!$A$7 :$A$10,'Sheet2'!B6,Sheet1!$B$7:$B$10)"

something like this?

.Formula ="=SUMIF(sheet1!$A$" & lRow & ":$A$" & gRow & ",'Sheet2'!B6,Sheet1!$B$" & lRow & ":$B$" & gRow & ")"

but this give error 1004
double quotes?! http://vbaexpress.com/forum/images/smilies/102.gif

[Bob Phillips]

How to make range variable

.Formula = "=SUMIF(sheet1!$A$5 :$A$6,'Sheet2'!B4,Sheet1!$B$5:$B$6)"

and next i is another range

.Formula = "=SUMIF(sheet1!$A$7 :$A$10,'Sheet2'!B6,Sheet1!$B$7:$B$10)"

something like this?

.Formula ="=SUMIF(sheet1!$A$" & lRow & ":$A$" & gRow & ",'Sheet2'!B6,Sheet1!$B$" & lRow & ":$B$" & gRow & ")"

but this give error 1004
double quotes?! http://vbaexpress.com/forum/images/smilies/102.gif

That code is fine as long as lRow and gRow have valid values.

[Cass]

sry never mind. i make mistake http://vbaexpress.com/forum/images/smilies/banghead.gif