Cannot allow multiple Range in a Sub

Hi



I tried the following code but it shows an error:





Sub test2col()
Sheets("Sheet6").Cells.ClearContents
Sheets("Sheet6").Range("A:B").Value = Sheets("Sheet1").Range("A:B").Value
Sheets("Sheet6").Range("D").Value = Sheets("Sheet1").Range("C").Value
End Sub





It said



Sheets("Sheet6").Range("D").Value = Sheets("Sheet1").Range("C").Value






has error.



Does a Sub not support to use Range more than one time? If so, do I need to write many Sub to do it?



Thanks for advance.

Hi,

with the Range method you need to use the "Column1:Column2" syntax even if columns 1 and 2 are the same. So, Range("A:A") will work. Alternatively, you can also use Columns("A").

I think the reason for this behaviour is that Range("A") looks for a named range... (?)

Thanks for help.

Hi,
I think the reason for this behaviour is that Range("A") looks for a named range... (?)

Just to confirm, yes - I too think it would be looking for a named range. :)

I can think of the following syntax variations:

Range("A1").Value = 1 '1. single cell
Range("B2:B4").Value = 2 '2. block of cells B2:B4
Range("C2,C4,C6").Value = 3 '3. Union style syntax (non-continuous range): cells C2, C4, C6
Range("SomeName").Value = 4 '4. named range
Range(Cells(5,5),Cells(6,6)).Value = 5 '5. E5:F6
Range("A2", "A4").Value = 6 '6. the top left/bottom right corners of the block, so A2:A4

Range("B1:B3 A2:C2").Value = 7 '7. Intersect style syntax; intersect of B1:B3 A2:C2 is cell B2.


Examples 1 to 4 are all syntax Range(arg).
So I guess if you wanted to you could mix them together like:
Range("C2,C4,C6:C8,B2:B13").Value = 3

Examples 5 to 6 are syntax Range(arg1,arg2). You don't have to use range objects in these arguments.
So, I guess if you wanted to you could mix them together like:
Range(Cells(5, 5), "F6").Value = 5

etc... etc...


Now, this can be confused with the Columns Property, where you can do what you were trying to:
Columns("A").Value = 1

Colin

For the sake of completeness, there's also the intersection syntax:
Range("B1:B3 A2:C2") 'single cell B2
Not that I've ever actually used it, AFAIR... ;)

Ah good point, I missed it - I'll add it onto the list now for completeness. Thanks! :)