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.

 

[Micron]

use a combination of string functions (replace 4-20ma below with your looped cell references)
left("4-20ma",1) & mid("4-20ma",instr("4-20ma","ma"))
mid("4-20ma",Instr("4-20ma","-")+1)

That will only be as good as the consistency of your data that you posted.

 

[LegendaryAxe]

Assuming Column A has the instrument names and Column B has the ranges, try this:

Option Explicit

Sub TextSplit()

Dim r As Range
Dim SplitText() As String
Dim MachineNames As Range
Dim ws As Worksheet

Worksheets("Sheet2").Activate

Set ws = Worksheets("Sheet1")

For Each r In ws.Range("A2", ws.Range("A2").End(xlDown))
    Range("A" & r.Row).Value = r.Value
    SplitText = Split(r.Offset(0, 1).Value, "-")
    Range("B" & r.Row).Value = SplitText(0) & Right(r.Offset(0, 1).Value, 2)
    Range("C" & r.Row).Value = SplitText(1)
Next r
   
End Sub

 

[LegendaryAxe]

And here's a non-VBA solution:

Book1.xlsm
	A	B	C
1	Instrument Ranges	Min	Max
2	29-34ma	29ma	34ma
3	1-51ma	1ma	51ma
4	14-48ma	14ma	48ma
5	13-38ma	13ma	38ma
6	13-40ma	13ma	40ma
7	2-69ma	2ma	69ma
8	16-61ma	16ma	61ma
9	20-51ma	20ma	51ma
10	9-35ma	9ma	35ma
11	21-44ma	21ma	44ma
12	1-64ma	1ma	64ma
13	28-43ma	28ma	43ma
14	28-45ma	28ma	45ma
15	4-42ma	4ma	42ma
16	19-39ma	19ma	39ma
Sheet1

Cell Formulas
Range	Formula
B2:B16	B2	=LEFT(A2,SEARCH("-",A2)-1)&RIGHT(A2,2)
C2:C16	C2	=MID(A2,SEARCH("-",A2)+1,10)

 

[beartooth91]

This is what I'm trying to do. And getting a Compile Error: Expected Array on the first copy line.

Sub Split_String()

Dim splitsiglvl As String, finalRow As Long, e As Long, i As Long

e = Workbooks(AISCAN.xlsx).Sheets(1).Range("C" & Rows.Count).End(xlUp).Row

With Workbooks("NIC Master Database.xlsm").Worksheets("Analog Inputs")

  finalRow = .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)

       splitsiglvl(0).Copy Destination:=Workbooks(AISCAN.xlsx).Sheets(1).Range("R" & e)

       splitsiglvl(1).Copy Destination:=Workbooks(AISCAN.xlsx).Sheets(1).Range("S" & e)

    End If

  Next i

End Sub

 

[Joe4]

If you want "splitsiglvl" to be an array, you need ellipses in your variable declaration, i.e.

Dim splitsiglvl() As String

 

[beartooth91]

Well, can't get it to work. Current code as follows, below. I'm getting a Compile Error: Invalid Qualifier on the first copy line.

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("AISCAN.xlsx").Worksheets(1).Activate
'Set ws = Workbooks("NIC Master Database.xlsm").Worksheets("Analog Inputs")
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)
       splitsiglvl(0).Copy Destination:=Workbooks(AISCAN.xlsx).Sheets(1).Range("R" & e)
       splitsiglvl(1).Copy Destination:=Workbooks(AISCAN.xlsx).Sheets(1).Range("S" & e)
    End If
  Next i
End With
End Sub

 

[LegendaryAxe]

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

 

[Joe4]

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)

 

[beartooth91]

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

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.....?