VBA How to Use Split to Split a String and Copy

[beartooth91]

Hello Again -

I have a column of instrument ranges in Col AR (Range is AR11 to rows.count) of one sheet and want to split each of those strings into two parts - a min and a max - and then copy the min to Column R.....and copy the max to Column S, both in a different worksheet, starting in row 3.
The instrument ranges are different, with most being a '4-20ma' entry in .Cells(x, "AR"). I want to end up with 4ma in .Cells(y, "R") and 20ma in .Cells(y, "S").

Need to use a loop to copy and need to use the Split function, splitting at the hyphen (I think).

I'm pretty good with For If Then Else Next loops, but after the above..... I have no idea and could use your help.

Thanks in advance.

 

[beartooth91]

This takes the data from Column A starting at A2 and put the output in Columns B and C in the same sheet.
If you supply pertinent data like sheet names, we'll change that for you if you have trouble changing it to fit your needs yourself.

Sub Or_Maybe_So()
Dim allArr, i As Long, j As Long
allArr = Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row).Value    '<----Change required
ReDim Preserve allArr(1 To UBound(allArr), 1 To 3)
    For i = LBound(allArr) To UBound(allArr)
        allArr(i, 2) = Split(allArr(i, 1), "-")(0)
        allArr(i, 3) = Split(allArr(i, 1), "-")(1)
    Next i
    For j = 2 To UBound(allArr, 2)
        Cells(2, j).Resize(UBound(allArr)) = Application.Index(allArr, , j)    '<----Change/sheet name required
    Next j
End Sub

Following up on this: I've been messing around with this and can't get it to work. Changed one line of code (See below) and am getting the "subscript of range" on the allArr(i, 2) = Split(allArr(i, 1), "-")(0) line.

Sub Or_Maybe_So()
With Workbooks("AISCAN.xlsx").Sheets(1)
Dim allArr, i As Long, j As Long
allArr = Range("C3:C" & Cells(Rows.Count, 1).End(xlUp).Row).Value    '<----Change required
ReDim Preserve allArr(1 To UBound(allArr), 1 To 3)
    For i = LBound(allArr) To UBound(allArr)
        allArr(i, 2) = Split(allArr(i, 1), "-")(0)
        allArr(i, 3) = Split(allArr(i, 1), "-")(1)
    Next i
    For j = 2 To UBound(allArr, 2)
        Cells(2, j).Resize(UBound(allArr)) = Application.Index(allArr, , j)    '<----Change/sheet name required
    Next j
End With
End Sub

 

[jolivanes]

Did you read the last sentence in my last post? The BTW line.
Like I mentioned, I copy and paste the request but I don't want all the extra data that is totally unnecessary.

 

[jolivanes]

If you use the code from Post #30 after changing the workbook names and sheet names, does it work.
You said that you only changed one line. The code in Post #30 has 19 lines and your "one line changed" code has 14 lines. Hardly a one line change.
Why did you add the "With Workbooks ….. line?
In which workbook/sheet is the data for the array?
If it is in the AISCAN workbook, your "With …. End With" is missing the required periods.

 

[jolivanes]

What is the result of

MsgBox allArr(1, 1): Exit Sub

when you insert that line after the

ReDim Preserve allArr(1 To UBound(allArr), 1 To 3)

Comment out (put an apostrophe in front of it) after using it or just delete that MsgBox line.

You had the right idea using the "With...End With" statement but there is a little more to it.

With...End With Statement - Visual Basic

Learn more about: With...End With Statement (Visual Basic)

learn.microsoft.com

 

[beartooth91]

If you use the code from Post #30 after changing the workbook names and sheet names, does it work.
You said that you only changed one line. The code in Post #30 has 19 lines and your "one line changed" code has 14 lines. Hardly a one line change.
Why did you add the "With Workbooks ….. line?
In which workbook/sheet is the data for the array?
If it is in the AISCAN workbook, your "With …. End With" is missing the required periods.

The code that I was trying was your original post ....14, I think.....?

 

[beartooth91]

What is the result of

MsgBox allArr(1, 1): Exit Sub

when you insert that line after the

ReDim Preserve allArr(1 To UBound(allArr), 1 To 3)

Comment out (put an apostrophe in front of it) after using it or just delete that MsgBox line.

You had the right idea using the "With...End With" statement but there is a little more to it.

With...End With Statement - Visual Basic

Learn more about: With...End With Statement (Visual Basic)

learn.microsoft.com

I use the With - End With alot, but I've had a misunderstanding of it. I'd believed it works when there is more than one workbook/worksheet present and that it would execute on the non-active workbook...... and I've found out that's not true.