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.
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
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments