Excel function to VBA

Question

I have a column (E) of items that are filled with codes that resemble the following format: 5301-500-300-000 with an adjacent column (F) of 'amounts paid' that look like the following: 53.20

My goal is to multiply the appropriate amounts in column F with the right tax rebates by using a nested if formula in vba. I've managed to do this using excel functions as follows:

a left(E2,4) formula & a mid(E2,10,2) formula followed by a
=IF(OR(F282=1151,F282=1153),IF(OR(G282=131,G282=200,G282=210,G282=300,G282=310,G282=320,G282=800,G282=821,G282=831,G282=841,,G282=700,G282=721),H282*0.5,IF(OR(G282=341,G282=351,G282=400,G282=410,G282=421,G282=431,G282=441,G282=500,G282=511,G282=521,G282=531,G282=600,G282=611,G282=900,G282=700,G282=721),H282*0.3031,0))) formula

My question is how could I convert this series of excel formulas into a vba format so that I wouldn't have to constantly use the LEFT & MID excel functions.

So far, I've tried creating variables for left' &mid `

Private Sub CommandButton2_Click()

Dim taxcode As Range, location As Range

Set taxcode = Left(Range("E2:E10000"), 4)
Set location = Mid(Range("E2:E10000"), 10, 2)


End Sub

But have already seen problems with my code. Any help would be most appreciated.

taxcode and locations should not be ranged, they should be strings. You cannot do the 'left' operation on a range, only on a string of characters. Try it again using that. — David G, Jun 23 '15 at 16:56
maybe its me but I cant follow what you are trying to do, you use Mid and left to extract 5301 and 00 then you use an if statement to check if certain cells all have a set value and then mutiply by h282 an amount. why use left and mid as I dont see you use those values. Maybe supply a better example of how you are using mid and left in conjunction with your if statement. — Sorceri, Jun 23 '15 at 17:09

score 0 · Accepted Answer · edited May 23 '17 at 11:51

I would use a regular expression for this sort of thing; that way you can avoid having those awfull nested LEFT() and MID() stuff.

So, let's get to it.

First, in the VBA editor, clic on the Tools menu and select References; enable Microsoft VBScript Regular Expressions 5.5.

Then, let's use a RegEx to split each entry from your string:

Function splitCode(code As String) As String()
    Dim ans(1 To 4) As String
    Dim re As RegExp

    Set re = New RegExp

    With re
        .IgnoreCase = True
        .MultiLine = False
        .Pattern = "([0-9]*)-([0-9]*)-([0-9]*)-([0-9]*)"
        ' Here's the magic:
        '       [0-9]* will match any sequence of digits
        '       The parenthesis will help you retreive each piece of the pattern
    End With

    If re.Test(code) Then
        ans(1) = re.Replace(code, "$1") ' You can use the Replace method to get
        ans(2) = re.Replace(code, "$2") ' each piece of the pattern.
        ans(3) = re.Replace(code, "$3") ' Simply use $n (where n is an integer)
        ans(4) = re.Replace(code, "$4") ' to get the n-th piece of the pattern enclosed in parenthesis
    End If
    Set re = Nothing
    splitCode = ans
End Function

Now that you have this array with each piece of your code, you can use it in other sub or function to get what you need:

sub doMyStuff()
    dim taxCodeRange as Range, taxCode as String()
    dim i as integer
    taxCodeRange = Range("E2:E1000")
    for i = 1 to taxCodeRange.Rows.count
        taxCode = splitCode(taxCodeRange.Cells(i,1))
        ' Now you can make whatever comparissons you need with each entry
        ' of the taxCode array.
        ' WARNING: Each entry in the array is a String, so you may want
        ' to convert it to integer before doing any comparissons
        if CInt(taxCode(1)) = 5301 then
        ' Do some stuff
        elseIf cInt(taxCode(1)) = 5302 then
        ' Do some other stuff
        ' ...
        ' ...
        ' end if
    next i
end sub

Hope this helps you.

Take a look to this post for more information about Regular Expressions in Excel

@Saif Happy to help. I noticed you accepted the answer, and that's great... It's great when I can help someoene by sharing what I know (by the way, I learned this solution here at SO... now it's your turn to pay it forward ;) — Barranka, Jun 26 '15 at 18:37

Excel function to VBA

1 Answers1