How to Apply Formula to Entire Column Using Excel VBA

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.

apply formula to entire column excel vba


Method 1 – Using the Range Formula in VBA

Steps:

  • Go to the Developer tab and select Visual Basic.

opening VBA to apply formula to entire column excel

  • Select Insert in the VBA window and click on Module.

inserting VBA module to apply formula to entire column excel

  • Insert the formula below in the new window:
Public Sub Range_Formula()
Range("C5:C10").Formula = "=B5*0.03"
End Sub

VBA code to apply formula to entire column excel

  • 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

VBA autofill to apply formula to entire column excel

  • 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 Entire Column Without Dragging in Excel


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


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

Get FREE Advanced Excel Exercises with Solutions!
Nazmul Hossain Shovon
Nazmul Hossain Shovon

Nazmul Hossain Shovon, a BUET graduate in Naval Architecture and Marine Engineering, embarked on his career with 8 months dedicated to the Exceldemy project's triumph. Transitioning into a Software Developer role, he specialized in web add-in development. At Exceldemy, he authored about 125 blog articles and solved many visitors’ problems, refining his writing skills and delving into Excel-related topics. With a primary passion for programming and software development, Shovon continually explores new horizons, fostering professional growth in his... Read Full Bio

2 Comments
  1. 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

    • Reply Lutfor Rahman Shimanto
      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:

      1. Press Alt+F11.
      2. Click on Insert followed by Module.
      3. Paste the following code into the module and save it:
        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

      4. Return to the sheet and select cell B2.
      5. Insert the following formula: =GETMeteorologicalWeekNo(A2)
      6. Drap the Fill Handle icon to copy the formula down.

      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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo