Named Ranges from VBA

**negcx** · 12-30-2008, 05:37 PM

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:

For ColumnIndex = 0 to 5
     MsgBox wksMyWorksheet.Range("MyNamedRange").Range("A1").Offset(0, ColumnIndex).Value

Next ColumnIndex

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** · 12-30-2008, 05:55 PM

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,

    Dim cell As Range
    Dim wks As Worksheet

    Set wks = some worksheet where myRange lives
    
    For Each cell In wks.Range("myRange")
        MsgBox cell.Value
    Next cell

**negcx** · 12-30-2008, 06:17 PM

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:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim WeeklySalesForecast As Double
    Dim WeekIndex As Integer
    Dim RecommendedSales As Double
    Dim RecommendedSalesString As String
    
    Dim wks As Worksheet
    Dim cell As Range
    
    Set wks = wksMonth
    
    ' Sales Forecast changed
    If SetupChanged(Target) Then
        ' Fill in the weekly sales forecast recommendation comments
        
        ' Clear all the comments
        For Each cell In wks.Range("WBSalesForecast")
            cell.ClearComments
        Next cell
        
        ' Fill in comments for full weeks
        For WeekIndex = 0 To constWeekCount - 1
            If wks.Range("WBDays").Offset(0, WeekIndex).Value = 7 Then
                RecommendedSales = 7 / _
                    wks.Range("WBDays").Offset(0, constTotalDaysColumn).Value _
                    * wks.Range("SetupSalesForecast").Value
                RecommendedSalesString = "Recommended: " & vbCrLf & Format(RecommendedSales, "Currency")

                wks.Range("WBSalesForecast").Offset(0, WeekIndex).AddComment RecommendedSalesString
            End If
        Next WeekIndex
    End If
End Sub

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.

**shg** · 12-30-2008, 06:21 PM

wks.Range("SetupSalesForecast")

as used must refer to a single-cell range.

**negcx** · 12-30-2008, 06:10 PM

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** · 12-30-2008, 06:12 PM

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** · 12-30-2008, 06:20 PM

Originally Posted by shg

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.

**negcx** · 12-30-2008, 06:28 PM

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

**negcx** · 12-30-2008, 06:29 PM

wks.Range("WBDays").Offset(0, WeekIndex).Value

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

**negcx** · 12-30-2008, 06:40 PM

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.