CODEWITHSUNDEEP
excelexcel-formulavba
Muhammad Khurrum Butt
Muhammad Khurrum Butt

Reputation: 137

Split cells with formula in next row Excel vba

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

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

enter image description here

after split should look like that with formula intact

enter image description here

Upvotes: 1

Views: 315

Answers (1)

Gary's Student
Gary's Student

Reputation: 96791

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.

enter image description here

EDIT#1:

To copy downwards, use this formula instead:

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

Upvotes: 3

Related Questions