Page 1 of 3 123 LastLast
Results 1 to 10 of 21

Thread: Excel VBA : Appending string with a variable

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

    Default Excel VBA : Appending string with a variable

    Hi Friends,

    I have a string and a variable called "Formula". I want to append this string with my variable and update this value in the Cells(Lastrow, 3) of my active sheet.

    String is :

    =IFERROR(IF(O9=” + Formula + " ;””);”Missing Leader Price”) "

    Note : Orange part is my string

    I tried to do it but its showing error. Can somebody help me to provide the vba code for this.

    Thanks
    Last edited by sachinns; Aug 1st, 2019 at 08:11 AM.

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

    Default Re: Excel VBA : Appending string with a variable

    Try
    Code:
    cell(lastrow,3).formula="=IFERROR(IF(O9=" & Formula & " ,""""),""Missing Leader Price"")"
    - 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. #3
    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

    Compiler error : sub or function not defined.

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

    Default Re: Excel VBA : Appending string with a variable

    Can you please post the rest of 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

  5. #5
    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:
    Private Sub Validate_Click()
    If ComboBox3.Text = "" Then
    Cancel = 1
    MsgBox "Please Enter the Formula Name"
    ComboBox3.SetFocus
    Exit Sub
    End If
    If TextBox2.Text = "" Then
    Cancel = 1
    MsgBox "Please Enter the Formula Description"
    TextBox2.SetFocus
    Exit Sub
    End If
    
    
    Set ws = ThisWorkbook.Worksheets("Formula")
    ws.Activate
    
    Dim Lastrow
    Lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    Lastrow = Lastrow + 1
    Cells(Lastrow, 1) = ComboBox3
    Cells(Lastrow, 2) = TextBox2
    'Cells(Lastrow, 3) = TextBox3
    Dim Formula As String
    Formula = TextBox3
    'Cells(Lastrow, 3) = "=IFERROR(IF(O9=" + Formula '+ ";" + Chr(34)  + Chr(34))+ ";" + Chr(34)+ "Missing Leader Price" + Chr(34) + ")"
    'Cells(Lastrow, 3).Formula = "H" '"=IFERROR(IF(O9=" & Formula & " ,""""),""Missing Leader Price"")"
    End Sub
    Note : I commented some part code .
    Last edited by sachinns; Aug 1st, 2019 at 08:42 AM.

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

    Default Re: Excel VBA : Appending string with a variable

    What line gives the error?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

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

    The code which you gave.
    Cells(Lastrow, 3).Formula = "=IFERROR(IF(O9=" & Formula & " ,""""),""Missing Leader Price"")"

    @Fluff



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

    Default Re: Excel VBA : Appending string with a variable

    In that case I suspect that TextBox3 does not have a value.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  9. #9
    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, It is have the value. I can see the Text in the textbox.


    If the string is "Hello" then i am able to append and update it into my sheet. But the string which i mentioned above is not working.
    Please help me.
    Last edited by sachinns; Aug 1st, 2019 at 09:39 AM.

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

    Default Re: Excel VBA : Appending string with a variable

    If you add the message box as shown
    Code:
    Dim Formula As String
    Formula = TextBox3
    MsgBox "|" & Formula & "|"
    Cells(Lastrow, 3).Formula = "=IFERROR(IF(O9=" & Formula & " ,""""),""Missing Leader Price"")"
    What does it say?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

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
  •