0

This code was working just fine, but I did a bunch of other code that manipulates and reads the same area of the sheet and now this section does not work.

I have tried a bunch of stuff with syntax but none worked. It may be that I need to resize my array but since im setting it equal to a range I didnt think that I had to. Also It says the problem is the range but I dont know. I would rather not have to resize as its taking from a larger table whose line items will be dynamic but I can do that and make it dynamic if I need to. I did try deleting the range and renaming it and it did not work.

Private Sub UserForm_Initialize()

      Dim codes()
      Dim ws As Worksheet
        Set ws = Worksheets("Sheet1")
        codes = ws.Range("cCodes")         

         CostCode1.List = codes     ''these are combo boxes                        
         CostCode2.List = codes
         CostCode3.List = codes
         CostCode4.List = codes
         CostCode5.List = codes
         CostCode6.List = codes

'' ADD UNITS

End Sub
JvdV
  • 70,606
  • 8
  • 39
  • 70
Chris Lafky
  • 13
  • 5

2 Answers2

0

you don't need to declare the sheet for the named range.
named ranges are stored as an external address including the sheet's Name. 

codes = Range("cCodes")

should be sufficient.

horst
  • 781
  • 1
  • 6
  • 14
  • 1
    Unqualified `Range`, when used outside of a sheet's code module, always resolves to `ActiveSheet.Range`. It is [never advised](https://stackoverflow.com/q/17733541/11683) to use that. – GSerg Jun 25 '19 at 19:56
  • @ChrisLafky If this solved it for you, then your named range was on another sheet as opposed to `Sheet1`, and you should have used that sheet instead to qualify the `.Range` call. – GSerg Jun 25 '19 at 19:57
  • @GSerg how would you do this then? I need the reference to go to the table so I have to use some sort of range I think unless I can directly call the table columns – Chris Lafky Jun 26 '19 at 12:52
  • @ChrisLafky You had `Set ws = Worksheets("Sheet1")` and `ws.Range("cCodes")`. If removing the `ws.` fixed it for you, then it must have been some other sheet that has the `cCodes` range. So you would have `Set ws = Worksheets("Other sheet")`. – GSerg Jun 26 '19 at 12:55
  • It worked last night for some reason but will not work now. I checked the sheet its name is from sheet1 I will try deleting and recreating the range – Chris Lafky Jun 26 '19 at 12:57
  • by making it a workbook level name and getting rid of the ws. that fixed it – Chris Lafky Jun 26 '19 at 12:59
  • Now this other piece of code is having that same problem with error 1004 I must be doing something wrong with my named ranges - For Each desc In Range("Description") With calcs If Me.CostCode1.Value = desc.Value Then desc.Offset(, 22).Select – Chris Lafky Jun 26 '19 at 13:15
  • it is giving me an error because the range reads a value so the offset is not working – Chris Lafky Jun 26 '19 at 13:19
  • @GSerg If you use this with any other ActiveSheet, `?Range("Table1").Worksheet.Name` will return the sheet name of where the named range is. Obviously that's not the case for normal ranges, but with this particular case I agree with horst, unless I'm missing something obvious. – FAB Jun 26 '19 at 13:22
  • @FAB You are correct to a degree. `Range("Table1")` will succeed even when a wrong sheet is the `ActiveSheet`, provided that the name in question is a [workbook level name](https://stackoverflow.com/a/2176154/11683) (which e.g. Table's names are). However this only pushes the problem one step further: instead of giving error 1004 when a wrong sheet is active, it will give error 1004 when a wrong workbook is active (or worse, will pick up the Table1 range from that other workbook in case where it has one, potentially ruining it). So I would argue one should always qualify their `Range` calls. – GSerg Jun 26 '19 at 13:37
  • Thanks for clarifying that @GSerg. I was just running some tests with 2 open workbooks and named ranges with same name, and arrived to the same conclusion. I'm still trying to figure out how to qualify the named range over a different workbook, but that's a different subject. – FAB Jun 26 '19 at 13:43
  • @FAB You don't need to qualify named range over a different workbook, you need to protect yourself from depending on [which book is active](https://stackoverflow.com/q/10714251/11683). That is, you qualify the `Range` call with your own workbook's sheet object, and that will always work regardless of the active workbook. – GSerg Jun 26 '19 at 13:47
0

As far as I can tell the error comes because you don't have the named range "cCodes").

Go to Formulas -> Name Manager and check your names. Alternatively, use a range directly in the code, i.e.: codes = ws.Range("A1:A100")

To answer your question in the comment:

Is there a way for me to directly reference the three columns of the table that I want to set to the array

Here are a few ways to manipulate the range from your table into the array (specific rows/columns), and back on the sheet (see comments in code). Hope this helps.

Option Explicit

Sub test()
    Dim rngData As Range
    Dim arrData As Variant

    With Range("Table1") 'this is only the content of the table, does not include the headers
        Set rngData = Range(.Cells(1, 1), .Cells(.Rows.Count, 3)) 'Set the range starting at cell row 1, col 1 - until total number of rows in table, col 3
        'Set rngData = rngData.Offset(-1).Resize(.Rows.Count + 1) 'if you want to include headers as well
        Debug.Print rngData.Address
    End With

    arrData = rngData 'allocate the data from the range to the array

    rngData.Offset(0, Range("Table1").Columns.Count + 1) = arrData 'put the array back on the sheet, 1 column to the right of the table

    Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Sheet1")
    ws.Range("N1").Resize(UBound(arrData), UBound(arrData, 2)) = arrData 'put the array back on the sheet in a specific range

End Sub
FAB
  • 2,505
  • 1
  • 10
  • 21