Results 1 to 5 of 5

Errors occuring when creating a set of named ranges

Threaded View

  1. #1
    Registered User
    Join Date
    06-03-2009
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2003, Excel 2007
    Posts
    59

    Errors occuring when creating a set of named ranges

    I'm trying to make my worksheet generate a bunch of named ranges whenever it is activated.

    However, I'm getting a "Not a valid name" error on the line in red. The value of r is a string, so I really don't see what the issue is. Another time, I got a object defined error, but I think I've declared everyhing too. That line is very similar to the line above, and tends to work once (the first time around the loop). After that, I get the errors I mentioned.

    Private Sub Worksheet_Activate()
    
        Set wb1 = ActiveWorkbook
        Set ws1 = wb1.Worksheets("Data")
        Set ws2 = wb1.Worksheets("Summary")
        Set ws3 = wb1.Worksheets("Cumulative Number of LDPs")
        Set ws4 = wb1.Worksheets("Setup")
        
        Dim found_proj As Range
        Dim end_row_p As Integer
        Dim end_row2 As Integer
        end_row2 = ws2.Cells(Rows.Count, 2).End(xlUp).Row
        
        'create named range for projects
        wb1.Names.Add Name:=("Proj_List"), _
        RefersTo:=ws4.Range(ws4.Range("AA4"), ws4.Range("AA4").End(xlDown))
        
        'create named range for subsystems in each project
        For Each r In ws4.Range("Proj_List")
            Set found_proj = ws2.Range("A:A").Find(What:=r, LookAt:=xlWhole)
            If Not found_proj Is Nothing Then
                If ws2.Range("A" & found_proj.Row + 1) <> "" Then
                    end_row_p = found_proj.Row
                ElseIf ws2.Range("A" & found_proj.Row).End(xlDown).Row > end_row2 Then
                    end_row_p = end_row2
                Else
                    end_row_p = ws2.Range("A" & found_proj.Row).End(xlDown).Row - 1
                End If
                wb1.Names.Add Name:=(r), _
                RefersTo:=ws2.Range(ws2.Range("B" & found_proj.Row), ws2.Range("B" & end_row_p))
            End If
        Next r
    
    End Sub
    If anyone could tell me what i'm doing wrong, I'd be greatful.
    Last edited by Kaigi; 07-10-2009 at 03:01 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1