VBA Excel: fill formula till the last used range

Hello experts,

I would like to add the following procedure to my personal.xlsb

Context:

When I add a formula in a specific range:  In my example attached C1. I would like, instead of dragging manually the formula, to have a procedure that cover the following requirement:
1-Display inputbox that says "Select the cell in which is your formula
2-Fill formula till the last used range. Last used row number should take into account the last used range of the sheet.

I attached an example with my initial data: Input sheet and with the final result Final-Result sheet after I run the procedure.

If you have questions, please contact me.
LVL 1
LD16Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieAnalyst Assistant Commented:
No file attached I'm afraid.:)
0
LD16Author Commented:
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You may try something like this...
Sub FillFormulaDown()
Dim FormulaCell As Range
Dim lr As Long
lr = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
On Error Resume Next
Set FormulaCell = Application.InputBox("Please select the Formula Cell.", "Fill Down The Formula!", Type:=8)
On Error GoTo 0
If FormulaCell Is Nothing Then
    MsgBox "You didn't select any cell.", vbExclamation, "No Cell Was Selected!"
    Exit Sub
ElseIf Not FormulaCell.HasFormula Then
    MsgBox "The cell you selected doesn't contain any formula in it.", vbExclamation, "Cell Has No Formula"
    Exit Sub
End If
FormulaCell.AutoFill Range(FormulaCell, Cells(lr, FormulaCell.Column)), xlFillDefault
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

LD16Author Commented:
Tested and it works. Thank you again for your help!!!
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You'r welcome! Glad it worked as desired.
0
Rob HensonFinance AnalystCommented:
Subodh has provided you with what you asked for but are you aware of this trick for filling formulas down a column using the mouse without having to drag or select?

With the cell in which you have entered the formula hover the mouse cursor over the bottom right corner of the cell and it will change to a + symbol. Double click at that point and it will fill down the cell contents until it reaches the last occupied cell of a neighbouring column. So in your example, you have entered the formula in C1 and you have contiguous data down to row 21. Double clicking the bottom right of C1 will fill column C down to row 21.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.