Insert new column with a Formula

Hello

Information:
Excel with data (amounts and text) from column A to AQ.
Columns needed for the forumla are:
Column C [Document Type] with text or empty
Column X [FA Posting Category] with text or empty
Column Y [FA Posting Type] with text or empty
Column AC[FA Entry Type] with text "Fixed Assets" or empty

The formula I need is:
=IF(X2="Disposal";IF(Y2="Invoice";X2;"Liquidation");Y2)

I want VBA to insert that formula in all rows where column AC [FA Entry type]="Fixed Assets" (or alternatively add one more condition i.e. if(AC="Fixed Assets";below formula;"")

I tried using this one but it doesnt work.

Code:

---

Sub Test()
Dim Rng As Range
Columns("AR:AR").Insert
Set Rng = Range("AR2:AR" & Range("B2").End(xlDown).Row)
Rng.FormulaR1C1 = "=IF(X2="Disposal";IF(Y2="Invoice";X2;"Liquidation");Y2)"
End Sub

---

Hello PatNecom,

You have posted this same question at the ExcelForum

This site and the ExcelForum have rules about cross-posting questions.

What Leith said.

Without being judgmental, you might want to brush up on regular Excel concepts before plunging TOO deeply into VBA. In particular, I would study the syntax of an IF statement in Excel and see if semicolons are explained. (Have you used another language that uses semicolons for IF statements?) Another item you might want to get comfortable with is using double quotes in a string. Here's a good explanation specifically for doing that in VBA: (Oops, I can't post URLs here[yet] - google the next line.)
site:vbaexpress.com What is escape character for quotation marks?

Why you are trying to use .FormulaR1C1 instead of just ".Formula" ?

And the Rng assignment is very unusual looking. Are you trying to put the formula in all of [new] column AR except row 1, as well as a certain (or actually uncertain) number of cells in column B? That doesn't seem to relate to "Fixed Assets". It's hard to understand what you're really trying to do.

Quote:

---

Originally Posted by LarryG

Without being judgmental, you might want to brush up on regular Excel concepts before plunging TOO deeply into VBA. In particular, I would study the syntax of an IF statement in Excel and see if semicolons are explained. (Have you used another language that uses semicolons for IF statements?)

---

You may use commas as separators in Excel, as do I, but European versions of Excel uses semi-colons as separators. As the guy is from Spain it is likely that he is just replicating what he sees in Excel.

Thanks for the European explanation. I wouldn't have dreamt that could be what that was. Good eye, and smart to know that.

Did you hover, or recognize Spain's flag :) I'm in Texas so I only know Mexico's :)

Quote:

---

Originally Posted by LarryG

Did you hover, or recognize Spain's flag :) I'm in Texas so I only know Mexico's :)

---

I know about the semi-colons/commas issue, so I guessed the guy might be European, and looking at the flag I did recognise that it was Spain (knowing there are only 30 or so makes that easier :)).

I was in Texas in July, for SQLSaturday in Austin on the 13th.

And your flag ... and my state flag - how about that.
July is a bit warm here, so sorry about that for you, but the winters are great :)

When writing a formula in a spreadsheet, a semicolon is being used as a separator and function names in the local language in Spain, Poland and several other countries (not always and not all functions are translated into the user's language).
If you want to assign formula content to the Formula property in the VBA code, use the US function names and separators, which is a comma. In addition, if a formula has a textual value, it must be enclosed in double quotation marks.
The correct entry will look like this:

Code:

---

Rng.Formula = "=IF(X2=""Disposal"",IF(Y2=""Invoice"",X2,""Liquidation""),Y2)"

---

You can write a formula in VBA in the local language. We assign it to the FormulaLocal property. But remember one thing. The code will not work properly when it is run on a different language version. The only certain saving of formulas in VBA to be correctly converted regardless of the user's language is the assignment to the Formula property, according to the rules described earlier.


There are still exceptions that will not be properly converted despite using the Formula property. These are arguments of functions that are saved as a text value, e.g. in Polish =KOMÓRKA("kolumna") in US =CELL("col"), in Polish =INFO("wersja") in US =INFO("RELEASE") . Unfortunately, these types of arguments are not automatically converted to the user's language. The code must be corrected manually or the condition checking the language version of the application must be applied. A very well-prepared translator at: https://excel-translator.de can help in the correct conversion of problematic arguments.

Artik

Hi guys

Thanks for helping and sorry for all the confussion about the commas/semicolons. Yes, I am European (actually from the same country as Artik but living in Spain).

I am really amator in VBA and the code I used was a copy of a code found somewhere on another website.

I thought that my explanation was easy to follow but I guess I was wrong.
Basically I have a formula (the one you we discussed) that works perfectly in Excel.

So if you are able to give me a right code (better than before) that would be great. With my current code AR column is filled with text "=IF(X2=""Disposal"",IF(Y2=""Invoice"",X2,""Liquidation""),Y2)"
rather than giving the result of the formula :(

And to answer Larry about the Range assignment, I wanted to value to be placed from AR2 until the last row with data (and for this I used column B as reference as it is one of the columns that will always have data (no empty cells)).

Quote:

---

Originally Posted by PatNecom

With my current code AR column is filled with text "=IF(X2=""Disposal"",IF(Y2=""Invoice"",X2,""Liquidation""),Y2)"
rather than giving the result of the formula :(

---

The AR column cannot be formatted as text. If it's formatted in this way, you'll get the formula content, not the result.

Quote:

---

Originally Posted by PatNecom

I wanted to value to be placed from AR2 until the last row with data (and for this I used column B as reference as it is one of the columns that will always have data (no empty cells)).

---

The code is supposed to be OK. But until at least cell B3 is full. If the cells below B2 are empty, then Range("B2").End(xlDown).Row will indicate the last row in the worksheet (B1048574). Therefore, it is better to use the search from below. After the patch, the code might look like this

Code:

---

Sub Test1()
    Dim Rng        As Range
   
    Columns("AR:AR").Insert
   
    Set Rng = Range("AR2:AR" & Cells(Rows.Count, "B").End(xlUp).Row)
   
    If Rng.Rows.Count > 1 Then
        Rng.Formula = "=IF(X2=""Disposal"",IF(Y2=""Invoice"",X2,""Liquidation""),Y2)"
    End If
End Sub

---

Artik

Quote:

---

Originally Posted by Artik

There are still exceptions that will not be properly converted despite using the Formula property. These are arguments of functions that are saved as a text value, e.g. in Polish =KOMÓRKA("kolumna") in US =CELL("col"), in Polish =INFO("wersja") in US =INFO("RELEASE") . Unfortunately, these types of arguments are not automatically converted to the user's language. The code must be corrected manually or the condition checking the language version of the application must be applied. A very well-prepared translator at: https://excel-translator.de can help in the correct conversion of problematic arguments.

---

Yes, but it is possible to have an English language version of Excel (even in Spain), but using local language separators (semi-colons). The fact that the OP used a formula in English, =IF(X2="Disposal";IF(Y2="Invoice";X2;"Liquidation");Y2), made me think that is what they had.

Quote:

---

Originally Posted by LarryG

And your flag ... and my state flag - how about that.

---

That is the Chilean flag, similar but not quite the same. It was relevant when I joined VBAX but I am back in the UK now.

Quote:

---

Originally Posted by LarryG

July is a bit warm here, so sorry about that for you, but the winters are great :)

---

It was hot, but I had a great time. Lynne came along and we had a 9 days in Austin. We lived there for a bit in the 80s, so we were listening to lots of music, doing some two-step, and revisiting old haunts, as well as doing the conference.