VBA For Next question

[dcanham]

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

 

[Richard Schollar]

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:

...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 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]

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]

You are welcome

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]

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....

    ' 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]

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:

    ' 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]

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

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]

You originally said the defined names were DataPoint1 DataPoint2 etc, but your code says "DataName" & nPoints.

 

[dcanham]

That was meant as just an example....the code clip is the actual subroutine.

 

[GlennUK]

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.