3 Tips for Writing Formulas with VBA Macros in Excel

Sign up for our Excel webinar, times added weekly: www.excelcampus.com/blueprint...
In this video I share 3 tips for writing and creating Excel formulas with VBA macros.
Download the file: www.excelcampus.com/vba/writi...
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).
Register for my free upcoming webinar on getting started with macros & VBA: www.excelcampus.com/yt-vba-we...
Related videos:
2 Critical VBA Assumptions: • 2 Critical Excel VBA A...
Free webinar on learning macros & VBA: • Free Webinar on VBA Ma...
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

[@kickilicoff]

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

[@sim7717]

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

[@divinorodriguesdasilva5048]

Excellent video!

[@Bebicsek]

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

[@jlpayne052710]

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!

[@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.

[@Juanksv09]

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

[@randomCADstuff]

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

[@augustodemelo915]

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

[@suavesergio]

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

[@mobr.]

Very clear and informative video. Thank you.

[@olibolivar7223]

Just what I needed.Thank you sir! :D

[@rajeevdixit7019]

You are amazing tracher of vba

[@stuartmuscroft3753]

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

[@jgojiz]

Amazing explanation many thanks!

[@gokul8370]

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

[@smrenterprises589]

Thanks a lot . It's perfect

[@irynamusiiovska828]

Thanks for your video.

[@ChollyMc]

Well done! Thank you!!!

[@ExcelCampus]

You're welcome, Charles! 😀

[@dottied5596]

This helped so much! Thank u!!

[@abhi2k68]

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

[@strosa2406]

too useful .. thanks jon

[@Pankaj-Verma-]

Thanks for the kind help.

[@jgg518]

Very Helpful Video

[@kopobinang6928]

Thank You!

[@sasavienne]

Nice. Thanks...

[@fundip43]

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 .

[@hoggrobinson]

beauriful

[@shavkatpulatov5203]

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

[@debbie6085]

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.

[@avb6programminguser776]

Good for VBA programming and VB6 programming

[@paultc16]

Nice Video

[@magibis7519]

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

[@kumarrohit8538]

Superb

[@samsammito168]

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

[@FRANKWHITE1996]

thx

[@nadermounir8228]

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 ?

[@microsecure2612]

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

[@mithiladas2481]

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?

[@frankcornfield5915]

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

[@PhilipHubbe]

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?

[@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

[@flying_horse]

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 ?

[@abdulsafwan711]

How can I hide the formula and show only values

[@Dopeboyz789]

What if i dont wanna see the formula

[@prashant_karna]

Activecell.formular1c1="=sum(a18:r[-1]c)" Sir please check what is wrong with this formula

[@masonharper4610]

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

[@ElGordoFreeman]

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.