How to define variable in loop?

dmadhup

Board Regular
Joined
Feb 21, 2018
Messages
146
Office Version
  1. 365
I have defined variable like this:

Code:
Dim sumColumn15, sumColumn16, sumColumn17, sumColumn18, sumColumn19 As Long
sumColumn15 = 0
sumColumn16 = 0
sumColumn17 = 0
sumColumn18 = 0
sumColumn19 = 0

and I want to optimize this:

Code:
For i=15 To 19
  Dim sumColumn & i = 0
Next i

But no success. Any help please
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,486
Office Version
  1. 365
Platform
  1. Windows
I don't think you can do that.
And your original code does not even do what you think it does.

This:
Code:
Dim sumColumn15, sumColumn16, sumColumn17, sumColumn18, sumColumn19 As Long
declares sumColumn19 as Long, and the rest are declared as Variant.
Each variable needs to be declared explicitly like this:
Code:
Dim sumColumn15 As Long, sumColumn16 As Long, sumColumn17 As Long, sumColumn18 As Long, sumColumn19 As Long
or
Code:
Dim sumColumn15 As Long
Dim sumColumn16 As Long
Dim sumColumn17 As Long
Dim sumColumn18 As Long
Dim sumColumn19 As Long

What you are looking to do might require Arrays.
See: https://excelmacromastery.com/excel-vba-array/
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,474
Office Version
  1. 365
Platform
  1. Windows
You could use an array like
Code:
   Dim sumColumn(15 To 19) As Long
As it's declared as long the values are automatically 0
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,955
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
You can't do that. Also Dim doesn't go there, it's only used to declare variables.

As you have it written, use arrays.

Code:
Sub test()
Dim x As Long, sumColumn(15 To 19) As Long
For x = 15 To 19
    sumColumn(x) = 0
Next
End Sub

Although dimming a variable as Long will default it to 0 anyway.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,109,247
Messages
5,527,625
Members
409,777
Latest member
jamilowella

This Week's Hot Topics

Top