Change formula to vba.

User avatar
adam
SilverLounger
Posts: 2155
Joined: 23 Feb 2010, 12:07

Change formula to vba.

Post by adam »

Hi anyone,

I want to use the following formula in my excel sheet to extract the words in the square bracket. It works fine.

Code: Select all

=MID(V9,FIND("[",V9)+1,FIND("]",V9)-FIND("[",V9)-1)
However, now I want to embed the formula in the VBA Module. But I cannot make the modification work.

Code: Select all

Private Sub CommandButton1_Click()
Me.txtStatus.Value = "=MID(txt.Name,FIND("[",txt.Name)+1,FIND("]",txt.Name)-FIND("[",txt.Name)-1)"
End Sub
Any help on this would be kindly appreciated.
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 71516
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Change formula to vba.

Post by HansV »

If you want to use quotes " inside a quoted string, you have to double each of them to "" (only inside the string, not the " at the beginning and end).
And the reference to the text box should be outside the quotes:

Code: Select all

Private Sub CommandButton1_Click()
    Me.txtStatus.Value = "=MID(" & txt.Name & ",FIND(""[""," & txt.Name & ")+1,FIND(""]""," & txt.Name & ")-FIND(""[""," & txt.Name & ")-1)"
End Sub
Did you really mean txt.Name or should it be txtName?
Regards,
Hans

User avatar
adam
SilverLounger
Posts: 2155
Joined: 23 Feb 2010, 12:07

Re: Change formula to vba.

Post by adam »

Following is the text where I want to extract the words in the Square bracket.

Code: Select all

{"success":true,"status":"100","message":["The n to 123 is sent."],"time":"Total Time Taken to send this: 0.085 seconds"}
Your modification is not helping to to extract the word The n to 123 is sent.

What might I be doing wrong here?

txt.Name should be txtName.
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 71516
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Change formula to vba.

Post by HansV »

Oh wait, you're not setting a cell formula. Setting the value of a text box to a formula won't work - it makes no sense.

Code: Select all

Private Sub CommandButton1_Click()
    Dim s As String
    Dim p1 As Long
    Dim p2 As Long
    s = Me.txtStatus.Value
    p1 = InStr(s, "[")
    p2 = InStr(s, "]")
    Me.txtName.Value = Mid(s, p1 + 1, p2 - p1 - 1)
End Sub
If you want to return the text between [ ] without the quotes, use

Code: Select all

    Me.txtName.Value = Mid(s, p1 + 2, p2 - p1 - 3)
Regards,
Hans

User avatar
adam
SilverLounger
Posts: 2155
Joined: 23 Feb 2010, 12:07

Re: Change formula to vba.

Post by adam »

Thankyou very much Hans. It worked fine.
Best Regards,
Adam