Named Ranges from VBA

[negcx]

I have some named ranges that refer to 5 pieces of data organized into a row. For example, the name MyNamedRange might refer to $C$5:$C$10.

I am trying to loop through each column and get the values in MyNamedRange, then change corresponding values in a different named range. However, when I try to use Offset to access the subsequent columns of MyNamedRange, it doesn't work. It only gets the value of the first column right, the rest return <EMPTY>.

Sample code:

Please Login or Register  to view this content.

I started using .Range("A1") because I was getting errors before.

Is there a simpler way to do this (or simply a way that works)?

[shg]

The .Range("a1") is redundant; you can take it out. Otherwise, the code is fine, assuming you're looking for it to get the values in C5:G5.

If you want to see the values of the cells in myRange,

Please Login or Register  to view this content.

[negcx]

Sadly the code is not fine because it doesn't work. For some reason it's returning <EMPTY> rather than the appropriate values.

The reason I'm using an index is because I'm working in two different named ranges.

[shg]

Sadly the code is not fine because it doesn't work. For some reason it's returning <EMPTY> rather than the appropriate values.

You did see that it's returning the values in C5:G5, not C5:C10, right?

The reason I'm using an index is because I'm working in two different named ranges.

I don't know what that means.

[negcx]

The behavior I'm getting is so bizarre. My sheet has an area called Setup. These are cells that the user changes to setup the sheet. This contains a named range Setup, which includes a variety of values. When the setup is changed, comments that calculate sales forecast recommendations need to be changed. That is the purpose of this code.

When I change the setup, nothing seems to happen with the comments, even though SetupChanged() returns TRUE. When I step through it seems that the WBDays is never = 7 (this is a row of data indicating the number of days in that week, e.g. 5, 7 ,7, 7, 5).

Here is the code:

Please Login or Register  to view this content.

When I change one of my setup variables - the total Sales Forecast - I get a type mismatch on the if = 7 statement. I am confused.

[negcx]

You did see that it's returning the values in C5:G5, not C5:C10, right?

Yes, that was the intention.

I don't know what that means.

I'm getting values from one row and using them to modify another row, both of which are governed by my Index. Effectively I'm trying to access them like two arrays.

[shg]

Please Login or Register  to view this content.

as used must refer to a single-cell range.

[negcx]

That is a single cell range.. not having problems with that one

[negcx]

Please Login or Register  to view this content.

seems to be the culprit. It seems like this should work but it's simply not working.

[negcx]

I seem to have gotten it to (mostly) working by using .Cells instead of .Offset. Now my problem is that the routine accesses cells that are in the process of being updated. When these cells are being updated my routine simply clears the comments, and does not add new ones. When I change a Setup cell that does not cause the WBDays named range to be in the process of updating, the comments are added..

Is there a way to call my function after the worksheet has already recalculated?

Thanks for your help.

[negcx]

Created new subroutine, moved to Worksheet_Calculate. Solved-

thanks.