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]

OK, some more questions:

Do you actually have a worksheet named "Charts"? Also, in the Workbooks line(s) at the top, you should probably refer to the Charts.xls by its full name so:

dStartDate = Workbooks("Charts.xls").Names("StartDate").RefersToRange.Value

Which specific line does the debugger error out on? If it's in the loop, what's the value of nPoints when it errors out (if in the VBE in debug mode, you can hover your mouse pointer over nPoints in the code window, and it should tell you what the value of nPoints is).

Another thing is the loop doesn't currently increment the values as you are not using the loop counter i on the side of the value assignment. So, instead of:

Worksheets("Charts").Range("DataName" & nPoints).Value = dStartDate + nDivisor

you will probably want:

Worksheets("Charts").Range("DataName" & nPoints).Value = dStartDate + nDivisor * i

Another thought, as Glenn also raised, do you have sufficient named ranges if i is quite large? ie DataName1, DataName2,...., DataName150 or whatever your maximum is likely to be?

Hopefully, we can get to the bottom of this!