CODEWITHSUNDEEP
excelvba
Nenne
Nenne

Reputation: 160

Name manager VBA

I'm running a code which basically loops through every sheet of the workbook (containing a huge number of sheets) and copies the text contained in some cells identified through the name manager. Basically, in every sheet there are 4 cells identified through the name manager as "Item_1","Item_2","Item_3","Item_4", as shown in the example. enter image description here

This is a version of the code

for each anyWS in thisworkbook.worksheets
name=anyWS.Name
num_row=2
for num_row=2 To 4
if workbooks(xx).sheets(name).range("Item_"& num_row).value <> "" then
'the code does something...
else
end if
next
next

In some sheets, though, I have also a fifth item, so that I defined it "Item_5" through the name manager for that sheet. I edited this part of the above code: for num_row=2 To 4 into for num_row=2 To 5, but it fails whenever a sheet doesn't have "Item_5" defined through the name manager, and the code displays an error: the object workbooks(xx).sheets(name).range("Item_"& num_row).value does not exist when num_row=5. I tried the following:

  1. define automatically "Item_5" through name manager for every sheet in the wb, but it doesn't work.
  2. insert a statement On error goto where the code fails, but unsuccessfully, since when the loop is executed is "stuck" in the error. The code shouldn't stop. Simply, when it doesn't find the range named "Item_5", it should exit the for loop and go to the following sheet.
  3. add a condition like If not (workbooks(xx).sheets(name).range("Item_"& num_row).value) is nothing then, to identify the cases when that name doesn't exist, but it doesn't work.

The third option seems the better to me, so I ask you if there is a way to identify the non-existing object defined through the name manager, in order to prevent that error.

Upvotes: 1

Views: 1651

Answers (1)

VBasic2008
VBasic2008

Reputation: 54807

Loop Through Named Cell Ranges

Option Explicit

Sub LoopWorksheets()

    Dim ws As Worksheet
    Dim Cell As Range
    Dim n As Long
    
    For Each ws In ThisWorkbook.Worksheets
        For n = 1 To 5
            On Error Resume Next
                Set Cell = ws.Range("Item_" & n)
            On Error GoTo 0
            If Not Cell Is Nothing Then ' named cell exists
                If Len(CStr(Cell.Value)) > 0 Then ' named cell is not blank
                    'the code does something...
                    Debug.Print "The value of cell '" & Cell.Address(0, 0) _
                        & "' ('Item_" & n & "') in worksheet '" _
                        & ws.Name & "' is equal to '" & CStr(Cell.Value) & "'."
                Else ' named cell is blank
                    Debug.Print "The cell '" & Cell.Address(0, 0) _
                        & "' ('Item_" & n & "') in worksheet '" _
                        & ws.Name & "' is blank."
                End If
                Set Cell = Nothing
            Else ' named cell doesn't exist
                Debug.Print "The named cell '" & "Item_" & n _
                    & "' was not found in worksheet '" & ws.Name & "'."
            End If
        Next n
    Next ws
    
    MsgBox "Looped through worksheets.", vbInformation
    
End Sub

Edit

  • You could use a function and do something like the following (simplified).
Sub LoopWorksheetsUsingFunction()

    Dim ws As Worksheet
    Dim n As Long
    Dim RangeName As String
    
    For Each ws In ThisWorkbook.Worksheets
        For n = 1 To 5
            RangeName = "Item_" & n
            If NamedRangeExists(ws, RangeName) Then
                If ws.Range(RangeName).Value <> "" Then
                    ' do something
                End If
            End If
        Next n
    Next ws
    
End Sub

Function NamedRangeExists( _
    ByVal ws As Worksheet, _
    ByVal RangeName As String) _
As Boolean
    Dim rg As Range
    On Error Resume Next
        Set rg = ws.Range(RangeName)
    On Error GoTo 0
    NamedRangeExists = Not rg Is Nothing
End Function

Upvotes: 2

Related Questions