I am trying to take values from a sheet and copy them to another sheet, and I am getting this error: Run-time error '1004': Method 'Range' of object '_Worksheet' failed.
Here is the code
the 3rd line is what is generating the error. What makes it bizarre is that line 1, 2, and 3 are all almost identical. Each Range in LoadStudy is using the exact same definition for the location except for the column value. So line 1 refers to column O, line 2 is column P, line 3 is column Q. These are dynamic ranges defined by this code:
=OFFSET('Load Study'!$Q$12,0,0,MATCH("*",'Load Study'!$Q:$Q,-1),1)
Where the Q is the changing column value.
In the VBA debugging, it shows that the right side of my equations (related to the object PartWorksheet) are all properly evaluating.
I've tried setting the Range LoadStudy.Range("OutputDeviation") as a variable, and it fails with the same error.
I've also tried renaming it to rule out naming conflicts.
For some reason, excel is not acknowledging this range as existing. Additionally, these ranges are defined in the scope of the Load Study worksheet only, so there is no conflict of interest from a global range.
Any ideas? My best guess is that the error is stemming from the code that defines the range in Name Manager, but because the code is identical to its neighbors, I can't find a problem with it.
EDIT: RESOLVED.
It had something to do with the column. I just deleted the column entirely, recreated column Q, and suddenly it worked. I have no idea what was going on.
Here is the code
Code:
LoadStudy.Range("OutputWireSize").Cells(i, 1) = PartWorksheet.Range("WireSize")
LoadStudy.Range("OutputVoltageDrop").Cells(i, 1) = PartWorksheet.Range("UniqueVoltageDrop")
LoadStudy.Range("OutputDeviation").Cells(i, 1) = PartWorksheet.Range("UniqueVDPercent")the 3rd line is what is generating the error. What makes it bizarre is that line 1, 2, and 3 are all almost identical. Each Range in LoadStudy is using the exact same definition for the location except for the column value. So line 1 refers to column O, line 2 is column P, line 3 is column Q. These are dynamic ranges defined by this code:
=OFFSET('Load Study'!$Q$12,0,0,MATCH("*",'Load Study'!$Q:$Q,-1),1)
Where the Q is the changing column value.
In the VBA debugging, it shows that the right side of my equations (related to the object PartWorksheet) are all properly evaluating.
I've tried setting the Range LoadStudy.Range("OutputDeviation") as a variable, and it fails with the same error.
I've also tried renaming it to rule out naming conflicts.
For some reason, excel is not acknowledging this range as existing. Additionally, these ranges are defined in the scope of the Load Study worksheet only, so there is no conflict of interest from a global range.
Any ideas? My best guess is that the error is stemming from the code that defines the range in Name Manager, but because the code is identical to its neighbors, I can't find a problem with it.
EDIT: RESOLVED.
It had something to do with the column. I just deleted the column entirely, recreated column Q, and suddenly it worked. I have no idea what was going on.
Last edited:
