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.

 

[Joe4]

I.AM.THE.LORD.OF.THE.IDIOTS

Thanks, still getting the same compile error. As the next reply suggests; maybe this can't be copied.....?

See my reply above...

 

[beartooth91]

Are you sure it should be Workbooks(AISCAN.xlsx) rather than Workbooks("AISCAN.xlsx") in the copy lines?

A few other things:
1. If AISCAN.xlsx is the actual name of your workbook, it needs to be placed between double-quotes, otherwise it is treated as a variable.
2. I believe "Copy" is used with ranges, not values. You should just be able to set the values equal to your variables, i.e.

Workbooks("AISCAN.xlsx").Sheets(1).Range("R" & e).Value = splitsiglvl(0)
Workbooks("AISCAN.xlsx").Sheets(1).Range("S" & e).Value = splitsiglvl(1)

Still can't get it to work. Getting the 'subscript out of range' error.

 

[Joe4]

If you hit debug, what line of code does it highlight?

You may need to break it up activate the other workbook first before updating the values, i.e.

Workbooks("AISCAN.xlsx").Activate
Sheets(1).Range("R" & e).Value = splitsiglvl(0)
Sheets(1).Range("S" & e).Value = splitsiglvl(1)

 

[jolivanes]

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

 

[beartooth91]

If you hit debug, what line of code does it highlight?

You may need to break it up activate the other workbook first before updating the values, i.e.

Workbooks("AISCAN.xlsx").Activate
Sheets(1).Range("R" & e).Value = splitsiglvl(0)
Sheets(1).Range("S" & e).Value = splitsiglvl(1)

Yeah, I just tried that and getting the 'subscript out of range' error on the line right above End If.....

Sub Split_String()

Dim splitsiglvl() As String, finalRow As Long, e As Long, i As Long
e = Workbooks("AISCAN.xlsx").Sheets(1).Cells(Rows.Count, "C").End(xlUp).Row + 1
Workbooks("NIC Master Database.xlsm").Worksheets("Analog Inputs").Activate
'With Workbooks("NIC Master Database.xlsm").Worksheets ("Analog Inputs")
  finalRow = Workbooks("NIC Master Database.xlsm").Worksheets("Analog Inputs").Cells(11, "B").SpecialCells(xlLastCell).Row
  
  For i = 11 To finalRow
    If Cells(i, "AR").Value Like "4-20*" Then
       splitsiglvl = Split(Cells(i, "AR").Value)
       Workbooks("AISCAN.xlsx").Worksheets(1).Activate
       Workbooks("AISCAN.xlsx").Worksheets(1).Range("R" & e).Value = splitsiglvl(0)
       Workbooks("AISCAN.xlsx").Worksheets(1).Range("S" & e).Value = splitsiglvl(1)
    End If
  Next i
'End With
End Sub

 

[beartooth91]

Yeah, I just tried that and getting the 'subscript out of range' error on the line right above End If.....

Sub Split_String()

Dim splitsiglvl() As String, finalRow As Long, e As Long, i As Long
e = Workbooks("AISCAN.xlsx").Sheets(1).Cells(Rows.Count, "C").End(xlUp).Row + 1
Workbooks("NIC Master Database.xlsm").Worksheets("Analog Inputs").Activate
'With Workbooks("NIC Master Database.xlsm").Worksheets ("Analog Inputs")
  finalRow = Workbooks("NIC Master Database.xlsm").Worksheets("Analog Inputs").Cells(11, "B").SpecialCells(xlLastCell).Row
 
  For i = 11 To finalRow
    If Cells(i, "AR").Value Like "4-20*" Then
       splitsiglvl = Split(Cells(i, "AR").Value)
       Workbooks("AISCAN.xlsx").Worksheets(1).Activate
       Workbooks("AISCAN.xlsx").Worksheets(1).Range("R" & e).Value = splitsiglvl(0)
       Workbooks("AISCAN.xlsx").Worksheets(1).Range("S" & e).Value = splitsiglvl(1)
    End If
  Next i
'End With
End Sub

Also, there's something wrong with the split as its copying the complete '4-20mA' string into the R cell on the other worksheet.

 

[LegendaryAxe]

Also, there's something wrong with the split as its copying the complete '4-20mA' string into the R cell on the other worksheet.

That's the reason you're getting the error. You're trying to split but the entire string is going into splitsiglvl(0) and there is no splitsiglvl(1) (as the string is not being split).

 

[Micron]

Thinking that is because a value in some row is causing the array to have only one member. I'd use an error handler to trap that instance and debug.print the row number then look at the data - or just look and see if you can find it.

 

[LegendaryAxe]

If you're splitting based on "-" you should use splitsiglvl = Split(Cells(i, "AR").Value, "-")

The default delimiter is a space (which isn't what you want).

 

[beartooth91]

That's the reason you're getting the error. You're trying to split but the entire string is going into splitsiglvl(0) and there is no splitsiglvl(1) (as the string is not being split).

Yep, and I fixed that part of it. I corrected it to

 splitsiglvl = Split(Cells(i, "AR").Value, "-")

Splitting correctly but not looping. Just get one set of entries on the other worksheet.