chokka
asked on
Split Function in Excel VBA Macro
Excel VBA Macro code.
I am passing two sets of value to the variable strRecord for the below function.
Value1 :- "1,1,,EACH,ADVAIR ,NDC#:0000-0695-00,STR:250 -50MCG,FM: ARIN,SZ:60 EA,3163219,,6/30/2013,0,$1 00.20 ,$100.20 "
Using split function based on Comma, I am able to filter values and assign to its variable - DetaiFields
Value 2: "4,4,,EACH,ATRIPLA,NDC#:15 584-0101-0 1,STR:200- 300-600MG, FM:TABS,SZ :30 EA,3743309,,6/30/2013,0,$7 85.83 ,$3,143.32 "
In the Value 2 for the last set is $3,143.22 ( Currency DataType ). I want the whole value 3143.32 . But the Split function filters by comma and retrieve only the value 3.
How to handle the Currency datatype value ?
I am passing two sets of value to the variable strRecord for the below function.
Value1 :- "1,1,,EACH,ADVAIR ,NDC#:0000-0695-00,STR:250
Using split function based on Comma, I am able to filter values and assign to its variable - DetaiFields
Value 2: "4,4,,EACH,ATRIPLA,NDC#:15
In the Value 2 for the last set is $3,143.22 ( Currency DataType ). I want the whole value 3143.32 . But the Split function filters by comma and retrieve only the value 3.
How to handle the Currency datatype value ?
Private Function GetInvoiceDetail(strRecord As String) As InvoiceDetail
Dim DetailFields() As String
DetailFields = Split(Replace(strRecord, Chr(34), ""), ",")
GetInvoiceDetail.Ship_Qty = CLng(DetailFields(0))
GetInvoiceDetail.Order_Qty = CLng(DetailFields(1))
GetInvoiceDetail.Code = DetailFields(2)
GetInvoiceDetail.UM = DetailFields(3)
GetInvoiceDetail.Blank1 = DetailFields(4)
GetInvoiceDetail.Description = DetailFields(5)
GetInvoiceDetail.Blank2 = DetailFields(6)
GetInvoiceDetail.Blank3 = DetailFields(7)
GetInvoiceDetail.Blank4 = DetailFields(8)
GetInvoiceDetail.CIN = CLng(DetailFields(9))
GetInvoiceDetail.PO_Number = DetailFields(10)
GetInvoiceDetail.Due_Date = CDate(DetailFields(11))
GetInvoiceDetail.DEA_Class = CLng(DetailFields(12))
GetInvoiceDetail.Unit_Price = CDbl(DetailFields(13))
GetInvoiceDetail.Extension = CDbl(DetailFields(14))
End Function
ASKER
Thanks fanpage,
we need If Else Condition
Price can be $0 and it can be 3 to 5 digits.
In my posting , i have passed Value 1 and Value 2. In the Value 1, i have mentioned with 3 digits and in the Value 2 i have mentioned with 4 digits.
we need If Else Condition
Price can be $0 and it can be 3 to 5 digits.
In my posting , i have passed Value 1 and Value 2. In the Value 1, i have mentioned with 3 digits and in the Value 2 i have mentioned with 4 digits.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
My code works for any length data and it handles both unit price and extension.
ASKER
@MartinLiss - there is a problem. You can check by executing this attachment.
What happens is that Extension Field Value is replaced by Unit Price Value.
ImportCSV.xls
Test-6619289--5-30-2013.csv
What happens is that Extension Field Value is replaced by Unit Price Value.
ImportCSV.xls
Test-6619289--5-30-2013.csv
ASKER
@MartinLiss it works.
Thanks
Thanks
DetailFields(13) = Replace(DetailFields(13), "|", ",")
[b] DetailFields(14) = Replace(DetailFields(14), "|", ",")[/b]
Oops. Change line 19 to
DetailFields(14) = Replace(DetailFields(14), "|", ",")
DetailFields(14) = Replace(DetailFields(14), "|", ",")
I see you fixed the typo without my help:)
ASKER
thank you MartinLiss
You're welcome and I'm glad I was able to help.
Marty - MVP 2009 to 2013
Marty - MVP 2009 to 2013
Is the Unit_Price ever going to be greater than $999.99?
If not, you could use these changes to the last two lines;
Open in new window
BFN,
fp.