Help with splitting Array using VBA?

[Coyotex3]

Hello guys, trying to figure out how to split my data.

Here is the code I have which loads my array.

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.

 

[lrobbo314]

This will do it without loading it into an array.

Book2
	A	B	C	D	E	F
1	Site ID	Cost	Add On	Adj	Tax	Shipping
2	XX001	100	0	0	5	0
3	XX002	100	0	0	5	0
4	XX003	100	0	0	5	0
5	XX004	100	0	0	5	0
6	XX005	100	0	0	5	0
7	XX006	100	0	0	5	0
8	XX007	100	0	0	5	0
9	XX008	100	0	0	5	0
10	XX009	100	0	0	5	0
11	XX010	100	0	0	5	0
12	XX011	100	0	0	5	0
13	XX012	100	0	0	5	0
Sheet1

Sub XSPLIT()
Dim r As Range:         Set r = Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)

r.Value = Evaluate(Replace("=RIGHT(@,LEN(@)-FIND(""-"",@))", "@", r.Address))

End Sub

 

[Coyotex3]

This will do it without loading it into an array.

Book2
	A	B	C	D	E	F
1	Site ID	Cost	Add On	Adj	Tax	Shipping
2	XX001	100	0	0	5	0
3	XX002	100	0	0	5	0
4	XX003	100	0	0	5	0
5	XX004	100	0	0	5	0
6	XX005	100	0	0	5	0
7	XX006	100	0	0	5	0
8	XX007	100	0	0	5	0
9	XX008	100	0	0	5	0
10	XX009	100	0	0	5	0
11	XX010	100	0	0	5	0
12	XX011	100	0	0	5	0
13	XX012	100	0	0	5	0
Sheet1

Sub XSPLIT()
Dim r As Range:         Set r = Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)

r.Value = Evaluate(Replace("=RIGHT(@,LEN(@)-FIND(""-"",@))", "@", r.Address))

End Sub

Thank you for this option. Ideally I'd like to be able to do it within the array. I intend on trying to do some additional calculations hence why I'm loading the data into the array and then spitting it back out.

 

[lrobbo314]

Or loading it into an array and pasting it to another sheet. More like what you were asking for.

Sub WITHARRAY()
Dim wb As Workbook:         Set wb = ActiveWorkbook
Dim ws1 As Worksheet:       Set ws1 = wb.Sheets("Sheet1")
Dim ws2 As Worksheet:       Set ws2 = wb.Sheets("Sheet2")
Dim AR() As Variant

With ws1
    AR = .Range("A1:F" & .Range("A" & Rows.Count).End(xlUp).Row).Value2
End With

For i = 2 To UBound(AR)
    AR(i, 1) = Split(AR(i, 1), "-")(1)
Next i

ws2.Range("A1").Resize(UBound(AR), UBound(AR, 2)).Value = AR

End Sub

 

[Coyotex3]

Or loading it into an array and pasting it to another sheet. More like what you were asking for.

Sub WITHARRAY()
Dim wb As Workbook:         Set wb = ActiveWorkbook
Dim ws1 As Worksheet:       Set ws1 = wb.Sheets("Sheet1")
Dim ws2 As Worksheet:       Set ws2 = wb.Sheets("Sheet2")
Dim AR() As Variant

With ws1
    AR = .Range("A1:F" & .Range("A" & Rows.Count).End(xlUp).Row).Value2
End With

For i = 2 To UBound(AR)
    AR(i, 1) = Split(AR(i, 1), "-")(1)
Next i

ws2.Range("A1").Resize(UBound(AR), UBound(AR, 2)).Value = AR

End Sub

This one works. Thank you so much for this!

 

[Coyotex3]

Mods, please remove if this requires a new thread.

Just had a follow on question to this, Is there a way of adding an "If" statement to this?

I get a runtime error if one of the cells on Range("A") does not contain "-" Trying to bypass this someway somehow.

Book4
	A	B	C	D	E	F
1	Site ID	Cost	Add On	Adj	Tax	Shipping
2	12345-XX001	100	0	0	5	0
3	12345-XX002	100	0	0	5	0
4	12345-XX003	100	0	0	5	0
5	12345-XX004	100	0	0	5	0
6	12345-XX005	100	0	0	5	0
7	12345-XX006	100	0	0	5	0
8	12345-XX007	100	0	0	5	0
9	12345-XX008	100	0	0	5	0
10	12345-XX009	100	0	0	5	0
11	12345-XX010	100	0	0	5	0
12	12345-XX011	100	0	0	5	0
13	12345	100	0	0	5	0
Sheet1

I tried playing around with something like this with no success of course

If InStr(1, (Range("A2" & i).Value), "-") > 0 Then

 

[lrobbo314]

This seems to do the trick.

Sub WITHARRAY()
Dim wb As Workbook:         Set wb = ActiveWorkbook
Dim ws1 As Worksheet:       Set ws1 = wb.Sheets("Sheet1")
Dim ws2 As Worksheet:       Set ws2 = wb.Sheets("Sheet2")
Dim AR() As Variant
Dim tmp As String

With ws1
    AR = .Range("A1:F" & .Range("A" & Rows.Count).End(xlUp).Row).Value2
End With

For i = 2 To UBound(AR)
    tmp = AR(i, 1)
    If InStr(tmp, "-") Then AR(i, 1) = Split(tmp, "-")(1)
Next i

ws2.Range("A1").Resize(UBound(AR), UBound(AR, 2)).Value = AR

End Sub

 

[Coyotex3]

This seems to do the trick.

Sub WITHARRAY()
Dim wb As Workbook:         Set wb = ActiveWorkbook
Dim ws1 As Worksheet:       Set ws1 = wb.Sheets("Sheet1")
Dim ws2 As Worksheet:       Set ws2 = wb.Sheets("Sheet2")
Dim AR() As Variant
Dim tmp As String

With ws1
    AR = .Range("A1:F" & .Range("A" & Rows.Count).End(xlUp).Row).Value2
End With

For i = 2 To UBound(AR)
    tmp = AR(i, 1)
    If InStr(tmp, "-") Then AR(i, 1) = Split(tmp, "-")(1)
Next i

ws2.Range("A1").Resize(UBound(AR), UBound(AR, 2)).Value = AR

End Sub

Sure does. Truly appreciate it.