Autofill Macro

[DonRamos]

Hi,

I have found various autofill macros for copying down a formula in one column for as many rows as have data in them in the column to the left. However I cannot for the life of me quite get it to function the way I want it to:

The cell I have my formula in lies in column C. It's location in column C will vary, but is always the last cell with data.

I assume I begin by finding this cell:

Please Login or Register  to view this content.

Next I want to copy the formula in that cell down column C for as long as data exists in the column to the left (column B)

This would be easy if it was always the same number of cells to autofill.

The thing is this is also a variable number each time I run the code. I have seen many forums discussing this problem but they all show how to solve the problem as if you are copying data from cell C2.

My problem is I start from the last cell with data in column C (variable) and finish at the last cell with data in column B (variable).

It's having two variables that is spoiling my party. Since I am a self taught novice on VBA I just can't quite figure out the correct syntax for this.

Thanks for any and all interest and help

[Leith Ross]

Hello DonRamos,

Welcome to the Forum!

This macro will fill column "C" down to the end of column "B", if the end of "B" is greater than "C".

Please Login or Register  to view this content.

Adding the Macro
1. Copy the macro above pressing the keys CTRL+C
2. Open your workbook
3. Press the keys ALT+F11 to open the Visual Basic Editor
4. Press the keys ALT+I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL+V
7. Make any custom changes to the macro if needed at this time.
8. Save the Macro by pressing the keys CTRL+S
9. Press the keys ALT+Q to exit the Editor, and return to Excel.

To Run the Macro...
To run the macro from Excel, open the workbook, and press ALT+F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

[DonRamos]

Hole in one!

Fantastic help and a great welcome to the forum. I have to say this is the first time I have actually had to ask a question on Excel because usually the answer can be worked out from reading answers to other similar questions, and making suitible adjustments to the code

Sadly after this one bugged me for over 5 hours I was forced to admit defeat lol.

Great help, and thanks again

[Lewis1]

Hi Leith,

For us more novice users of VBA - could you explain your code a little more. For example, I would like to perform a similar action as Don however.

My Date data is in column D and I have a VLOOKUP function which starts in Cell Q14 which I would like to autofill for all applicable dates.

Your help with this would be great!

Thanks!

Kind regards,
Lewis

[mudraker]

Please Login or Register  to view this content.

[Lewis1]

Checking out now - will come back shortly with success / fail.

[Lewis1]

I can get it to work,

But is there any fix if your columns start at different cells.

IE: one startes at A1 and the other at D12?

Thanks,
Lewis

Alternatively, if you could help me with the below that would also solve the problem.

I am using the below code to populate dates. At the moment, it starts populating from Cell D1, how do I get it to begin at cell D14?

Thanks,

[code]
Sub calendardays()

Application.ScreenUpdating = False

Dim st As Date, Fn As Date, c As Integer, Dt As Date
st = [A1]
Fn = [A2]
For Dt = st To Fn
If Weekday(Dt) > 1 And Weekday(Dt) < 7 Then
c = c + 1
Cells(c, "D") = Dt
End If

Next Dt

End Sub
[/end code]

[mudraker]

Lewis1

Your last reply breaks 2 Forum Rules

VBA/ Macro code not wrapped - Rule 3

Hijacking thread - Rule 2
Your 1st reply was ok as you only asked for an explanation of the code.

You need to wrap your code in this thread & also start a new thread

[Lewis1]

Cool - did not realise. Thanks.