VBA Loop and Msgbox issue

[PaulWJ]

I have a sheet which has several different named arrays with different dates in them. I'm trying to write a macro to loop through an array (determined during the macro) and then show a message box with all the dates in it (on separate lines). There will likely be blank cells at the bottom of each array that I'd like to avoid including in the message box. The HolShowOut reference links to a cell in the sheet that has the Named Array name in it that I want to use as the range. (The named array can change, depending on other actions in the sheet)

I've borrowed code from a response on another website and modified it slightly. The issue appears to be the myCell entry at the start of the For Each loop. The debug shows it as equalling Nothing, and I get an 1004 error, Method 'Range' of Object_Worksheet failed. rng does show the correct named array (DeskBook)

Private Sub Worksheet_Calculate()



    Dim myCell As Range
    Dim rng As String
    Dim Msgstring As String
   
    Sheets("Background").Activate
    rng = Sheets("Background").Range("HolShowOut").Value
    

    For Each myCell In Range(rng)
          If myCell.Value <> 0 Then
            If Msgstring <> "" Then
               Msgstring = Msgstring & vbCr & myCell.Value
            End If
         End If
    Next myCell

End Sub

I can't put a copy of the sheet up unfortunately, local permission don't allow it.

 

[saboh12617]

Hello,

When working with named ranges in VBA, you have to take into account the sheets in which they are defined.

If i have my named range "rng1" in sheet1 and i write in VBA "myRng = sheet2.Range("rng1") i will raise an error. Keep in mind that writing "myRng = Range("rng1") is referring to the ActiveSheet, which might not be sheet1 (when you write For Each myCell In Range(rng)).

So either you know that your arrays are all in the same worksheet, and then i think if all the code until this row is correct, the simple solution below should work:

For Each myCell In Worksheet("my_sheet").Range(rng)



Otherwise, you might loop on the workbook's names, to find the corresponding range. Like so

Private Sub Worksheet_Calculate()
    Dim myCell As Range
    Dim adr As String
    Dim Msgstring As String
 
    adr = Sheets("Background").Range("HolShowOut").Value
 
    Dim n As Name, myArray As Range
    For each n in Thisworkbook.Names
      If Right(n.Name, Len(n.Name) - InStrRev(n.Name, "!")) = adr Then
        Set myArray = n.RefersToRange
        Exit For
      End If
    Next n
    For Each myCell In myArray
      If IsDate(myCell.Value) Then
        Msgstring = Msgstring & vbCr & myCell.Value
      End If
    Next myCell
  MsgBox Msgstring
End Sub

 

[PaulWJ]

Thanks (again) Saboh.

The named arrays were all in the same worksheet, so setting the rng to that sheet made the difference.

 

[saboh12617]

Glad to help, thanks for the feedback. Do not forget to close the topic.