Debug Filldown (THIS POST IS NOW CLOSED)

[Jak]

Trying to fill down a value that appears in cell A2. The following code errors at the line:

Set FillRange = Range(Cells(x, y), Cells(r - 1, y))

Tried to debug it but to no avail. Any suggestions with the code welcomed.

Dim FillRange As Range
Dim FillWith As Range

Dim x As Long
Dim y As Long
Dim r As Long
Dim EndRow As Long

EndRow = Cells.SpecialCells(xlCellTypeLastCell).Row
x = 2 'Start at row 2
y = 1 'Column equals A

Do Until x > EndRow

r = Cells.SpecialCells(xlCellTypeBlanks).Row

Set FillRange = Range(Cells(x, y), Cells(r - 1, y))Set FillWith = Cells(x, y)
FillWith.AutoFill FillRange

x = r + 1
Loop

 

[Egress1]

Re: Debug Filldown

Trying to fill down a value that appears in cell A2. The following code errors at the line:

Set FillRange = Range(Cells(x, y), Cells(r - 1, y))

Set FillRange = Range(Cells(x, y), Cells(r - 1, y))Set FillWith = Cells(x, y)
FillWith.AutoFill FillRange

x = r + 1
Loop

Needs a colon between the two set commands if they're on the same line of code

Set FillRange = Range(Cells(x, y), Cells(r - 1, y)):Set FillWith = Cells(x, y)

 

[Jak]

thanks for that Egress1

Still errors on that line, no filldown

 

[Egress1]

thanks for that Egress1

Still errors on that line, no filldown

Ok, now your error occurs when the value of r = 1. The statement
Cells(r-1,y) is causing your error as r-1 = 0

Wash
Rinse
Repeat

 

[Egress1]

thanks for that Egress1

Still errors on that line, no filldown

What is special about the blank rows? Are you trying to fill just the blank cells or are you trying to fill everything BUT the blank cells?

 

[Jak]

I am trying to fill down blank cells. I use an input method to enter a value in cell "A2" and I need it to filldown to the last row in the used range. In the past I would refrence a column that always contained data and use code such as:

batchnumber = InputBox("Enter the Batch Number:")

Dim z As Range
Set z = Range("D2")
While Not IsEmpty(z.Value)
z.Offset(0, -1).Value = batchnumber
Set z = z.Offset(1, 0)
Wend
Set z = Nothing

The problem with this method is that the data I have been working with of late has numerous blank cells dotted around throughout and the filldown fails when a blank cell is encountered, hence the new approach.

HTH

 

[Egress1]

Well how about something like this?

endrow = Cells.SpecialCells(xlCellTypeLastCell).Row
[a3].Select
For k = 1 To endrow - 2
ActiveCell.Value = [a2]
ActiveCell.Offset(1, 0).Activate
Next k

 

[Jak]

Many thanks Egress1

That works great. Thanks for all your input.

 

[Legacy 12630]

Alternatively, some one-liners (this post has just been re-opened) :

EITHER :-
[A2].Copy Range([A3], Cells(Cells.SpecialCells(xlCellTypeLastCell).Row, 1))

OR :-
Range("A2:A" & Cells.SpecialCells(xlCellTypeLastCell).Row).FillDown

 

[Jak]

Thanks Ponsonby

Just noticed your contribution, sorry I closed the post early. Thanks for the excellent tips. :D