Split and transpose cell value
On Wednesday, November 1, 2017 at 5:11:20 AM UTC-7, Claus Busch wrote:
Hi Howard,
Am Tue, 31 Oct 2017 21:19:10 -0700 (PDT) schrieb L. Howard:
Sub Split_Transpose()
Dim oneRng As Range, ddFound As Range
Dim dDwn As String
Dim X As Variant
dDwn = Sheets("Sheet4").Range("J8")
Set oneRng = Sheets("Sheet5").Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row)
With Sheets("Sheet5")
Set ddFound = Sheets("Sheet5").UsedRange.Find(What:=dDwn, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not ddFound Is Nothing Then
X = Split(ddFound.Offset(, 1).Value, ",")
Sheets("Sheet5").Range("C2").Resize(UBound(X) - LBound(X) + 1).Value = Application.Transpose(X)
Else
MsgBox "No match found."
End If
End With
End Sub
there are some superfluous lines in your code.
Try:
Sub Split_Transpose()
Dim oneRng As Range, ddFound As Range
Dim dDwn As String
Dim X As Variant
dDwn = Sheets("Sheet4").Range("J8")
With Sheets("Sheet5")
Set ddFound = .Range("A:A").Find(What:=dDwn, _
LookIn:=xlValues, _
LookAt:=xlWhole)
If Not ddFound Is Nothing Then
X = Split(ddFound.Offset(, 1).Value, ",")
.Range("C2").Resize(UBound(X) + 1).Value = Application.Transpose(X)
Else
MsgBox "No match found."
End If
End With
End Sub
Regards
Claus B.
Hi Claus,
Yes, some lines not needed that I had not cleaned up.
Yours is, of course, quite tidy.
Thanks much.
Howard
|