How to Apply Formula to Entire Column Using Excel VBA

In this tutorial, I am going to show you 3 simple methods on how to apply a formula to an entire column using Excel VBA. These methods will save a large portion of your time. Also throughout this tutorial, you will learn some valuable Excel VBA commands and tools which will be useful in other Excel-related tasks.

How to Apply Formula to Entire Column Using Excel VBA: 3 Simple Methods

We have taken a concise dataset to explain the steps clearly. This dataset has 2 columns and 6 rows. The two columns are Product Price and an assumed Tax at 3%. Also, note that we have formatted all the data cells in the Accounting format. Besides, we will save the Excel workbook as a Macro-Enabled file format as we will be running VBA codes in all the methods.

1. Using Range Formula in VBA

We can define the entire range of cells within a column in Excel VBA where we want to apply a formula. Let us see how we can do this.

Steps:

First, go to the Developer tab and select Visual Basic.

Now, select Insert in the VBA window and click on Module.

Next, type in the formula below in the new window:

Public Sub Range_Formula()
Range("C5:C10").Formula = "=B5*0.03"
End Sub

Then, open the macro from the Developer tab by clicking on Macro.
Now, in the Macro window, select the Range Formula Macro and click Run.

Immediately, this will insert the formula into all the cells of the range that we specified.

2. Applying AutoFill in VBA

AutoFill is a great command in Excel VBA to work with data that are repetitive. We can easily use this by specifying the formula to fill with and the range of cells that we want to fill. Follow the steps below to achieve this.

Steps:

To begin with, double-click on cell C5 and insert the following formula, and press Enter.

=B5*0.03

Now, open a VBA module as before and insert the following macro code:

Public Sub AutoFill()
Range("C5").AutoFill Range("C5:C10")
End Sub

Finally, run this macro and you should get the formula for the entire range of cells.

3. Utilizing Range Value in Excel VBA

We can use the Value function in the Range command to apply a formula to an entire column inside Excel VBA. For this, type the following code and then do the same steps as before.

Public Sub Range_Value()
Range("C5:C10").Value = "=B5*0.03"
End Sub

Apply Formula to Entire Column Without Dragging in Excel

Although VBA might be the best option when working with large datasets, there is also a very handy tool to quickly fill a column with a certain formula. This can be done with the Fill Handle tool. Let us see how we can apply a Formula to an entire column without dragging in Excel.

Steps:

For this, type the following formula in cell C5:

=B5*0.03

Then, double-click on the Fill Handle icon as in the image below and this should do the job.

Apply Formula to Entire Column Using Keyboard Shortcut in Excel

If you are someone who likes to work more with the keyboard, then we have a cool shortcut that you can use to apply a formula to an entire column. Follow the steps below.

Steps:

To begin with, enter the previous formula in cell C5.
In addition, select all the cells from C5 to C10.
Finally, press Ctrl+D and this will fill the entire column with the formula.

Download Practice Workbook

You can download the practice workbook from here.

Apply Formula to Entire Column.xlsm

Conclusion

I hope that you were able to apply the methods that I showed in this tutorial on how to apply a formula to an entire column using Excel VBA. As you can see, there are quite a few ways to achieve this. So wisely choose the method that suits your situation best. If you get stuck in any of the steps, I recommend going through them a few times to clear up any confusion. If you have any queries, please let me know in the comments.

<< Go Back to How to Create Excel Formulas | Excel Formulas | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

Function GETMeteorologicalWeekNo(inputDate As Date) As Long Dim dict As Object Set dict = CreateObject("Scripting.Dictionary") dict.Add 1, "01 Jan – 07 Jan" dict.Add 2, "08 Jan – 14 Jan" dict.Add 3, "15 Jan – 21 Jan" dict.Add 4, "22 Jan – 28 Jan" dict.Add 5, "29 Jan – 04 Feb" dict.Add 6, "05 Feb – 11 Feb" dict.Add 7, "12 Feb – 18 Feb" dict.Add 8, "19 Feb – 25 Feb" dict.Add 9, "26 Feb – 04 Mar" dict.Add 10, "05 Mar – 11 Mar" dict.Add 11, "12 Mar – 18 Mar" dict.Add 12, "19 Mar – 25 Mar" dict.Add 13, "26 Mar – 01 Apr" dict.Add 14, "02 Apr – 08 Apr" dict.Add 15, "09 Apr – 15 Apr" dict.Add 16, "16 Apr – 22 Apr" dict.Add 17, "23 Apr – 29 Apr" dict.Add 18, "30 Apr – 06 May" dict.Add 19, "07 May – 13 May" dict.Add 20, "14 May – 20 May" dict.Add 21, "21 May – 27 May" dict.Add 22, "28 May – 03 Jun" dict.Add 23, "04 Jun – 10 Jun" dict.Add 24, "11 Jun – 17 Jun" dict.Add 25, "18 Jun – 24 Jun" dict.Add 26, "25 Jun – 01 Jul" dict.Add 27, "02 Jul – 08 Jul" dict.Add 28, "09 Jul – 15 Jul" dict.Add 29, "16 Jul – 22 Jul" dict.Add 30, "23 Jul – 29 Jul" dict.Add 31, "30 Jul – 05 Aug" dict.Add 32, "06 Aug – 12 Aug" dict.Add 33, "13 Aug – 19 Aug" dict.Add 34, "20 Aug – 26 Aug" dict.Add 35, "27 Aug – 02 Sep" dict.Add 36, "03 Sep – 09 Sep" dict.Add 37, "10 Sep – 16 Sep" dict.Add 38, "17 Sep – 23 Sep" dict.Add 39, "24 Sep – 30 Sep" dict.Add 40, "01 Oct – 07 Oct" dict.Add 41, "08 Oct – 14 Oct" dict.Add 42, "15 Oct – 21 Oct" dict.Add 43, "22 Oct – 28 Oct" dict.Add 44, "29 Oct – 04 Nov" dict.Add 45, "05 Nov – 11 Nov" dict.Add 46, "12 Nov – 18 Nov" dict.Add 47, "19 Nov – 25 Nov" dict.Add 48, "26 Nov – 02 Dec" dict.Add 49, "03 Dec – 09 Dec" dict.Add 50, "10 Dec – 16 Dec" dict.Add 51, "17 Dec – 23 Dec" dict.Add 52, "24 Dec – 31 Dec" Dim yearValue As Integer Dim startDate As Date Dim endDate As Date yearValue = year(inputDate) Dim key As Variant For Each key In dict.Keys Dim dates() As String dates = Split(dict(key), "–") startDate = DateValue(Trim(dates(0)) & " " & yearValue) endDate = DateValue(Trim(dates(1)) & " " & yearValue) If inputDate >= startDate And inputDate <= endDate Then GETMeteorologicalWeekNo = key Exit Function End If Next key GETMeteorologicalWeekNo = 0 End Function

2 Comments

Bharti Mar 30, 2024 at 11:47 PM

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

Lutfor Rahman Shimanto Mar 31, 2024 at 2:26 PM

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: