Excel VBA Error: 1004 Method 'Range' of object '_Worksheet' Failed when Selecting the Range of a Variables Value

1
votes

My problem: When trying to Set Table = cSheet.Range(brand_edit) (brand_edit is a variable with the value of a table name) I get Run-time error '1004': Method 'Range' of object '_WorkSheet' failed.

I think it is because I am trying to find the range of a variable's value and I can't think of a way to fix this.

More Info:

I am making a userform that allows the user to grab an Item ID from a Data Sheet in excel and fill a table/item list with the data from the row the Item ID leads.

I have setup a combobox that grabs my table of contents (the Brands) from the Data Sheet (I made it into a table and used the table as the RowSource). I then setup another combobox that displays the Item ID's from the selected Brand. When you select a particular ID I am trying to get it to grab the data from the row in the table that that Item ID leads.

Each Brand has a table tied to it I use some funky replacing to get it to match the table naming scheme. I use these tables as flexible ranges (these tables will change over time so I need to account for that). The item ID's lead the rows and each row has information about the item (cost, description, etc.)

If there are better ways to do any of these things, I am open to ideas.

User Form

These are some questions I viewed to try to solve this: This one introduced me to the code for this and I tried factoring to my use case and I got errors. Excel VBA - select, get and set data in Table

Public brand_edit As String

Private Sub cmbItemID_Change()

Dim cBook As Workbook
Dim cSheet As Worksheet
Dim ItemID As String
Dim Brand_Table As String
Dim test As String
Dim i As Long
Dim Table As ListObject

Set cBook = ActiveWorkbook
Set cSheet = cBook.Sheets("Gen. Info")

ItemID = cmbItemID.Value
Brand_Table = brand_edit

MsgBox Brand_Table

Set Table = cSheet.Range(brand_edit).Select

For i = 1 To Table.ListRows.Count
    If Table.ListColumns(1).DataBodyRange.Rows(i) = ItemID Then
    MsgBox ItemID
    End If
Next

MsgBox test

End Sub
Public Sub cmbItemID_DropButtonClick() 

'funky replacing

Dim brand As String
brand = cmbBrand.Value
brand_edit = Replace(brand, " ", "_")
brand_edit = Replace(brand_edit, """", "")
brand_edit = Replace(brand_edit, "-", "")
brand_edit = Replace(brand_edit, "__", "_")
brand_edit = LCase(brand_edit)

cmbItemID.RowSource = brand_edit

End Sub
3
vbaexcel
Welcome to Stack Overflow JED, thanks for all these details it's awesome that you put some effort in your first post - have an upvote for that. The next step is to edit the question to trim the fluff out, boil down the problem to its simplest, so that the question and its answers are not only helpful to you, but also to everyone in the future that will Google up "1004 Method 'Range' of object '_Worksheet' Failed" and end up right here on this page. Make sure you feast your eyes on How to Ask and minimal reproducible example in the SO Help Center, and you'll do great here. Cheers! - Mathieu Guindon
Thanks for the suggestions! I did some edits and tried to make the problem more apparent. - JED

3 Answers

0
votes

There is a better way to Synchronize ComboBoxes! No coding! enter image description here
Both ComboBoxes have the same dataSetting settings. They set the same value in the shared LinkedCell. 1. BoundColumn 2. LinkedCell 3. ListFillRange enter image description here But we change their display setting to give the user a different view. 1. ColumnCount 2. ColumnWidths 3. ListRows 4. ListWidth enter image description here

You can not directly set a the ListFillRange to a Table. The workaround is to create a Define a Name and set the ListFillRange the new name.

ListFillRange -> Products -> Table1

enter image description here

0
votes

So I fixed the problem. What was causing it was I was referencing the wrong sheet (aka Gen. Info). How I fixed it was this:

Set dSheet = cBook.Sheets("DATA")
Set Table = dSheet.Range(brand_edit)

Of course once one problem is fixed another one appears. So I am currently working to fix that one.

This post after reading it a number of times (a few last night before posting this and then a couple times this morning) I finnaly got what it was saying and helped me fix this problem. VBA method 'range of object' _Worksheet failed suddenly coming up when running code?

0
votes

Hopefully this is more useful.

Place getRowSource in a public module. It'll be easier to test and expand.

Public Function getRowSource(brand As String) As String
    brand = Replace(brand, " ", "_")
    brand = Replace(brand, """", "")
    brand = Replace(brand, "-", "")
    brand = Replace(brand, "__", "_")
    brand = LCase(brand)
    getRowSource = brand
End Function

In this way, you can test your rowsource logic in the Immediate Window

enter image description here

If cmbItemID's RowSource is the table that you want to reference then cmbItemID' s ListIndex is it the row that you want to reference.

Dim itemIndex As Long, brand_edit As String

brand_edit = getRowSource(cmbBrand.Value)
itemIndex = cmbItemID.ListIndex

With cSheet.ListObjects(brand_edit)
    TextBoxListPrice.Value = DataBodyRange(itemIndex, 1)
    TextBoxListCost.Value = DataBodyRange(itemIndex, 2)
    TextBoxNotes.Value = DataBodyRange(itemIndex, 3)
    TextBoxSpecs.Value = DataBodyRange(itemIndex, 4)
    TextBoxDescription.Value = DataBodyRange(itemIndex, 5)
End With