Zlerp
09-12-2014, 06:59 AM
Hello,
I have a code that is working fine, the only issue i have with it is that it Autofills and increments the value. I want it to just fill down with the same data. Can you please help me make this change, and a short explanation of why would be awesome, if not its ok.
.Cells(i, "A").Resize(, 24).AutoFill .Cells(i, "A").Resize(cntNums, 24)
.Cells(i, "Z").Resize(, 12).AutoFill .Cells(i, "Z").Resize(cntNums, 12)
These are the lines i need changed to FillDown
Here is what I currently have:
Public Sub BasicLoop()
Dim rowLast As Long
Dim cntNums As Long
Dim ary As Variant
Dim i As Long
Application.ScreenUpdating = False
With ActiveSheet
rowLast = .Cells(.Rows.Count, "Y").End(xlUp).Row
For i = rowLast To 5 Step -1
If InStr(.Cells(i, "Y").Value, ";") > 0 Then
ary = Split(.Cells(i, "Y").Value, ";")
cntNums = UBound(ary) - LBound(ary) + 1
.Rows(i + 1).Resize(cntNums - 1).Insert
.Cells(i, "A").Resize(, 24).AutoFill .Cells(i, "A").Resize(cntNums, 24)
.Cells(i, "Y").Resize(cntNums) = Application.Transpose(ary)
.Cells(i, "Z").Resize(, 12).AutoFill .Cells(i, "Z").Resize(cntNums, 12)
End If
Next i
End With
Application.ScreenUpdating = True
End Sub
Thank you for your help!!
I have a code that is working fine, the only issue i have with it is that it Autofills and increments the value. I want it to just fill down with the same data. Can you please help me make this change, and a short explanation of why would be awesome, if not its ok.
.Cells(i, "A").Resize(, 24).AutoFill .Cells(i, "A").Resize(cntNums, 24)
.Cells(i, "Z").Resize(, 12).AutoFill .Cells(i, "Z").Resize(cntNums, 12)
These are the lines i need changed to FillDown
Here is what I currently have:
Public Sub BasicLoop()
Dim rowLast As Long
Dim cntNums As Long
Dim ary As Variant
Dim i As Long
Application.ScreenUpdating = False
With ActiveSheet
rowLast = .Cells(.Rows.Count, "Y").End(xlUp).Row
For i = rowLast To 5 Step -1
If InStr(.Cells(i, "Y").Value, ";") > 0 Then
ary = Split(.Cells(i, "Y").Value, ";")
cntNums = UBound(ary) - LBound(ary) + 1
.Rows(i + 1).Resize(cntNums - 1).Insert
.Cells(i, "A").Resize(, 24).AutoFill .Cells(i, "A").Resize(cntNums, 24)
.Cells(i, "Y").Resize(cntNums) = Application.Transpose(ary)
.Cells(i, "Z").Resize(, 12).AutoFill .Cells(i, "Z").Resize(cntNums, 12)
End If
Next i
End With
Application.ScreenUpdating = True
End Sub
Thank you for your help!!