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 Gary's Student · Accepted Answer · 2016-01-18T17:30:41

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))

Split cells with formula in next row Excel vba

1 Answers