Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    May 2014
    Posts
    8
    Thanks
    1
    Thanked 0 Times in 0 Posts

    How to loop a macro Mac Excel 2011

    Hi

    I currently have a macro which names a cell based on its neighbours value.

    i need to run the macro fro the cells from B1 to B10 in the sheet

    current code is as below

    what needs adding??

    Sub Name_Range()

    ThisWorkbook.Names.Add Name:=Worksheets("DEFSR").Range("B1"), _
    RefersTo:=Worksheets("DEFSR").Range("C1")

    End Sub

    Cheers

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    For instance:
    Code:
    Sub Name_Range()
    Dim n As Long
    For n = 1 To 10
    ThisWorkbook.Names.Add Name:=Worksheets("DEFSR").Range("B" & n), _
    RefersTo:=Worksheets("DEFSR").Range("C" & n)
    Next n
    
    End Sub
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    New Lounger
    Join Date
    May 2014
    Posts
    8
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Hi

    This results in a runtime error

    Would the fact its Mac Excel cause this?

  4. #4
    New Lounger
    Join Date
    May 2014
    Posts
    8
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Hi

    apologies false alarm this works perfectly thanks, error was the missing data in cell b1!!!

    Thanks for your help Rory

  5. #5
    New Lounger
    Join Date
    May 2014
    Posts
    8
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Next step in my macro requires me to copy the values from one named range to another as a kind of default procedure

    I am doing this long windily at the moment with

    Application.Goto Reference:=ActiveWorkbook.Names("SR_ADDRESS1").Nam e
    Selection.Value = ActiveWorkbook.Names("DEF_SR_ADDRESS1").Value

    I have a table with the values in so am looking at a macro to mass copy this list over

    Col A 1-10 has the named ranges listed i.e. SR_ADDRESS1

    Col B 1-10 has the default named ranges for the above named ranges listed i.e.DEF_SR_ADDRESS1

    and Col C 1-10 has the actual data value i would like to copy

    any thoughts??

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    So column A and column B have the names of the ranges in, but are not themselves the named ranges?
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    New Lounger
    Join Date
    May 2014
    Posts
    8
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thats correct, it is just a reference list of them. The SR_ADDRESS1 Range is on a data entry sheet elsewhere in the spreadsheet

    The DEF_SR_ADDRESS1 named range is adjacent to its name in COL C. i.e. B1= text only DEF_SR_ADDRESS1, C1 = Named Range with data to be copied

Posting Permissions

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