How to Set Value to a Named Range with Excel VBA (3 Methods)

In this article, I’ll show you how you can set value (s) to a named range with VBA in Excel. You’ll learn to set a single value to a named range, as well as a series of values.


Set Value to a Named Range with Excel VBA (Quick View)

Sub Set_Single_Value_to_a_Named_Range()

Worksheet_Name = "Sheet1"
Named_Range = "MyRange"
Value = 100

Worksheets(Worksheet_Name).Range(Named_Range) = Value

End Sub

VBA Code to Set Value to a Named Range in Excel


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


How to Set Value to a Named Range with Excel VBA (Step-by-Step Analysis)

Here we’ve got a named range called MyRange that contains cell B3:D10 of a worksheet called Sheet1 of a workbook.

Named Range Set Value to a Named Range with Excel VBA

Our objective today is to insert a value (s) to this named range with Excel VBA.


1. Set a Single Value to a Named Range with Excel VBA

First of all, we’ll learn to set a single value to all the cells of this named range. Let the value be 100.

I am showing you the step-by-step procedure to execute this.

⧪ Step 1: Inserting the Inputs (Worksheet Name, Named Range, and Value)

First of all, we’ve to enter the inputs to the code. The inputs are worksheet name (Sheet1), named range (MyRange), and value (100).

The lines of code to set these will be:

Worksheet_Name = "Sheet1"
Named_Range = "MyRange"
Value = 100

⧪ Step 2: Setting the Value to the Named Range

Next, we have to set the value to the named range.

Worksheets(Worksheet_Name).Range(Named_Range) = Value

So, the complete VBA code will be:

⧭ VBA Code:

Sub Set_Single_Value_to_a_Named_Range()

Worksheet_Name = "Sheet1"
Named_Range = "MyRange"
Value = 100

Worksheets(Worksheet_Name).Range(Named_Range) = Value

End Sub

VBA Code to Set Value to a Named Range in Excel

⧭ Output:

Run the code (Don’t forget to change the worksheet name, named range, and the value according to your need before running the code).

It’ll set 100 to all the cells of the named range MyRange of Sheet1.

Read More: How to Edit Defined Names in Excel (Step-by-Step Guideline)


2. Set a Series of Values to a Named Range with Excel VBA (First Row, then Column)

We’ve learned to set a single value to a named range. This time we’ll learn to set a series of values to a named range with VBA.

We’ll go row-wise within the range first, then column-wise.

Let’s set a series of squares of even numbers starting from the square of 8 in the named range MyRange of Sheet1.

That means the series of values will be: 64 (Square of 8), 100 (Square of 10), 144 (Square of 12), and so on.

⧪ Step 1: Inserting the Inputs (Worksheet Name, Named Range, Starting Value, Increment, and the Index)

First of all, we have to insert the inputs into the code. They are: worksheet name (Sheet1), named range (MyRange), starting value (8), increment (2), index (2).

Worksheet_Name = "Sheet1"
Named_Range = "MyRange"
Starting_Value = 8
Increment = 2
Index = 2

⧪ Step 2: Iterating through a For-Loop to Set Values to Each Cell of the Named Range

Next, we’ll iterate through a for-loop to set values to each cell of the named range..

For i = 1 To Worksheets(Worksheet_Name).Range(Named_Range).Rows.Count
    For j = 1 To Worksheets(Worksheet_Name).Range(Named_Range).Columns.Count
        Worksheets(Worksheet_Name).Range(Named_Range).Cells(i, j) = Starting_Value ^ Index
        Starting_Value = Starting_Value + Increment
    Next j
Next i

Therefore, the complete VBA code will be:

⧭ VBA Code:

Sub Set_Multiple_Values_to_a_Named_Range_1()

Worksheet_Name = "Sheet1"
Named_Range = "MyRange"
Starting_Value = 8
Increment = 2
Index = 2

For i = 1 To Worksheets(Worksheet_Name).Range(Named_Range).Rows.Count
    For j = 1 To Worksheets(Worksheet_Name).Range(Named_Range).Columns.Count
        Worksheets(Worksheet_Name).Range(Named_Range).Cells(i, j) = Starting_Value ^ Index
        Starting_Value = Starting_Value + Increment
    Next j
Next i
End Sub

VBA Code Set Value to a Named Range with Excel VBA

⧭ Output:

Run the code (Again don’t forget to change the worksheet name, named range, starting value, increment, and index according to your need before running the code).

It’ll set a series of squares of even numbers starting from the square of 8 (64) in the named range, first row-wise and then column-wise.

Output Set Value to a Named Range with Excel VBA

Read More: How to Name a Column in Excel (3 Easy and Effective Ways)


3. Set a Series of Values to a Named Range with Excel VBA (First Column, then Row)

We’ll again insert a series of squares of even numbers starting from the square of 8 to the named range.

But this time we’ll go column-wise first, then row-wise.

The code is the same as the previous one. Only during the iterations, first iterate through the columns, then through the rows.

The VBA code will be:

⧭ VBA Code:

Sub Set_Multiple_Values_to_a_Named_Range_2()

Worksheet_Name = "Sheet1"
Named_Range = "MyRange"
Starting_Value = 8
Increment = 2
Index = 2

For i = 1 To Worksheets(Worksheet_Name).Range(Named_Range).Columns.Count
    For j = 1 To Worksheets(Worksheet_Name).Range(Named_Range).Rows.Count
        Worksheets(Worksheet_Name).Range(Named_Range).Cells(j, i) = Starting_Value ^ Index
        Starting_Value = Starting_Value + Increment
    Next j
Next i

End Sub

⧭ Output:

Run the code (Obviously with changing the inputs according to your need). It’ll set the series of required values to the given named range.

Read More: How to Edit Name Box in Excel (Edit, Change Range and Delete)


Things to Remember

In this article, I’ve focused on setting values to a named range only. If you want to know how to create a named range in Excel, you can visit this link.

Also while working with VBA in Excel, you needn’t save any file to run a VBA code. You can see the output of any code without saving the file. But in case you want to save the file for future use, you must save the file as Excel Macro-Enabled Workbook (*.xlsm).


Conclusion

So, these are the ways to set value (s) to a named range with VBA in Excel. Do you have any questions? Feel free to ask us. And don’t forget to visit our site ExcelDemy for more posts and updates.


Related Articles

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo