3 Tips for Writing Formulas with VBA Macros in Excel

In this video I share 3 tips for writing and creating Excel formulas with VBA macros.
You will learn:
1. How to use the Formula property of a range object to insert a formula into a cell. The formula is text that begins with the equals = sign and is wrapped in quotation marks.
2. How to use the macro recorder to create the VBA code for more complex formulas that contain special characters.
3. What R1C1 style formula notation is and how it works. R1C1 allows us to create relative references to cells (rows & columns), instead of hard coding cell addresses like A1. R1C1 can be used for both absolute and relative references, and also mixed references (absolute row & relative column).

Related videos:
00:00 Introduction
00:11 3 Tips for Writing Formulas with VBA Macros in Excel
00:35 The Formula Property
01:09 Use the Macro Recorder

[sim]

You are really the gem of a guy when it comes to describe things concerning excel or vba. Thanks a trillion for this video.

[BOWENSblog]

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

[kickilicoff]

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

[jlpayne]

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

[OttawaRocks]

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

[Bebicsek]

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

[Revlemmon]

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.

[Juanksv]

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

[suavesergio]

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

[randomCADstuff]

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

[yosef-yosef]

Very helpful and useful for me Excel Master

[fundip]

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 .

[augustodemelo]

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

[stuartmuscroft]

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

[shavkatpulatov]

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

[gokul]

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

[mobr.]

Very clear and informative video. Thank you.

[abhik]

At 1:11 I made up my mind that I will Like & download this video!

[microsecure]

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

[TheArifbmc]

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