PDA

View Full Version : [SOLVED] Change VBA from AutoFill to FillDown



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

Zlerp
09-12-2014, 07:08 AM
Figured it out!! I need to declare the type of Autofill i want!

.Cells(i, "A").Resize(, 24).AutoFill .Cells(i, "A").Resize(cntNums, 24), Type:=xlFillColumn

snb
09-12-2014, 07:30 AM
No, you should add code tags around VBA code in your post first !

Zlerp
09-12-2014, 07:50 AM
Thanks for the Info snb!

snb
09-12-2014, 09:33 AM
That really makes a difference in readability !