3 Tips for Writing Formulas with VBA Macros in Excel

Very helpful and useful for me Excel Master

my formula is only to find cell position using match formula. then i can use it to define where i will paste a value from B7 to H*
Range("B7").Select
Application.CutCopyMode = False
Selection.Copy
cell= .formula = "=MATCH(A7;$G:$G;0)"
Range("H" & cell).Select
ActiveSheet.Paste
but this not working.
please advise

Well done! Thank you!!!

Excellent video!

Activecell.formular1c1="=sum(a18:r[-1]c)"

Sir please check what is wrong with this formula

I have been searching around internet the whole day, and this video explains it all! Thank you!

Does anyone know how to insert a formula into a vertical range? As the example from above with the range B10 to B30

it was great! full desription to clear, thanks a lot, but I'd like also to kwon how to replace the "spread sheetname " with variables
thanks in advance

SO GOOD! Helped me lot! Thumbs up and Subbed as well.

I was trying over and over to find a re-record function of marcos . Specially when I don’t know how to write formulas in excel well let alone vba. so to use a second writing macro to record the steps to copy into a macros vba is really straight forward .

Thanks a lot . It's perfect

Excel adds "@" in front of the formula and it does not work. A different version of excel?

Thank You!

Hey Jon, I am trying to insert a formula that is giving me a compile error. (expected end of statement)
formula = "=CONCATENATE( "BILL OF MATERIAL:" , Mid(CELL( "Filename", A1), Find( "]", CELL( "Filename", A1)) + 1, 255))"
Any Ideas?

You are amazing tracher of vba

This helped so much! Thank u!!

Excellent!!!
It is exactly what I was looking for!!!
Thank you so much!!!

The last time was exactly what I was looking for and very well explained! Thank-you!!

Thank you John, for sharing this kind of tips with us. They´re very useful!

Thank you John for the VBA tips. I wonder how to write a formula using VBA where a sheet name is used and this sheet can change at anytime. So say Range("A1").Formula = "='Product DataBase'!D2". Product database is the sheet name but what if someone changes the sheet name? How can this be made dynamic ? Any idea ?

Jon, I really appreciate your videos they are amazing. Too many times I find that excel videos are all just veiled sales pitches. I also appreciate that they are easy to follow and not too fast Thanks

too useful .. thanks jon

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'Sub Myautofill()

If Range("A1") <> "" Then

Range("A1").Select
Selection.AutoFill _
Destination:=Range("A1:A5"), Type:=xlFillDefault
'End With
'Range("A1").Formula = "=IF(AND(F5="",G5="",H5=""),"",(I4+F5-G5-H5))"
End If
'End With
End Sub
pls help me on this

Nice Video

Hi , I am trying to write a formula that uses a range ( it's a countif formula )
Thing is I don't want to define the range myself .
But even if I have it defined by a variable set as a range , it wont work
Any tips ?

wow...GENIOUS!!! I am truly grateful, thank you

learning how to do some VBA is actually a good way to climb the corporate ladder in non-technology roles. Thanks for coaching people online and supporting productive talent. Technology is getting better these days. Good thing VBA will never be replaced in the next 20 years. It can assist in administrative work, repetitive work, improve critical thinking skills(like academic, and application) and saves time

Excellent video. I was absolutely hunting for a formula creator within a VBA environment and this does the trick!

Thank you for this video, it was a godsend!! I have huge formulas that I needed to write with VBA and had no idea how to do it until I watched this video.

Superb

This video with the R1C1 formula setting just saved my night :-) Thanks!

Thanks for the kind help.

How can I hide the formula and show only values

Just what I needed.Thank you sir! :D

Very Helpful Video

There is a third condition of the formula property, the formula name must be in English with commas as list separators. If you have a spanish Excel and you want to use the local name, SUMA instead of SUM, you have to use the FormulaLocal property. Obviously, if you want a more versatil code, use the Formula property.

Hi Jon, Thank you for another one of your great videos. I've just tried to sign up to the 7 Steps to Getting Started with Macros and VBA, but when I entered the time zone of Perth Australia, it prompted for a date but didn't allow one to be entered. Can you please help me with this as I'm keen to view the video? Thank you.

Sir pls giv me ur number

What if i dont wanna see the formula

my sheet1 B10 value is 298263 after adding formula"=sum(B4:B9)" i want to add a formula in B10 with existing value "298263" Example: Range("B10").Formula = "=SUM(B4:B9)+298263" Every time this formula change the existing value which is exist in B10 Cell. How can i do that?

Hi Jon,
I'm trying to use variables with the formula property can you share an example?

thank you very much for this video very helpful ....
i have a question please:
1- i have this formula: =IF(Sheet1!$B$15=Sheet4!B8,Sheet1!$B$14,"£0.00") and it works fine with your video tutorial
but the cell does not show the sign (£)
2- how to tell Macro to move to the next cell with the same formula
Many thanks

Nice. Thanks...

PLS GIVE MI CODE FOE
333-333-333
DAS IN EVERY 3 WORD IN COLUM

Excellent, clear explanations and pristine video and audio. Thanks!

Good for VBA programming and VB6 programming

Very clear and informative video. Thank you.

thx

this is really wonderful video. i have question. everyday data are not same how to do vlookup in vba dynamically.