VBA Autofill from active cell

[Hansulet]

I need a VBA to make an autofill from active cell.
I mention that I don't know the column letter.
It can be column A or column E or another column. It depends on running another macro.

 

[Joe4]

How do we know how far down to Autofill?
Should we be looking at the column to the left or to the right?
(Obviously, if you are trying to autofill column A, you won't be able to look to the left).

 

[Hansulet]

How do we know how far down to Autofill?
Should we be looking at the column to the left or to the right?
(Obviously, if you are trying to autofill column A, you won't be able to look to the left).

Yes, you are right, if it is column A, it is not possible.
I apologize for exemplifying wrongly.
But the idea is that in my database, the column is dynamic because it expands by several columns every month.

 

[Joe4]

OK, so can you answer my question of how you would like us to determine how far down to copy the formula?
And am I safe to assume that the "ActiveCell" will already have a formula in it to copy down?

Perhaps showing us some sample data and expected results would be helpful here.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Hansulet]

221

 

[Hansulet]

VALUE	NAME	SURNAME	SUM
214	NAME 1	SURNAME 1	212
	NAME 2	SURNAME 2	2121
	NAME 3	SURNAME 3	66
	NAME 4	SURNAME 4	221

 

[Hansulet]

test.xls
	A	B	C	D
1	VALUE	NAME	SURNAME	SUM
2	214	NAME 1	SURNAME 1	212
3		NAME 2	SURNAME 2	2121
4		NAME 3	SURNAME 3	66
5		NAME 4	SURNAME 4	221
Sheet1

Cell Formulas
Range	Formula
A2	A2	=D2+2

 

[Hansulet]

I want to make autofill in the VALUE Column. Mention that this column is dynamic because it expands by several columns every month
It can be column E or column M or another column. It depends on running another macro.

 

[Alex Blakenburg]

Would something like this work for you ?

    Dim rngFill As Range
    Dim rowLast As Long, colFill As Long
    
    Set rngFill = ActiveCell
    colFill = rngFill.Column
    
    If colFill = 1 Then
        rowLast = Cells(Rows.Count, colFill + 1).End(xlUp).Row   ' Look to the right if column A
    Else
         rowLast = Cells(Rows.Count, colFill - 1).End(xlUp).Row  ' Look to the left
    End If
    
    If rowLast <= rngFill.Row Then Exit Sub
    
    Set rngFill = Range(rngFill, Cells(rowLast, colFill))
    rngFill.FillDown

End Sub

 

[Hansulet]

YEEESS!!
Thank you, Alex Blakenburg