We have a sample dataset of product prices and taxes to be applied to them. We have formatted all the data cells in the Accounting format. We saved the Excel workbook in the Macro-Enabled file format as we will be running VBA codes in all the methods.
Method 1 – Using the Range Formula in VBA
Steps:
- Go to the Developer tab and select Visual Basic.
- Select Insert in the VBA window and click on Module.
- Insert the formula below in the new window:
Public Sub Range_Formula()
Range("C5:C10").Formula = "=B5*0.03"
End Sub
- Save the file and open the macro from the Developer tab by clicking on Macro.
- In the Macro window, select the Range Formula macro and click Run.
- This will insert the formula into all the cells of the range that we specified.
Method 2 – Applying AutoFill in VBA
Steps:
- Select cell C5 and insert the following formula, then press Enter.
=B5*0.03
- Open a VBA module and insert the following macro code:
Public Sub AutoFill()
Range("C5").AutoFill Range("C5:C10")
End Sub
- Run this macro and you should get the formula for the entire range of cells.
Method 3 – Utilizing the Range Value in Excel VBA
- Use the following code and run the macro.
Public Sub Range_Value()
Range("C5:C10").Value = "=B5*0.03"
End Sub
Apply a Formula to the Entire Column Without Dragging in Excel
Steps:
- Use the following formula in cell C5:
=B5*0.03
- Double-click on the Fill Handle icon as in the image below.
Apply Formula to the Entire Column Using a Keyboard Shortcut
Steps:
- Enter a formula in cell C5.
- Select all the cells from C5 to C10.
- Press Ctrl + D.
Read More: How to Apply Same Formula to Multiple Cells in Excel
Download the Practice Workbook
Related Articles
- How to Use Multiple Excel Formulas in One Cell
- How to Apply a Formula to Multiple Sheets in Excel
- How to Exclude Zero Values with Formula in Excel
- How to Make FOR Loop in Excel Using Formula
<< Go Back to How to Create Excel Formulas | Excel Formulas | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
I want to write code in VBA for assign week number using specific dates.
If I have numbers of Dates (365/366) years…20, 35.etc. One column with dates and I want to generate new column with assign week number (1-52) using specific dates.
Please help to add Standard Meteorological week number in Excel weather file
Standard Meteorological Weeks
Week No. Dates Week No. Dates
1 01 Jan – 07 Jan 27 02 Jul –08 Jul
2 08 Jan – 14 Jan 28 09 Jul – 15 Jul
3 15 Jan – 21 Jan 29 16 Jul – 22 Jul
4 22 Jan – 28 Jan 30 23 Jul – 29 Jul
5 29 Jan – 04 Feb 31 30 Jul – 05 Aug
6 05 Feb – 11 Feb 32 06 Aug – 12 Aug
7 12 Feb – 18 Feb 33 13 Aug – 19 Aug
8 19 Feb – 25 Feb 34 20 Aug – 26 Aug
9* 26 Feb – 04 Mar 35 27 Aug – 02 Sep
10 05 Mar – 11 Mar 36 03 Sep – 09 Sep
11 12 Mar – 18 Mar 37 10 Sep – 16 Sep
12 19 Mar – 25 Mar 38 17 Sep – 23 Sep
13 26 Mar – 01 Apr 39 24 Sep – 30 Sep
14 02 Apr – 08 Apr 40 01 Oct – 07 Oct
15 09 Apr – 15 Apr 41 08 Oct – 14 Oct
16 16 Apr – 22 Apr 42 15 Oct – 21 Oct
17 23 Apr – 29 Apr 43 22 Oct – 28 Oct
18 30 Apr – 06 May 44 29 Oct – 04 Nov
19 07 May – 13 May 45 05 Nov – 11 Nov
20 14 May – 20 May 46 12 Nov – 18 Nov
21 21 May – 27 May 47 19 Nov – 25 Nov
22 28 May – 03 Jun 48 26 Nov – 02 Dec
23 04 Jun – 10 Jun 49 03 Dec – 09 Dec
24 11 Jun – 17 Jun 50 10 Dec – 16 Dec
25 18 Jun – 24 Jun 51 17 Dec – 23 Dec
26 25 Jun – 01 Jul 52** 24 Dec – 31 Dec
* Week No. 9 will be 8 days during leap year
** Week No. 52 will always have 8 days
Hello Bharti
Thanks for visiting our blog and posting an exciting comment. As requested, I have developed an Excel VBA User-defined function to get Meteorological Week no.
Standard Meteorological Weeks:
Follow these steps:
I hope the Excel VBA User-defined function will fulfil your goal. I also have attached the solution workbook; good luck.
DOWNLOAD SOLUTION WORKBOOK
Regards
Lutfor Rahman Shimanto
Excel & VBA Developer
ExcelDemy