Question:
Can someone pls point out my error.I’m trying to set a range object to be comprised of a Range1 and an Offset from Range1. There is a table of values I will be passing from into; hence the variables in the range object variable.
Original Error:Run-time Error ‘1004’ Method “Range” of object_global failed. Essentially I’m looking for (example) result: Range(“A2:B94”) where A is known, 2-is unknown, B94 is offset n-columns and n-rows from A2.
Below is a revised sub using resize method, yet it still has an error.
Answer:
You can use theRange.Resize-method. Also, it is often easier to use the Cells-property when coding as it takes numeric parameters for row and column.Also, you should always specify on which sheet you are working, else VBA will use the ActiveSheet and that may or may not be the sheet you want to work with.
You have some issues with your variable declarations:
o
Dim line0, nrow0, ncol0 As Double will declare line0 and nrow0 as Variant and only ncol0 as Double. You need to specify the type for every variable.o You should declare all variables the deal with row or column numbers as
Long. Integer may give you an overflow and Double makes not much sense as the numbers have never a fraction part.If I understand your code correctly you could use
Dim line0 as Long, nrow0 as Long, ncol0 As Long, diff0 As Long
With ThisWorkbook.Sheets(1) ‘ <-- Replace with the book & sheet you want to work with
Set rng0 = .Cells(line0 + diff0, 1).resize(nrow0, ncol0)
End With
[/code]
If you have better answer, please add a comment about this, thank you!