The error “Method Range of object worksheet failed” typically occurs when there’s an issue with how the Range object is being used or referenced. In your VBA code, the way you’re referencing the range appears to be unusual and might be causing the error. Let’s correct the range references.
In your code, you are using:
FRAMES = rs.Range(rs.Cells(2, COLUMN)).Value
WIDTH = rs.Range(rs.Cells(3, COLUMN)).Value
HEIGHT = rs.Range(rs.Cells(4, COLUMN)).Value
DEPTH = rs.Range(rs.Cells(5, COLUMN)).Value
The Range method is generally used with a string argument to specify a range address, or with two Range objects to specify the start and end of a range. However, you are passing a single Cell object to Range, which is not a typical usage and likely causing the error.
You can directly access the value of a cell using the Cells property without needing to use Range. Here’s how you can modify the code:
FRAMES = rs.Cells(2, COLUMN).Value
WIDTH = rs.Cells(3, COLUMN).Value
HEIGHT = rs.Cells(4, COLUMN).Value
DEPTH = rs.Cells(5, COLUMN).Value
In this revised code, COLUMN should be a numeric value representing the column index (e.g., 1 for column A, 2 for column B, etc.) or a string representing the column letter (e.g., "A", "B", etc.). Ensure that COLUMN is defined and set to the correct column before this part of the code executes.
Additionally, make sure that the rs worksheet actually has data in the specified cells and that the data types in those cells are compatible with being assigned to a Long variable. For example, they should not contain text, and should not be empty.