Flexible address for autofill

[clark8529]

Hi,
I am working on a flexible code which does auto-filling in excel spreadsheet.
The algorithm I thought of is using address as the reference to where the auto fill should be. However, I cannot get this code to work

Range("A1").Select
col0 = ActiveCell.Column
ActiveCell.Offset(0, 1).Select
add1 = ActiveCell.address
col1 = ActiveCell.Column
ActiveCell.Value = 1
ActiveCell.Offset(1, 0).Select
add2 = ActiveCell.address
ActiveCell.Value = 2
Range(add1, add2).autofill Destination:=Range(add1,col1 & Range(col1 & Rows.Count).End(xlUp).Row)

The part that failed is in the last line, I am not sure how to modify that.

Thanks.

[Paul_Hossler]

Your macro was a little too complicated for me

This uses data in col A (say A1:A10) then puts 1 in B1 and 2 in B2 and then autofills down to B1:B10 with 1,2,3,... 10



Option Explicit

Sub FillCells()
Dim r1 As Range, r2 As Range

Set r1 = Range("A1")
Set r2 = r1.End(xlDown)

Set r1 = r1.Offset(0, 1)
Set r2 = r2.Offset(0, 1)

r1.Value = 1
r1.Offset(1, 0).Value = 2

Range(r1, r1.Offset(1, 0)).AutoFill Destination:=Range(r1, r2)
End Sub

[SamT]

Range(add1, add2).autofill Works

Range(add1,col1 & Range(col1 & Rows.Count) Means
Range("B2", "21") "1" is the Row that Range("21,048,576").End(xlUP).Row will return. Unless you accidentally put some value in the column Cells("21,048,576") happens to fall in.

IOW, "Range(add1,col1 & Range(col1 & Rows.Count).End(xlUp).Row)" is GIGO And I don't know what exactly you are trying to do.

Run this Code

Range("A1").Select
MsgBox "Message #1: The Active Cell is " & ActiveCell.Address
col0 = ActiveCell.Column
ActiveCell.Offset(0, 1).Select
MsgBox "Message #2: The Active Cell is " & ActiveCell.Address

add1 = ActiveCell.address
col1 = ActiveCell.Column

ActiveCell.Value = 1
ActiveCell.Offset(1, 0).Select
MsgBox "Message #3: The Active Cell is " & ActiveCell.Address

add2 = ActiveCell.address
ActiveCell.Value = 2

MsgBox "Message #4: The wierd address is " & Range(add1,col1 & Range(col1 & Rows.Count).End(xlUp).Row).Address

[SamT]

Hey Paul,

Range(r1, r1.Offset(1, 0))
Is that what "tells" autoFill to fill by a series of 1s?

Would it "count by twos" if B2's value was 3

[clark8529]

Your macro was a little too complicated for me

This uses data in col A (say A1:A10) then puts 1 in B1 and 2 in B2 and then autofills down to B1:B10 with 1,2,3,... 10



Option Explicit

Sub FillCells()
Dim r1 As Range, r2 As Range

Set r1 = Range("A1")
Set r2 = r1.End(xlDown)

Set r1 = r1.Offset(0, 1)
Set r2 = r2.Offset(0, 1)

r1.Value = 1
r1.Offset(1, 0).Value = 2

Range(r1, r1.Offset(1, 0)).AutoFill Destination:=Range(r1, r2)
End Sub



Hey Paul,
I wanted the code to autofill depending on the left column's value. The autofill is a series of autofill when we use Excel, from 1 to x, where x is the last row number which the cell on the left is not empty.
However, I would opt to create a sub which is flexible enough for the autofill to start from any cell within the spreadsheet, ie. not hardcoded to 1 particular point.
Alternative way of producing the same result is as follow:



Dim i As Long
i = 1
Do While Not ActiveCell.Value = ""
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = i
i = i + 1
ActiveCell.Offset(1, -1).Select
Loop

End Sub

Your suggested code didn't work if the first reference cell is not A1.

[clark8529]

Range(add1, add2).autofill Works

Range(add1,col1 & Range(col1 & Rows.Count) Means
Range("B2", "21") "1" is the Row that Range("21,048,576").End(xlUP).Row will return. Unless you accidentally put some value in the column Cells("21,048,576") happens to fall in.

IOW, "Range(add1,col1 & Range(col1 & Rows.Count).End(xlUp).Row)" is GIGO And I don't know what exactly you are trying to do.

Run this Code

Range("A1").Select
MsgBox "Message #1: The Active Cell is " & ActiveCell.Address
col0 = ActiveCell.Column
ActiveCell.Offset(0, 1).Select
MsgBox "Message #2: The Active Cell is " & ActiveCell.Address

add1 = ActiveCell.address
col1 = ActiveCell.Column

ActiveCell.Value = 1
ActiveCell.Offset(1, 0).Select
MsgBox "Message #3: The Active Cell is " & ActiveCell.Address

add2 = ActiveCell.address
ActiveCell.Value = 2

MsgBox "Message #4: The wierd address is " & Range(add1,col1 & Range(col1 & Rows.Count).End(xlUp).Row).Address



Hey SamT,
I am trying to make an autofill function depending on the values on the left column. If the value in the left column is empty, the script stops. The autofill is a series from 1 to x, where x is the last row of a filled cell in the left column. The autofill should work regardless the first cell's position, ie. the first reference cell position is not hardcoded.
I should have removed "Range("A1").Select from the code. I just noticed that error but code still doesn't work.

The last line is giving me an error "Method 'Range' of object '_Global' failed.

After re-reading your explanation, it means I cannot use Activecell.Column to define the column address.
How do I make this flexible?
Originally:

Range("A1:B" & Range("A" & Rows.Count).End(xlUp).Row)

[SamT]

Assume that Column A is filled down to some Row, ie A1 to A10.
Assume that you want to select A3 before running the Code.
You then want to autofill B3 to B10 with the numbers 1 to 8.

In actuality, you want to select a cell in any column and have the column to the right, Autofilled down from next to the selected Cell to next to the bottom used cell of the selected Column.

Is that correct?

If YES, then the Range you want autofilled is

Range(Selection.Offset(, 1), Selection.End(xlDown)).Offset(,1)

If YES, AND, you expect some cell in the selected column above the bottom used cell to be empty, (A1:A5 and A7:A10 have values, BUT, you still want B3:B10 to be Autofilled) THEN, the range to AutoFill is

Range(Selection.Offset(, 1), Cells(Rows.Count, Selection.Column)End(xlUp)).Offset(,1)

Note:A Range Address is (Column&Row), But A Cells address is (Row, Column)

Note: A double ended range is Range(RangeObject1, RangeObject2)

[clark8529]

Assume that Column A is filled down to some Row, ie A1 to A10.
Assume that you want to select A3 before running the Code.
You then want to autofill B3 to B10 with the numbers 1 to 8.

In actuality, you want to select a cell in any column and have the column to the right, Autofilled down from next to the selected Cell to next to the bottom used cell of the selected Column.

Is that correct?

If YES, then the Range you want autofilled is

Range(Selection.Offset(, 1), Selection.End(xlDown)).Offset(,1)

If YES, AND, you expect some cell in the selected column above the bottom used cell to be empty, (A1:A5 and A7:A10 have values, BUT, you still want B3:B10 to be Autofilled) THEN, the range to AutoFill is

Range(Selection.Offset(, 1), Cells(Rows.Count, Selection.Column)End(xlUp)).Offset(,1)

Note:A Range Address is (Column&Row), But A Cells address is (Row, Column)

Note: A double ended range is Range(RangeObject1, RangeObject2)


I placed the code exactly this way... And it gives me an error "Autofill method of Range class failed".


Sub test2()
'Start with selected cell A1. A1 to A10 is filled with values. A11 onwards is empty.

Activecell.Offset(0,1).Select
add1 = ActiveCell.address
ActiveCell.Value = 1
ActiveCell.Offset(1, 0).Select
add2 = ActiveCell.address
ActiveCell.Value = 2
Range(add1, add2).autofill Destination:=Range(Selection.Offset(, 1), Selection.End(xlDown)).Offset(, 1)

End Sub



Range(Selection.Offset(, 1), Selection.End(xlDown)).Offset(, 1) is still not working.
I tried replacing it immediately to Range (add1,add2) and it still didn't work.
I changed the line to hardcode, say Range ("B1","B10"), the script worked. I am puzzled...

[clark8529]

Finally I made it... LOL :rotlaugh:

I had realized my mistake there and corrected the script a little. It's the address I was missing from the Range(Selection.Offset(, 1), Selection.End(xlDown)).Offset(, 1).



Sub test2()

add01 = ActiveCell.address
ActiveCell.Offset(0, 1).Select
add1 = ActiveCell.address
ActiveCell.Value = 1
ActiveCell.Offset(1, 0).Select
add2 = ActiveCell.address
ActiveCell.Value = 2
Range(add01).Select

Range(add1, add2).autofill Destination:=Range(Selection.Offset(, 1).address, Selection.End(xlDown).Offset(, 1).address)

End Sub

[Paul_Hossler]

Hey Paul, Is that what "tells" autoFill to fill by a series of 1s?

Would it "count by twos" if B2's value was 3


Yes, it's the VBA equivalent of putting the cursor on the little square at lower right corner of B2 and double clicking when it turns into a 'cross'

18436



Option Explicit

Sub FillCells()
Dim r1 As Range, r2 As Range

Set r1 = Range("A1")
Set r2 = r1.End(xlDown)

Set r1 = r1.Offset(0, 1)
Set r2 = r2.Offset(0, 1)

r1.Value = 1
r1.Offset(1, 0).Value = 3

Range(r1, r1.Offset(1, 0)).AutoFill Destination:=Range(r1, r2)
End Sub

[Paul_Hossler]

Finally I made it... LOL :rotlaugh:

I had realized my mistake there and corrected the script a little. It's the address I was missing from the Range(Selection.Offset(, 1), Selection.End(xlDown)).Offset(, 1).




Glad you solved it, but I still think you took a round about way to get there, with all the .Select's, Selection's, Offset's, Address's, etc.



Option Explicit

Sub FillCells_1()
Dim r1 As Range, r2 As Range

Set r1 = ActiveCell
Set r2 = r1.End(xlDown)

Set r1 = r1.Offset(0, 1)
Set r2 = r2.Offset(0, 1)

r1.Value = 1
r1.Offset(1, 0).Value = 2

Range(r1, r1.Offset(1, 0)).AutoFill Destination:=Range(r1, r2)
End Sub

[Paul_Hossler]

Hey Paul,

Your suggested code didn't work if the first reference cell is not A1.


True, I followed your


Range("A1").Select

from your first post

[clark8529]

Paul and SamT, :)
Thanks all for helping anyway. It's working great.