dcsimg
Results 1 to 9 of 9

Thread: Excel VBA : Help me to join string and variable using VBA

  1. #1

    Thread Starter
    Member
    Join Date
    Jun 2019
    Posts
    37

    Excel VBA : Help me to join string and variable using VBA

    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

  2. #2
    Frenzied Member jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    1,427

    Re: Excel VBA : Help me to join string and variable using VBA

    Code:
    Cells(Lastrow, 3) = "=IFERROR(IF(O9=" & CStr(Formula) & " ;" & Chr$(34) & Chr$(34) & ");" & Chr$(34) & "Missing Leader Price" & Chr$(34) & ") "
    Note that this may or may not be a legal formula - I have not tested it.

  3. #3
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,447

    Re: Excel VBA : Help me to join string and variable using VBA

    I tried to do it but its showing error.
    what error?
    what is the content of formula variable?
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  4. #4

    Thread Starter
    Member
    Join Date
    Jun 2019
    Posts
    37

    Re: Excel VBA : Help me to join string and variable using VBA

    @jdc2000

    Sorry. Error.
    Last edited by sachinns; Aug 2nd, 2019 at 04:12 AM.

  5. #5

    Thread Starter
    Member
    Join Date
    Jun 2019
    Posts
    37

    Re: Excel VBA : Help me to join string and variable using VBA

    @westconn1

    Code:
    Dim MyFormula As String
    MyFormula = TextBox3
    Cells(Lastrow, 3).Formula = "(IF(O9=" + MyFormula + " ,"""");""Missing Leader Price"")"
    '"=IFERROR"
    Hi,
    This code is working fine. But when i add the string which i commented in code i.e, =IFERROR , Then my code is not working. When i checked with 1 expert , he told me that you might have used =IFERROR as variable somewhere in the worksheet .
    So is there any way to add the string =IFERROR to this code without changing anything in the worksheet.

    Please help me on this.

  6. #6
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,998

    Re: Excel VBA : Help me to join string and variable using VBA

    What is the value of `TextBox3`?
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - 2015 IMP Links : Acceptable Use Policy, FAQ
    MyGear:
    OMEN by HP - 15-ce073tx with Win10+Office 2013. || Mac Book Pro (10.6.8) with Office 2011

  7. #7

    Thread Starter
    Member
    Join Date
    Jun 2019
    Posts
    37

    Re: Excel VBA : Help me to join string and variable using VBA

    @Siddharth Rout

    It can be anything what user gives.

  8. #8
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,998

    Re: Excel VBA : Help me to join string and variable using VBA

    Quote Originally Posted by sachinns View Post
    @Siddharth Rout

    It can be anything what user gives.
    When you are writing a formula you cannot leave it to the user. There are lot of things that you need to consider. For example

    1. Have they entered the correct syntax?
    2. Have they entered the correct function name etc etc.

    Also if the user or you use ", then you will have to pad it with extra quotes.

    So let's assme the user types Sum("A10:A11") in the textbox then the code will be

    Code:
        Dim MyFormula As String
        
        '~~> Assuming the textbox has Sum("A10:A11")
        MyFormula = Replace(TextBox3.Text, Chr(34), """")
    
        Cells(Lastrow, 3).Formula = "=IFERROR(O9=" & _
                                    MyFormula & _
                                    ",""Missing Leader Price"")"
    Now try the above code with just 9 in the textbox.

    If you are just accepting Numeric Inputs in the Textbox then you may want to limit the entry to the textbox so that the user cannot type anything other than numbers. When Text will be allowed then there is a chance of user making errors. For example what if the user type "Batman!" in the textbox?

    and if you want to do a String comparision like "O9=Batman", then you will have to pad the text within quotes which may further complicate things...
    Last edited by Siddharth Rout; Aug 2nd, 2019 at 04:45 AM.
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - 2015 IMP Links : Acceptable Use Policy, FAQ
    MyGear:
    OMEN by HP - 15-ce073tx with Win10+Office 2013. || Mac Book Pro (10.6.8) with Office 2011

  9. #9
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,447

    Re: Excel VBA : Help me to join string and variable using VBA

    you might have used =IFERROR
    very easy to check, just use find

    as pointed out by siddharth, if you use iferror remove the IF
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Tags for this Thread

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width