Consulting

Results 1 to 12 of 12

Thread: Insert new column with a Formula

  1. #1
    VBAX Newbie
    Joined
    Aug 2019
    Posts
    2
    Location

    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.
    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
    Last edited by Bob Phillips; 08-16-2019 at 10:54 AM. Reason: Added VBA tags

  2. #2
    VBAX Expert Leith Ross's Avatar
    Joined
    Oct 2012
    Location
    San Francisco, California
    Posts
    552
    Location
    Hello PatNecom,

    You have posted this same question at the ExcelForum

    This site and the ExcelForum have rules about cross-posting questions.
    Sincerely,
    Leith Ross

    "1N73LL1G3NC3 15 7H3 4B1L17Y 70 4D4P7 70 CH4NG3 - 573PH3N H4WK1NG"

  3. #3
    VBAX Regular
    Joined
    Aug 2019
    Posts
    9
    Location
    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.

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Quote Originally Posted by LarryG View Post
    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.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  5. #5
    VBAX Regular
    Joined
    Aug 2019
    Posts
    9
    Location
    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

  6. #6
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Quote Originally Posted by LarryG View Post
    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.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  7. #7
    VBAX Regular
    Joined
    Aug 2019
    Posts
    9
    Location
    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

  8. #8
    VBAX Mentor
    Joined
    Dec 2008
    Posts
    404
    Location
    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:
    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
    Last edited by Artik; 08-17-2019 at 06:27 PM.

  9. #9
    VBAX Newbie
    Joined
    Aug 2019
    Posts
    2
    Location
    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)).

  10. #10
    VBAX Mentor
    Joined
    Dec 2008
    Posts
    404
    Location
    Quote Originally Posted by PatNecom View Post
    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 View Post
    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
    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

  11. #11
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Quote Originally Posted by Artik View Post
    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.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  12. #12
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Quote Originally Posted by LarryG View Post
    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 View Post
    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.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •