+ Reply to Thread
Results 1 to 4 of 4

Defing a named range

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    03-30-2010
    Location
    Manchester England
    MS-Off Ver
    Excel 2010
    Posts
    992

    Defing a named range

    I have to define 24 named ranges on a annual worksheet. The range names have to be deleted from last years sheet and installed on the new sheet for this year. The deletion code works but the following code for the new sheet fails with a compile error expected end of statement at line 10
    Sub NameRngs(NewYr)
     Dim myNamedRange As Range
      Dim myRangeName As String
      Set myWorksheet = "ManagementAccounts_" & NewYr
    x = 1
    For a = 0 To 240 Step 20
    
    10      Set myNamedRange = myWorksheet.Cells(1, 11 + a),.Cells(1, 19 + a))
                  myRangeName = "_Formulas" & x
          ThisWorkbook.Names.Add Name:=myRangeName, RefersTo:=myNamedRange
                        Set myNamedRange = myWorksheet.Range(.Cells(7, 11 + a), .Cells(67, 19 + a))
                  myRangeName = "_Results" & x
                x = x + 1
          ThisWorkbook.Names.Add Name:=myRangeName, RefersTo:=myNamedRange
    Next a
    End Sub
    I believe that if unspecified range has a workbook scope but the worksheet is essential too.
    Can someone show me where I am going wrong please
    John

  2. #2
    Forum Expert BMV's Avatar
    Join Date
    01-13-2020
    Location
    St-Petersburg, Russia
    MS-Off Ver
    2013-2016
    Posts
    1,329

    Re: Defing a named range

    Set myNamedRange = myWorksheet.Range(Cells(1, 11 + a),.Cells(1, 19 + a))

  3. #3
    Registered User
    Join Date
    10-05-2019
    Location
    Work: East Coast; Home: Texas, USA
    MS-Off Ver
    Mostly 2010 ( Windows 10 & 365 Insider Super Fast )
    Posts
    32

    Re: Defing a named range

    Set myNamedRange = myworksheet.Range(Cells(1, 11 + a), Cells(1, 19 + a))
    But this would be better
    myworksheet.Range(myworksheet.Cells(1, 11 + a), myworksheet.Cells(1, 19 + a))
    see: https://www.excelforum.com/excel-programming-vba-macros/1303728-contiguous-range-reference.html#post5266029

  4. #4
    Forum Contributor
    Join Date
    03-30-2010
    Location
    Manchester England
    MS-Off Ver
    Excel 2010
    Posts
    992

    Re: Defing a named range

    BMV and MollyBrennholz,
    I have realised, to my shame that I have not thanked you for your help. Your corrections to my code worked and have now been incorporated.
    So belatedly many thanks for setting me right
    John

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Listbox displaying named range B but Adding named range A to cell
    By ikkenieikke in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 02-05-2018, 02:27 PM
  2. [SOLVED] Compare cells to named range and create new sheets using related named range
    By drewship in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 12-10-2015, 03:35 PM
  3. [SOLVED] Assign named range to one cell depending on named range in another
    By x65140 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-22-2015, 11:04 AM
  4. [SOLVED] determining if cell is part of named range and what that named range is
    By stnkynts in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-16-2014, 07:56 PM
  5. Adding row to a named range-updating the named range address
    By kjsconv in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 08-12-2013, 11:22 PM
  6. Replies: 1
    Last Post: 02-27-2013, 12:00 AM
  7. [SOLVED] Determine what Named Range the Target Address is and return Named Range Name
    By jordan2322 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-18-2012, 10:49 PM

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