Address SPLIT

[sllaksvb]

Hi all,

I am currently working on a macro that transfers data from a worksheet to another. I'm having difficulties with splitting up the address into 3 columns. I need the address in Sheets("Sheet1").Range("C19") to be copied and split into Sheets("Sheet2").Range("AD3:AF3"). The address should be split at the commas, with the first section in AD3, second in AE3, and the last in AF3. add split.xlsx

[SamT]

Sheets("Sheet2").Range("AD3:AF3") = WorksheetFunction.Transpose(Split(Sheets("Sheet1").Range("C19"), ","))

[mdmackillop]

Hi Sam
I don't think you need the transpose

Sheets("Sheet2").Range("AD3:AF3") = Split(Sheets("Sheet1").Range("C19"), ",")

[sllaksvb]

Thank you for you response. Is it possible to loop the split to do the same for rows AD3:AF3 to AD17:AF17?

[SamT]

Does that include C19 to C33?

For i = 1 to 15
With Sheets("Sheet2")
.Range("AD" & i+2 &":AF" & i+2) = Split(Sheets("Sheet1").Range("C" & i + 18), ",") 
End with
Next

Put many more Rows in there and this will start slowing down and you will need to look at All-Array codes.

[sllaksvb]

Nope, it will be using C19 as the address, just copying it to AD3:AF3 to AD17:AF17 in sheet2. Thank you for your prompt reply!

[SamT]

Did you figure it out?

[sllaksvb]

SamT, I managed to figure out, embedded an if statement as well. Does exactly what I need it to!

For c = 1 To 15
    If Cells(c + 2, 1) <> "" Then
    With Sheets("Sheet2")
        .Range("AD" & c + 2 & ":AF" & c + 2) = Split(Sheets("Sheet1").Range("C19"), ", ")
    End With
    Else: Range("AD" & c + 2 & ":AF" & c + 2).Value = ""
    End If
    Next c

Thank you for your help!

[mdmackillop]

FYI "Resize" is easier to handle and adjust
Range("AD" & c + 2 & ":AF" & c + 2) ==> Range("AD" & c + 2).Resize(,3)

[snb]

reducing the worksheet interaction:

Sub M_snb()
     sn = [C19:E24]
     
     For j = 1 To UBound(sn)
        st = Split(sn(j, 1), ",")
        sn(j, 1) = st(0)
        sn(j, 2) = st(1)
        sn(j, 3) = st(2)
     Next
     
     Sheet2.Cells(Rows.Count, 30).End(xlUp).Offset(1).Resize(UBound(sn), UBound(sn, 2)) = sn
End Sub

[SamT]

Resize? Yep.
All-Array? Yep.
Newbie? Yep. *KISE? Yep?

*Self Explanatory

[sllaksvb]

Hi snb,

I'm not entirely sure I understand what your subroutine does. Could you elaborate a little so that I could incorporate it into my code? Thank you !