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.

 

[LegendaryAxe]

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.

What's looping is "i". "e" is the variable you're using to enter values but that variable never changes in the loop, so the values will just be entered in that same position over and over again.

 

[LegendaryAxe]

So before the "End If" try adding a line that says "e = e + 1" and see if that fixes it.

 

[jolivanes]

Post #14 macro does what needs to be done if you add requirements like workbook/sheet names

Just curious. What is the result of Post #14?

 

[beartooth91]

So before the "End If" try adding a line that says "e = e + 1" and see if that fixes it.

No change

 

[beartooth91]

Post #14 macro does what needs to be done if you add requirements like workbook/sheet names

Just curious. What is the result of Post #14?

Haven't tried it as I'd like to understand the vba for arrays a bit better.

 

[jolivanes]

Re: Haven't tried it.
It's nice to know I wasted my time.
Happy new year.

 

[beartooth91]

Re: Haven't tried it.
It's nice to know I wasted my time.
Happy new year.

Actually, I really do appreciate your effort on it. I'll go through it and try it....I just need to understand how it works.

 

[jolivanes]

If you want to go by line numbers, the "Sub Or_Maybe_So()" being line 1.
line 3 puts all your data in an array, assuming the data is in Column A starting at the second row.
Line 4 resizes the array to 3 "columns" wide while maintaing the values already in it
line 5 tells it to cycle or loop from the first entry to the last entry
line 6 puts the value before the "-" into the second "column" of the array
Line 7 puts the value following the "-" into the third "column" of the array
Line 8 tells it togo the the next row down in the array
Line 9 cycles or loops column wise through the array starting at the second column
Line 10 outputs that column data into a column on your sheet. Since the loop starts at 2, it uses that value as a column number in this case because it is next door to your original data. As mentioned in the post, that needs changing to fit your needs. I posted the data in Post #14 before I had readall the posts between Post #2 and the rest of the posts.
Line 11 tells it to go to the next column in the array and do the same as with the 2nd column but one column over

 

[beartooth91]

If you want to go by line numbers, the "Sub Or_Maybe_So()" being line 1.
line 3 puts all your data in an array, assuming the data is in Column A starting at the second row.
Line 4 resizes the array to 3 "columns" wide while maintaing the values already in it
line 5 tells it to cycle or loop from the first entry to the last entry
line 6 puts the value before the "-" into the second "column" of the array
Line 7 puts the value following the "-" into the third "column" of the array
Line 8 tells it togo the the next row down in the array
Line 9 cycles or loops column wise through the array starting at the second column
Line 10 outputs that column data into a column on your sheet. Since the loop starts at 2, it uses that value as a column number in this case because it is next door to your original data. As mentioned in the post, that needs changing to fit your needs. I posted the data in Post #14 before I had readall the posts between Post #2 and the rest of the posts.
Line 11 tells it to go to the next column in the array and do the same as with the 2nd column but one column over

I'm following most of it, but am lost on Line 10, specifically Application.Index and where/how/syntax for the 2nd worksheet (in this case AISCAN)......

Cells(2, j).Resize(UBound(allArr)) = Application.Index(allArr, , j) '<----Change/sheet name required

 

[jolivanes]

This is from what I gather from your Post #5 references.
Both workbooks need to be open and you macro is run from wb1
Change sh1, sh2, wb1 and wb2 to more meaningful names
Both workbooks were previously saved somewhere.
In the "Set wb1" line, change the workbook name to the workbook that has the non split data.
In the "Set wb2" line, change the workbook name to the workbook that will receive the split data.
Split data will be placed starting at the same row of the last used cell in sh2 which is the receiving sheet in the receiving workbook.
Code has no error checking in case cell value does not have a "-"
The j+16 in the 3rd last line is just a way to end up in the right column, here 2 + 16 = 18 = Column R

Sub Or_Maybe_So_Diff_Books()
Dim allArr, i As Long, j As Long, lr As Long
Dim wb1 As Workbook, wb2 As Workbook
Dim sh1 As Worksheet, sh2 As Worksheet
Set wb1 = Workbooks("Name of workbook that has the data to be split.xlsm")    '<----Change required
Set wb2 = Workbooks("Name of the workbook that receives the split data.xlsm")    '<----Change required
Set sh1 = wb1.Sheets("Name of the sheet that has the data")    '<----Change required
Set sh2 = wb2.Sheets("Name of the sheet where the split data will be pasted")    '<----Change required
allArr = sh1.Range("A2:A" & sh1.Cells(sh1.Rows.Count, 1).End(xlUp).Row).Value    '<----Change required. Here from A2 on down.
lr = sh2.Cells(sh2.Rows.Count, 3).End(xlUp).Row    '<----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)
        sh2.Cells(lr, j+16).Resize(UBound(allArr)) = Application.Index(allArr, , j)    '<----Change/sheet name required
    Next j
End Sub

VBA - Pasting only specific columns from array

following a question I have asked recently I have created an array to copy data from one worksheet to another. My problem is that the array includes many columns I do not need in the new worksheet....

stackoverflow.com

WorksheetFunction.Index method (Excel)

Office VBA reference topic

learn.microsoft.com

BTW, you don't need to quote as all it does is create clutter for people that copy posts to work with.