I am trying to copy values from a range to another place without copy-pasting. But im running in to a problem with a .Range().Resize() depending on how i delcare the range.
If i try the code as follows it works fine:
Sub Test()
Dim ws1 As Worksheet
Set ws1 = Worksheets("TEST 1")
Dim CopyFrom As Range
Set CopyFrom = ws1.Range(Cells(1, 2), Cells(10, 2))
ws1.Range("A1").Resize(CopyFrom.Rows.Count).Value = CopyFrom.Value
End Sub
But if i use the following intead, i get an run-time error '1004', Method 'Range' of object'_Worksheet' failed:
ws1.Range(Cells(1, 1)).Resize(CopyFrom.Rows.Count).Value = CopyFrom.Value
I´m getting that both methods of declaring the single cell range are somehow not the same, but how do they differ exactly? Also, is the a way do do this still using the R1C1 notation? And are there differences in performance in either one?
I find its easier to keep things organized using R1C1, but it´s not the end of the end of the world if i have to mix them.
Also tryed adding a ws1. to all Cells() but it made no difference.
The confusion comes from the fact that the Range-method can be called with one or with two parameters, and those two calls differ:
When called with one parameter, you specify a Range address (as string).
Examples:
Dim r As Range
Set r = ws1.Range("C3")
Set r = ws1.Range("A1:F10")
Dim addr as String
addr = "A1:A20"
Set r = ws1.Range(addr)
With ws1
Dim lastRow As Long
lastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
Set r = .Range("AB1:AB" & lastRow)
End With
When called with two parameters, both parameters must be of type Range. Those Ranges define top, left, bottom and right of the range.
Examples:
With ws1
Set r = .Range(.Cells(1, 1), .Cells(10, 10))
End With
Dim r1 As Range, r2 As Range
Set r1 = ws1.Range("A1")
Set r2 = ws1.Cells(10, 10)
Set r = ws1.Range(r1, r2)
Set r = ws1.Range(ws1.Cells(2, "B"), ws1.Range("X10"))
However, you "mixed" those two calls: ws1.Range(Cells(1, 1)) calls the Range-method with one parameter, but this parameter is not an Range Address but a Range. Used like this the Value of Cells(1, 1) is used (if that looks like a range address you'll get a result, otherwise an error) Note that not qualifying Cells with a worksheet means it references a cell on the ActiveSheet, which may or may not be ws1.
You could use ws1.Range(ws1.Cells(1, 1).address), but obviously that's a little but too complicated. Just use ws1.Cells(1, 1) directly:
ws1.Cells(1, 1).Resize(CopyFrom.Rows.Count).Value = CopyFrom.Value
Update: Just figured out that it is possible to provide range addresses also when using the variant with two parameters:
Set r = ws1.Range("A1", "C4")
Set r = ws1.Range("A1", r1)
Set r = ws1.Range(r2, "C4")
However, it is not possible to use a Range (like ws1.Cells(1, 1)) when using the variant with one paramter.