VBA Adjustment - Rick

kparadise

Board Regular
Joined
Aug 13, 2015
Messages
186
Hello,

Rick provided me with this VBA code which works great!

Code:
Sub ID_RiskLevel3_LegalObl()
  Dim R As Long, X As Long, Z As Long, LastRow As Long, MaxNewRows As Long
  Dim MaxCol2 As Long, MaxCol3 As Long, ID As String
  Dim Data As Variant, Result As Variant, RL3 As Variant, LO As Variant
  LastRow = Cells(Rows.Count, "C").End(xlUp).Row
  Data = Range("A2:C" & LastRow).Value
  For R = 1 To UBound(Data)
    MaxCol2 = Len(Data(R, 2)) - Len(Replace(Data(R, 2), vbLf, ""))
    MaxCol3 = Len(Data(R, 3)) - Len(Replace(Data(R, 3), vbLf, ""))
    MaxNewRows = MaxNewRows + Application.Max(MaxCol2, MaxCol3) + 1
  Next
  ReDim Result(1 To MaxNewRows, 1 To 3)
  For R = 1 To UBound(Data)
    If Len(Data(R, 1)) > 0 And Data(R, 1) <> ID Then ID = Data(R, 1)
    RL3 = Split(Data(R, 2), vbLf)
    LO = Split(Data(R, 3), vbLf)
    If UBound(RL3) > UBound(LO) Then
      ReDim Preserve LO(0 To UBound(RL3))
    ElseIf UBound(RL3) < UBound(LO) Then
      ReDim Preserve RL3(0 To UBound(LO))
    End If
    For Z = 0 To UBound(RL3)
      X = X + 1
      Result(X, 1) = ID
      Result(X, 2) = RL3(Z)
      Result(X, 3) = LO(Z)
    Next
  Next
  Range("A2").Resize(UBound(Result), 3) = Result
End Sub

Basically - this code looks at three columns and splits columns two and three while simply copying down column one. Was I am trying to do is to keep the identical function of this code; but instead of splitting columns 2 and 3, I need it to split columns 3 and 4. And there will be a new column 2 where I need it to copy down just like column 1 of the original code.

So, just pretend that before I run the code above, I inserted a column between A [ID] and B [RiskLevel3] and it is called [Team]. I want [ID] and [Team] to be copied down while [RiskLevel3] and [LegalObl] are split.
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,148
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Hello,

Rick provided me with this VBA code which works great!

Code:
Sub ID_RiskLevel3_LegalObl()
  Dim R As Long, X As Long, Z As Long, LastRow As Long, MaxNewRows As Long
  Dim MaxCol2 As Long, MaxCol3 As Long, ID As String
  Dim Data As Variant, Result As Variant, RL3 As Variant, LO As Variant
  LastRow = Cells(Rows.Count, "C").End(xlUp).Row
  Data = Range("A2:C" & LastRow).Value
  For R = 1 To UBound(Data)
    MaxCol2 = Len(Data(R, 2)) - Len(Replace(Data(R, 2), vbLf, ""))
    MaxCol3 = Len(Data(R, 3)) - Len(Replace(Data(R, 3), vbLf, ""))
    MaxNewRows = MaxNewRows + Application.Max(MaxCol2, MaxCol3) + 1
  Next
  ReDim Result(1 To MaxNewRows, 1 To 3)
  For R = 1 To UBound(Data)
    If Len(Data(R, 1)) > 0 And Data(R, 1) <> ID Then ID = Data(R, 1)
    RL3 = Split(Data(R, 2), vbLf)
    LO = Split(Data(R, 3), vbLf)
    If UBound(RL3) > UBound(LO) Then
      ReDim Preserve LO(0 To UBound(RL3))
    ElseIf UBound(RL3) < UBound(LO) Then
      ReDim Preserve RL3(0 To UBound(LO))
    End If
    For Z = 0 To UBound(RL3)
      X = X + 1
      Result(X, 1) = ID
      Result(X, 2) = RL3(Z)
      Result(X, 3) = LO(Z)
    Next
  Next
  Range("A2").Resize(UBound(Result), 3) = Result
End Sub

Basically - this code looks at three columns and splits columns two and three while simply copying down column one. Was I am trying to do is to keep the identical function of this code; but instead of splitting columns 2 and 3, I need it to split columns 3 and 4. And there will be a new column 2 where I need it to copy down just like column 1 of the original code.

So, just pretend that before I run the code above, I inserted a column between A [ID] and B [RiskLevel3] and it is called [Team]. I want [ID] and [Team] to be copied down while [RiskLevel3] and [LegalObl] are split.
Am I the Rick you are referring to? Some of that code looks like what I would have written while other parts don't. Can you post a link to the thread where the above code was posted to (I am a little unclear as to your layout and think seeing the original thread would help me in figuring out why I constructed the code the way I did, assuming I am the Rick you were referring to of course)?
 

kparadise

Board Regular
Joined
Aug 13, 2015
Messages
186

ADVERTISEMENT

There is going to be text data in that cell. But I just want it to follow what Column A does. copy it down until the new column A ID is located.

Did that help?
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,148
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
There is going to be text data in that cell. But I just want it to follow what Column A does. copy it down until the new column A ID is located.
Give this macro a try...
Code:
[table="width: 500"]
[tr]
	[td]Sub ID_RiskLevel3_LegalObl_With_New_Col_B()
  Dim R As Long, X As Long, Z As Long, LastRow As Long, MaxNewRows As Long
  Dim MaxCol3 As Long, MaxCol4 As Long, ID As String, NewHead As String
  Dim Data As Variant, Result As Variant, RL3 As Variant, LO As Variant
  LastRow = Cells(Rows.Count, "C").End(xlUp).Row
  Data = Range("A2:D" & LastRow).Value
  For R = 1 To UBound(Data)
    MaxCol3 = Len(Data(R, 3)) - Len(Replace(Data(R, 3), vbLf, ""))
    MaxCol4 = Len(Data(R, 4)) - Len(Replace(Data(R, 4), vbLf, ""))
    MaxNewRows = MaxNewRows + Application.Max(MaxCol3, MaxCol4) + 1
  Next
  ReDim Result(1 To MaxNewRows, 1 To 4)
  For R = 1 To UBound(Data)
    If Len(Data(R, 1)) > 0 And Data(R, 1) <> ID Then ID = Data(R, 1)
    If Len(Data(R, 2)) > 0 And Data(R, 2) <> NewHead Then NewHead = Data(R, 2)
    RL3 = Split(Data(R, 3), vbLf)
    LO = Split(Data(R, 4), vbLf)
    If UBound(RL3) > UBound(LO) Then
      ReDim Preserve LO(0 To UBound(RL3))
    ElseIf UBound(RL3) < UBound(LO) Then
      ReDim Preserve RL3(0 To UBound(LO))
    End If
    For Z = 0 To UBound(RL3)
      X = X + 1
      Result(X, 1) = ID
      Result(X, 2) = NewHead
      Result(X, 3) = RL3(Z)
      Result(X, 4) = LO(Z)
    Next
  Next
  Range("A2").Resize(UBound(Result), 4) = Result
End Sub[/td]
[/tr]
[/table]
 

Forum statistics

Threads
1,136,341
Messages
5,675,206
Members
419,553
Latest member
hanahass

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top