Creating VBA Macro in Excel to Insert Formulas: Sum and IF Function with Selected Cell

Hello, I am a beginner when it comes to creating macros in VBA under excel, I need your help on the topic of a macro inserting a formula into a selected cell, any formula, even the sum of the two cells above. After inserting the code below, the macro crashes, it only works if I do not put the = sign before if, i.e. it inserts the text only.



Thanks in advance for your help

VBA does not support Polish formula names, only their English equivalents.
The easiest way to do this is to turn on the macro recorder, enter the formula in Excel, and finish recording. Then you go Alt + F11 into the code, enter the module and find the last macro recorded. From it you copy the mentioned formula, possibly correcting its arguments.

By default, the recorder registers the formula in the R1C1 format - in the R code is the current row, C - the current column. If you enter the formula into cell B3, the formula: "= RC" means "= B3"

The equivalent of dollars (absolute reference) in the formula is the number next to R or C, which is "= R2C5" (which translates directly as: second row, fifth column) corresponds to "= $ E $ 2".

The equivalent of a relative reference (without dollars - moved when dragging) - is specifying a positive or negative number for R and / or C, meaning by how much the reference should be moved relative to the current one, e.g. for the mentioned B3 formula: "= R [1] C" means " = B4 ", the formula" = R [-2] C [-1] "means" = A1 "," = R [3] C [2] "means" = D6 "etc.

You insert a formula by means of a cell reference (or precede it with the name of a sheet from the Worksheets collection):

You can also use letter designations of cells, but then the assignment method changes:


You must also remember that if you are inside a quotation mark, if you want to put a single quotation mark: ", you must precede it with the second one, ie write" ".
Additionally, all semicolons separating arguments in functions in Excel are converted into commas in VBA.
So finally your example function written in letter format should look like this:

and in the R1C1 format - here it depends on which cell you are inserting it into, let's assume that in A1, like this:


greetings

Thanks a lot Marcin, the macro works :)

While working on the file, I encountered a new problem. The NEW file contains a new version of one of the sheets in the OLD file, I copy this sheet (with the changed name) to the OLD file, the macro adjusts, among others, the formulas in other sheets of the OLD file to the current version, but ... the problem is the fact that the new file introduces several new macros that are not in the OLD file and even with the recorder I am unable to create macros that would copy macros from one file to another. Hope it is possible?

If it is a one-time operation, you can manually copy this macro from the old file to the new one. If it's supposed to be automatic, check it out this link where shows how to copy the module between workbooks (just don't do it on C: \, but somewhere to the pace)

Hi,
I have registered a formula that references another sheet in the same workbook.

Range ("K2"). Select
ActiveCell.FormulaR1C1 = "= IFERROR (IF (INDEX ('Sheet5'! C [-10]: C [-3], MATCH (RC [-10], 'Sheet5'! C [-10], 0), 5 ) = RC [-6], RC [-6], "" ""), "" "") "
Range ("K2"). Select

My macro first asks for the name of the sheet with which to compare the values of the current sheet. Hence I have this sheet name (specified by the variable sheet ).
However, I would like to Sheet5 replace the variable in the given formula sheet .

Please help

This is how it should work:

Remember about the spaces around the & sign.

Of course it works
Maciej Gonet thank you / you.

I have formulas typed in cells of one sheet as:

'= (I563 + I564) * 2
'= (B350 + I564) * 3, etc

My macro rewrites the contents of multiple cells to another sheet (in a loop):

ActiveSheet.Cells (x + 565, y) .Formula = Sheets ("first_sheet"). Cells (x, y)

Everything fits correctly on the second sheet, but the formulas don't work until you double-click each cell. Only then does it work.
What to add in the vba code to make it happen automatically?

ActiveSheet.Calculate

Unfortunately it does not work.

Hmm ... In my works. Automatic recalculation is not turned off? Attach a pricelist file

An attachment would be nice, but please try this:

Thanks.
Started working, but stops when the formula says:

'= IF (I569 = "Carwash", 2, 0)

Added after 1 [hours] 29 [minutes]:

Approx. I coped.
What blind eyes (at the top there is information that vba does not recognize Polish commands; besides, the semicolons must be converted to commas).
Ultimately, the entry looks like this:

'= If (I569 = "Car wash", 2.0)

These are the consequences of not having an attachment. VBA recognizes Polish formulas, you only need to use FormulaLocal property instead of Formula.