Autofill Code 1

[luceze]

Does anyone know how to write a macro that will autofill a range if there is data in the cell to the left. (the same as double clicking on the autofill square)
I have this code
Selection.AutoFill Destination:=Range("a1:a5&quot
which works but I have to state the range.

If anyone understands what I'm talking about I would appreciate the help.

Eric

 

[Sypher2]

I thought using a Selection.Offset in the Destination might work, but it just gave me an error. You may have to use a Copy/PasteSpecial routine instead of Autofill. Use the Offset property to select where to paste the formula.

 

[DaleWatson123321]

Eric,

Here's a routine which works. I avoided using the "AutoFill" option because the "Source" cell must be included in the "Destination", plus I had other difficulties. With my method, it requires that you have a cell (off to the side somewhere) which is formattedd with your fill of choice, and you need to name the cell. I have used the name "filltype" in the following routine.

As requested, the routine first checks for a value in the cell to the left of the cursor.

Sub Fill_Cells()
num = ActiveCell.Offset(0, -1).Value
If num = "" Then Exit Sub
Range("filltype&quot.Copy
curcell = ActiveCell.Address
ActiveCell.Offset(4, 0).Select
botcell = ActiveCell.Address
fillRange = curcell & ":" & botcell
Range(fillRange).Select
ActiveSheet.Paste
End Sub

Hope this works to your satisfaction.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[luceze]

Thanks Dale. As usual you were right on the mark.