Hello guys, trying to figure out how to split my data.
Here is the code I have which loads my array.
I however would like to learn how to split the first column prior to dumping the array.
This is the original data:
And this is the desired result :
I essentially want to keep only the TextAfter "-" using VBA.
Here is the code I have which loads my array.
VBA Code:
Sub ArraysTemp()
Dim a As Variant
Dim wb1 As Workbook
Dim ws1 As Worksheet, ws2 As Worksheet
Dim LR As Long
Set wb1 = Workbooks("TempD.xlsx")
wb1.Activate
ActiveSheet.Name = "Source Data"
Sheets.Add.Name = "Array Data"
Set ws1 = wb1.Worksheets("Source Data")
Set ws2 = wb1.Worksheets("Array Data")
LR = ws1.Range("B" & Rows.count).End(xlUp).Row
a = ws1.Range("A1:F" & LR).Value
ws2.Range("A1").Resize(UBound(a), UBound(a, 2)).Value = a
End Sub
I however would like to learn how to split the first column prior to dumping the array.
This is the original data:
TempD.xlsx | ||||||||
---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | |||
1 | Site ID | Cost | Add On | Adj | Tax | Shipping | ||
2 | 12345-XX001 | $100.00 | $0.00 | $0.00 | $5.00 | $0.00 | ||
3 | 12345-XX002 | $100.00 | $0.00 | $0.00 | $5.00 | $0.00 | ||
4 | 12345-XX003 | $100.00 | $0.00 | $0.00 | $5.00 | $0.00 | ||
5 | 12345-XX004 | $100.00 | $0.00 | $0.00 | $5.00 | $0.00 | ||
6 | 12345-XX005 | $100.00 | $0.00 | $0.00 | $5.00 | $0.00 | ||
7 | 12345-XX006 | $100.00 | $0.00 | $0.00 | $5.00 | $0.00 | ||
8 | 12345-XX007 | $100.00 | $0.00 | $0.00 | $5.00 | $0.00 | ||
9 | 12345-XX008 | $100.00 | $0.00 | $0.00 | $5.00 | $0.00 | ||
10 | 12345-XX009 | $100.00 | $0.00 | $0.00 | $5.00 | $0.00 | ||
11 | 12345-XX010 | $100.00 | $0.00 | $0.00 | $5.00 | $0.00 | ||
12 | 12345-XX011 | $100.00 | $0.00 | $0.00 | $5.00 | $0.00 | ||
13 | 12345-XX012 | $100.00 | $0.00 | $0.00 | $5.00 | $0.00 | ||
Source Data |
And this is the desired result :
TempD.xlsx | ||||||||
---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | |||
1 | ID | Cost | Add On | Adj | Tax | Shipping | ||
2 | XX001 | $100.00 | $0.00 | $0.00 | $5.00 | $0.00 | ||
3 | XX002 | $100.00 | $0.00 | $0.00 | $5.00 | $0.00 | ||
4 | XX003 | $100.00 | $0.00 | $0.00 | $5.00 | $0.00 | ||
5 | XX004 | $100.00 | $0.00 | $0.00 | $5.00 | $0.00 | ||
6 | XX005 | $100.00 | $0.00 | $0.00 | $5.00 | $0.00 | ||
7 | XX006 | $100.00 | $0.00 | $0.00 | $5.00 | $0.00 | ||
8 | XX007 | $100.00 | $0.00 | $0.00 | $5.00 | $0.00 | ||
9 | XX008 | $100.00 | $0.00 | $0.00 | $5.00 | $0.00 | ||
10 | XX009 | $100.00 | $0.00 | $0.00 | $5.00 | $0.00 | ||
11 | XX010 | $100.00 | $0.00 | $0.00 | $5.00 | $0.00 | ||
12 | XX011 | $100.00 | $0.00 | $0.00 | $5.00 | $0.00 | ||
13 | XX012 | $100.00 | $0.00 | $0.00 | $5.00 | $0.00 | ||
Sheet3 |
I essentially want to keep only the TextAfter "-" using VBA.