PDA

View Full Version : Solved: I'm having trouble selecting a range



EirikDaude
10-02-2009, 01:56 AM
I'm making a macro for (among other things) copying a selection of cells from one worksheet to another. According to the VBA-help it seems the method I'm using should work, but it seems that when I try to select the range I want to copy the "Cells()"-function returns the contents of the cell instead of the address. I've also tried using Cells().Address, but I didn't get that to work either.

Have any of you guys got any idea of a different approach I can use to acchieve the same result?

I'm using Excel 2003 with SP3 and Visual Basic 6.5.

Here's the code I've come up with:
For i = LBound(skift1MF) To UBound(skift1MF)
Sheets("Kompetanseoversikt").Range(Cells(skift1MF(i), 1), Cells(skift1MF(i), 8)).Select
Selection.Copy
Sheets("Kompetanseoversikt (2)").Range(Cells(5 + i, 1), Cells(5 + i, 8)).Select
ActiveSheet.Paste
Next

Bob Phillips
10-02-2009, 02:57 AM
Set sh = Sheets("Kompetanseoversikt (2)")

With Sheets("Kompetanseoversikt")

For i = LBound(skift1MF) To UBound(skift1MF)

.Range(.Cells(skift1MF(i), 1), .Cells(skift1MF(i), 8)).Copy _
sh.Range(sh.Cells(5 + i, 1), sh.Cells(5 + i, 8))
Next
End With

EirikDaude
10-02-2009, 03:39 AM
Thanks for the help!

It seems that solved my problem. But could you please tell me why my attempt at defining the range failed while your worked. I'm curious :)

I'd also like to know if I was correct in defining "sh" as an object-variable (I was asked to define the variable before I was allowed to set any properties for it).

Bob Phillips
10-02-2009, 05:02 AM
You were not qualifying all of the range objects (Range, Cells), and so it was reflecting the activesheet in those cases, not the one you thought.