Split cells with formula in next row Excel vba

Question

I am trying to split cells if contain "," to next row i am using this formula to get data from sheet1 (=Sheet1!B3) and so on and data is in B3:AF50. i use this vba code

Sub splitcells()
Dim InxSplit As Long
Dim SplitCell() As String
Dim RowCrnt As Long

 With Worksheets("Sheet2")

 RowCrnt = 3

 Do While True


  If .Cells(RowCrnt, "b").Value = "" Then
    Exit Do
  End If

  SplitCell = Split(.Cells(RowCrnt, "b").Value, ",")

  If UBound(SplitCell) > 0 Then

    .Cells(RowCrnt, "b").Value = SplitCell(0)


    For InxSplit = 1 To UBound(SplitCell)
      RowCrnt = RowCrnt + 1

      .Cells(RowCrnt, "b").Value = SplitCell(InxSplit)

      .Cells(RowCrnt, "B").Value = .Cells(RowCrnt - 1, "B").Value
    Next
  End If

  RowCrnt = RowCrnt + 1

  Loop

End With

End Sub

Problem is

I don't know how to give range(B3:Af50) in this code
This code is not keeping my code (=Sheet1!)
it is just copying the value before ","

my cell values are like that

B3 ABC,XYZ,KKK,LLL i want it to split as B3 = ABC and B4 = XYZ B5 = KKK b6 = LLL

and if value in B3 is changed it should clear the cells (B4,B5,B6)splitted early and update if split required criteria ","

Have sheet like that

after split should look like that with formula intact

Gary's Student · Accepted Answer · 2016-01-18 18:52:35Z

3

No VBA is needed.

With data in Sheet1 cell B3, put this in any cell of any sheet:

=TRIM(MID(SUBSTITUTE(Sheet1!$B3,",",REPT(" ",999)),COLUMNS($A:A)*999-998,999))

and copy across.

EDIT#1:

To copy downwards, use this formula instead:

=TRIM(MID(SUBSTITUTE(Sheet1!B$3,",",REPT(" ",999)),ROWS($1:1)*999-998,999))

edited Jan 18, 2016 at 18:52

answered Jan 18, 2016 at 17:30

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Muhammad Khurrum Butt Over a year ago

it is splitting it into Columns and also if value in Sheet 1 is change it is not updating sheet 2

Gary's Student Over a year ago

@MuhammadKhurrumButt See my EDIT#1:

Muhammad Khurrum Butt Over a year ago

Edit#1 is working good but only in first 4 rows not covering full range B3:AF50

Kzhel Farmer Over a year ago

@MuhammadKhurrumButt i think you need to change address eg =Sheet1!B4 and so on

Collectives™ on Stack Overflow

Split cells with formula in next row Excel vba

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related