VBA- A macro which Applies Formula down a column until last row

DesperateKid

New Member
Joined
Oct 14, 2014
Messages
5
Hi
I need a macro for VBA which applies a formula From cell I4 to the last I value for which Column A has data filled in.
The formula in the worksheet would be equivalent to "= $j3+1" In Cell I4 which is then dragged down(autofilled) until the last row in column I.

Thanks so much!!
 
I get a syntax error with the provided formula you gave me. Originally, the formula I used when entered manually in my summary sheet is =Indirect(A2&"!S63") - it looks up the sku number in A2 and locates the tabsheet and returns the value that is located in cell S63. Does that make sense? I am a beginner at VBA , so I'm not sure if the same formula would work in a Macro.
Sorry, my error, forgot to double the quote marks. Try:

Range("B2").Formula = "=INDIRECT("" '"" & A2&""'!S63"")"
 
Upvote 0

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Sorry, my error, forgot to double the quote marks. Try:

Range("B2").Formula = "=INDIRECT("" '"" & A2&""'!S63"")"


It worked!! :) never been so exited to run my first macro lol

I will definitely learn how to build macros on my own. Hopefully i will become a pro in a short amount of time.

We use a lot of them at work so learning VBA code is quite useful.

Thank you so much,
 
Upvote 0
Hello is it possible to add this formula in a bigger macro?
This is my macro now. I added the small macro to my bigger macro I need to enter formulas from my active cell and below.

Sub CreateNewSheet()
Dim i As Byte
sheet_name_to_create = InputBox("Vul project naam in", "Nieuw invul formulier")
For rep = 1 To (Worksheets.Count)
If LCase(Sheets(rep).Name) = LCase(sheet_name_to_create) Then
MsgBox "Het project bestaat al"
Exit Sub
End If

Next
Sheets("TEMPLATE").Copy After:=Sheets(1)

For i = 1 To 1
Sheets(ActiveSheet.Name).Name = sheet_name_to_create

Next i
Sheets("Calculatie").Activate
ActiveSheet.Cells(1, 1).Select
Do Until IsEmpty(activeCell) = True
activeCell.Offset(0, 1).Select
r = r + 1
Loop
activeCell.Value = sheet_name_to_create
activeCell.Offset(1, 0).Select
Range("activecell").Formula = "=$J3+1"
Range("activecell", "activecolumn" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
End Sub
 
Upvote 0
Code:
Sub FillFormula()
Range("I4").Formula = "=$J3+1"
Range("I4", "I" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
End Sub

Same as Ajandras, - signed in just to send my thanks: your code saved my sanity (not exaggerating, I've been fighting with this for two days and it's weekend here :cry:)...
I've had a nifty trick for it but for some reason it won't work in new excel (or maybe office with different language set up - though I don't think this would be the cause as VBA is always english anyway from what I see)...

Thank you so very much :)
 
Upvote 0
Same as Ajandras, - signed in just to send my thanks: your code saved my sanity (not exaggerating, I've been fighting with this for two days and it's weekend here :cry:)...
I've had a nifty trick for it but for some reason it won't work in new excel (or maybe office with different language set up - though I don't think this would be the cause as VBA is always english anyway from what I see)...

Thank you so very much :)
Glad this was helpful! Thank you for taking the time to let me know. :)
 
Upvote 0
Hi Joe, a few years on and anoher register specifically to say thanks for this script - got me out of a pickle !!

Neteixuser
 
Upvote 0
I don't know why but this isn't working for me :(

Code:
Sheets("MY SHEET1").Select
Range("A2").Formula = "=VLOOKUP(C[3],'MY SHEET2'!C:C[1],2,0)"
Range("A2", "A" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown

What am I doing wrong? :(
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top