VBA For Next question

dcanham

Active Member
Joined
Jun 7, 2006
Messages
306
Ok, here is what I'm trying to do. I have a series of named cells that I want to write a value to. The names are: DataPoint1, DataPoint2, DataPoint3, DataPoint4, DataPoint5, ......etc all the way up to 30. I want to assign values to those cells in a for next loop. I'm not quite sure how to do that. I would appreciate if someone could sketch out an example of how to assign a value to a named cell using the loop iterator. Something like the loop below. Thanks for any help.

' setting up naming loop
Dim nPoints As Integer
For nPoints = 1 To nDataPoints
Worksheets("Charts").Cells("DataPoint" + str(nPoints)).Select
ActiveCell.Value = 1 + nDataPoint
Next nPoints
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Hi Dcanham

In your example, you are attempting to assign the same value to each named cell - is this what you want? The value is 1+nDataPoint (in the loop, nDataPoint doesn't change). If so, you could do it like:

Code:
...snippet...
for nPoints = 1 to nDataPoint
   Worksheets("Charts").Range("DataPoint" & nPoints).Value = 1 + nDataPoint
Next nPoints

This does assume, however, that you have assigned a value to nDataPoint previously in your code. However, should you wish to have incrementing values in the cells, then you just need to replace nDataPoint with nPoints:

Code:
...code snippet...
for nPoints = 1 to nDataPoint
   Worksheets("Charts").Range("DataPoint" & nPoints).Value = 1 + nPoints
Next nPoints

Again this assumes that you have assigned a avlue to nDataPoint previously in your code (otherwise, the for loop will have no upper boundary).

Note that by dimming nPoints as an integer, you can't have more than 32,768 iterations of the loop (as that's the maximum number that an int in VBA can hold) - you may wish to consider defining nPoints as a Long instead (this can hold over 4 million).

Also, I am presuming that you have already defined your named ranges either previously in code, or in the Excel sheet.

Hope this helps!

Richard
 

dcanham

Active Member
Joined
Jun 7, 2006
Messages
306
Well that was intended just as an example, but would work just fine for what I need to do. I didn't know you could use the '&' to create a name like that. Thanks for the excellant info.
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
You are welcome :biggrin:

You can use the string concatenation operator within the Range argument because Range expects a string (or an object if using Cells(x,y) as the argument) - so you can build the string as you would anywhere else in your code.

Richard
 

dcanham

Active Member
Joined
Jun 7, 2006
Messages
306

ADVERTISEMENT

ok I'm getting 'Run-time Error 9 Subscript out of range' error on the code below. The purpose of the code is to place a date value in a varying number of named cells. Named "DataName1, DataName2, DataName3, ....etc'. I've tried putting in 'DataName1' directly into the Range() statement, same result. Yes the cell is there on Charts.xls and is named 'DataName1'. The values are good at the point where it goes into the 'Worksheets' statement. Any thoughts on why this error is getting thrown? Thanks....


Code:
    ' determining divisor
    If (sIncrement = "Week") Then
        nDivisor = 7
    ElseIf (sIncrement = "Month") Then
        nDivisor = 30
    Else
        nDivisor = 1
    End If
    
    ' determining the total number of days in the date range
    nTotalDays = dEndDate - dStartDate
    
    ' determining number of datapoints
    nDataPoints = (nTotalDays / nDivisor)
    
    ' setting up naming loop
    Dim nPoints As Integer
    For nPoints = 1 To nDataPoints
        Worksheets("Charts").Range("DataName" & nPoints).Value = dStartDate + nDivisor
    Next nPoints
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Hi

I suspect that it could be because nDataPoints is being passed into the loop as a decimal rather than an integer value (ie not a whole number). I must admit that I can't replicate this error so I could be barking up the wrong tree here, but have you explicitly stated the variable type of nTotalDays and dEndDate and dStartDate? They should probably be Long variable type. Also, use the following amended code:

Code:
    ' determining divisor 
    If (sIncrement = "Week") Then 
        nDivisor = 7 
    ElseIf (sIncrement = "Month") Then 
        nDivisor = 30 
    Else 
        nDivisor = 1 
    End If 
    
    ' determining the total number of days in the date range 
    nTotalDays = dEndDate - dStartDate 
    
    ' determining number of datapoints 
    nDataPoints = (nTotalDays \ nDivisor) 
    
    ' setting up naming loop 
    Dim nPoints As Integer 
    For nPoints = 1 To nDataPoints 
        Worksheets("Charts").Range("DataName" & nPoints).Value = dStartDate + nDivisor 
    Next nPoints

The only change is in the nDataPoints = line where I have changed the divisor operator to the integer division operator.

Give it a try and come back if it's no help.

Richard
 

dcanham

Active Member
Joined
Jun 7, 2006
Messages
306

ADVERTISEMENT

Didn't seem to make a difference. I even tried forcing that to an Integer, it didn't work either. I've included the whole subroutine this time. Don't mind the acerbic message boxes :p


Code:
Private Sub CreateSeriesNamesButton_Click()
    ' initializing variables
    Dim dStartDate As Date
    Dim dEndDate As Date
    Dim sIncrement As String
    Dim nDivisor As Integer
    Dim nTotalDays As Integer
    Dim nDataPoints As Integer
        
    ' retreiving values
    dStartDate = Workbooks("Charts").Names("StartDate").RefersToRange.Value
    dEndDate = Workbooks("Charts").Names("EndDate").RefersToRange.Value
    sIncrement = IncrementComboBox.Value
    
    ' testing to make sure start date is before end date
    If (dStartDate > dEndDate) Then
        ' start date must be before end date
        MsgBox "The End Date must be after the Start Date", vbExclamation, "That was Dumb!!"
        Exit Sub
    End If
    
    ' testing minimum date range
    If (dStartDate < "05/01/2006" Or dEndDate < "05/01/2006") Then
        ' no sales numbers before that date
        MsgBox "There are no sales numbers available to this system prior to 05/01/2006", vbExclamation, "I'm just too lazy"
        Exit Sub
    End If

    ' testing maximum date range
    If (dEndDate > Now() Or dStartDate > Now()) Then
        ' no sales numbers for the future
        MsgBox "I lost my crystal ball....I cannot currently predict the future!", vbExclamation, "Futurama??"
        Exit Sub
    End If
    
    ' determining divisor
    If (sIncrement = "Week") Then
        nDivisor = 7
    ElseIf (sIncrement = "Month") Then
        nDivisor = 30
    Else
        nDivisor = 1
    End If
    
    ' determining the total number of days in the date range
    nTotalDays = dEndDate - dStartDate
    
    ' determining number of datapoints
    nDataPoints = Int(nTotalDays \ nDivisor)
    
    ' setting up naming loop
    Dim nPoints As Integer
    For nPoints = 1 To nDataPoints
        Worksheets("Charts").Range("DataName" & nPoints).Value = dStartDate + nDivisor
    Next nPoints
    


End Sub
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
You originally said the defined names were DataPoint1 DataPoint2 etc, but your code says "DataName" & nPoints.
 

dcanham

Active Member
Joined
Jun 7, 2006
Messages
306
That was meant as just an example....the code clip is the actual subroutine.
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
Yes, I get that the code clip is the actual subroutine, but wanted to check that you actually have defined names DataName1 DataName2 etc as names in the workbook.
 

Forum statistics

Threads
1,136,267
Messages
5,674,729
Members
419,523
Latest member
Urnovio

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top