Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 21

Thread: Excel VBA : Appending string with a variable

  1. #11
    Board Regular
    Join Date
    Jun 2019
    Posts
    52
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel VBA : Appending string with a variable

    @Fluff
    It is showing the text in msgbox. The text in textbox3 is showing in the msgbox.
    eg : | "text" |

    @Fluff
    When my string is just "hello" then it is working fine. please check.

    Code:
    Dim Formula As String
    Formula = TextBox3
    MsgBox "|" & Formula & "|"
    Cells(Lastrow, 3).Formula = "Hello" & Formula
    Last edited by sachinns; Aug 1st, 2019 at 10:04 AM.

  2. #12
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,038
    Post Thanks / Like
    Mentioned
    467 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Excel VBA : Appending string with a variable

    That works fine for me.
    Try
    Code:
    Dim MyFormula As String
    MyFormula = TextBox3
    Cells(Lastrow, 3).Formula = "=IFERROR(IF(O9=" & MyFormula & " ,""""),""Missing Leader Price"")"
    It's best to avoid using VBA keywords (such as Formula) for the names of variables or procedures, as VBA can sometimes get confused.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #13
    Board Regular
    Join Date
    Jun 2019
    Posts
    52
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel VBA : Appending string with a variable

    @Fluff

    No. Still it is not working for me.

  4. #14
    Board Regular
    Join Date
    Jun 2019
    Posts
    52
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel VBA : Appending string with a variable

    @Fluff

    Code:
    Dim MyFormula As String
    MyFormula = TextBox3
    'MsgBox "|" & MyFormula & "|"
    Cells(Lastrow, 3).Formula = "(IF(O9=" + MyFormula + " ,"""");""Missing Leader Price"")"
    This part of string is also working fine.

    When i add this part of the string "=IFERROR" Then it is not working.

    I think VBA is not considering IFERROR as a string.
    Last edited by sachinns; Aug 1st, 2019 at 10:27 AM.

  5. #15
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,038
    Post Thanks / Like
    Mentioned
    467 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Excel VBA : Appending string with a variable

    In that case you have probably got a variable, function, sub or some other procedure called IFERROR.
    Check all your code
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  6. #16
    Board Regular
    Join Date
    Jun 2019
    Posts
    52
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel VBA : Appending string with a variable

    @Fluff

    In this UserForm, I never used IFERROR as variable, function, sub or some other procedure anywhere.

    Please help me.
    Last edited by sachinns; Aug 1st, 2019 at 10:34 AM.

  7. #17
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,038
    Post Thanks / Like
    Mentioned
    467 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Excel VBA : Appending string with a variable

    Not just in the userform, anywhere in that workbook.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  8. #18
    Board Regular
    Join Date
    Jun 2019
    Posts
    52
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel VBA : Appending string with a variable

    @Fluff

    The workbook is very very big. The file size 30MB + . =IFERROR is there in so many modules and functions.

    So is there any way i can add this string without changing the other modules and functions ???

  9. #19
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,038
    Post Thanks / Like
    Mentioned
    467 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Excel VBA : Appending string with a variable

    Your code works fine for me, so it is not a problem with that line of code.
    If you have used IFERROR as the name of a variable or procedure you will need to change it.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  10. #20
    Board Regular
    Join Date
    Jun 2019
    Posts
    52
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel VBA : Appending string with a variable

    @Fluff

    I found the issue. It is not because i have used IFERROR as the name of variable . It is because we cannot write a string to a cell which starts with "=" . Because it will be considered as formula. So we have to use '= .

    Thanks a lot for your valuable time.

    Code:
    Cells(Lastrow, 3).Formula = "'=IFERROR(IF(O9=" + MyFormula + " ,"""");""Missing Leader Price"")"
    Last edited by sachinns; Aug 2nd, 2019 at 05:53 AM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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