first sip of working with range in vba excel

[bpascal123]

Hi,

I have started learning vba for excel. Now, I feel fine with cells
workarounds in a loop or else. But when it comes to a range, I can't
find the reason why if I'm following the excel object model it's not
working.

Sub tryingtocopyrange()

Dim wkb1 As Workbook 'copy from wkb1
Dim wkb2 As Workbook 'copy to wkb2
Dim wks1 As Worksheet 'source sheet for wkb1
Dim wks2 As Worksheet 'destination sheet for wkb2

Set wkb1 = Workbooks("source.xls")
Set wkb2 = Workbooks("desti.xls")
Set wks1 = wkb1.Worksheets("mysource")
Set wks2 = wkb2.Worksheets("mydesti")

'A - the following is not working
'wks2.Range(Cells(1, 1), Cells(10, 3)) = wks1.Range(Cells(1, 1),
Cells(10, 3)).Value

'B - this is working but i'd rather use the cell method as i find it
easier to play with variables for indices in a loop
'wks2.Range("A1:C10") = wks1.Range("A1:C10").Value

'C - using the copy method, I should be able to copy a range and set
one cell as a destination
wks1.Range(Cells(1, 2), Cells(10, 2)).Copy
Destination:=wks2.Cells(4, 1)

End Sub

This little step that makes my life hard as a non-programmer trying to
code in vba, once over would take me to bigger steps such as copying
range into vba arrays...

Thanks,
Pascal

 

[GS]

bpascal123 explained :

Hi,

I have started learning vba for excel. Now, I feel fine with cells
workarounds in a loop or else. But when it comes to a range, I can't
find the reason why if I'm following the excel object model it's not
working.

Sub tryingtocopyrange()

Dim wkb1 As Workbook 'copy from wkb1
Dim wkb2 As Workbook 'copy to wkb2
Dim wks1 As Worksheet 'source sheet for wkb1
Dim wks2 As Worksheet 'destination sheet for wkb2

Set wkb1 = Workbooks("source.xls")
Set wkb2 = Workbooks("desti.xls")
Set wks1 = wkb1.Worksheets("mysource")
Set wks2 = wkb2.Worksheets("mydesti")

'A - the following is not working
'wks2.Range(Cells(1, 1), Cells(10, 3)) = wks1.Range(Cells(1, 1),
Cells(10, 3)).Value

'B - this is working but i'd rather use the cell method as i find it
easier to play with variables for indices in a loop
'wks2.Range("A1:C10") = wks1.Range("A1:C10").Value

'C - using the copy method, I should be able to copy a range and set
one cell as a destination
wks1.Range(Cells(1, 2), Cells(10, 2)).Copy
Destination:=wks2.Cells(4, 1)

End Sub

This little step that makes my life hard as a non-programmer trying to
code in vba, once over would take me to bigger steps such as copying
range into vba arrays...

Thanks,
Pascal

Pascal,
What you need to understand is the concept of 'fully qualified'
references. Consider this approach:

Sub CopyRange()
Dim wksSource As Worksheet, wksTarget As Worksheet

Set wksSource = Workbooks("source.xls").Sheets("mysource")
Set wksTarget = Workbooks("desti.xls").Sheets("mydesti")

With wksSource
.Range(.Cells(1, 2), .Cells(10, 2)).Copy _
Destination:=wksTarget.Cells(4, 1)
End With

'OR
wksTarget.Cells(4, 1) = _
wksSource.Range(wksSource.Cells(1, 2), wksSource.Cells(10, 2))
End Sub

 

[GS]

A better solution:

Sub CopyRange()
Dim wksSource As Worksheet, wksTarget As Worksheet

Set wksSource = Sheets("Sheet2")
Set wksTarget = Sheets("Sheet1")

With wksSource
.Range(.Cells(1, 2), .Cells(10, 2)).Copy _
Destination:=wksTarget.Cells(4, 1)
End With

'OR
Dim v As Variant
v = wksSource.Range(wksSource.Range(wksSource.Cells(1, 2),
wksSource.Cells(10, 2)).Address)
wksTarget.Range(wksTarget.Cells(4, 1).Address).Resize(UBound(v), 1) =
v
End Sub

All are single line statements so watch word-wrapping.

 

[Clif McIrvin]

Hi,

I have started learning vba for excel. Now, I feel fine with cells
workarounds in a loop or else. But when it comes to a range, I can't
find the reason why if I'm following the excel object model it's not
working.
[ ]

'B - this is working but i'd rather use the cell method as i find it
easier to play with variables for indices in a loop
'wks2.Range("A1:C10") = wks1.Range("A1:C10").Value

[ ]

GS answered your question "Why is this working but that is not?" (Using
Object qualifiers - which you in fact had partly correct in your earlier
question about aligning data in columns.)

I'd like to comment on the differences between using cell indexing and
the A1 reference method you referred to just now.

You are absolutely correct that using Cell(row, column) indexing is much
more suited to programmatic manipulation than the alphabetic based A1
style of referencing.

I'm coming to the conclusion, however, that there are times when using
the A1 reference results in more readable code. Consider:

ColAin = .Transpose(Range("A1:A" & Cells(Rows.Count, _
"A").End(xlUp).Row))

compared with:

With WorksheetFunction
C1in = .Transpose(Range(Cells(1, 1), _
Cells(Cells(Rows.Count, 1).End(xlUp).Row, 1)))
End With

or even:

LastC1in = Cells(Rows.Count, 1).End(xlUp).Row
With WorksheetFunction
C1in = .Transpose(Range(Cells(1, 1), Cells(LastC1in, 1)))
End With

The major distinction there, I think, is the difference between A1 style
notation (the Excel user interface default) and RC (Row, Column)
notation that is more naturally suited to programmatic indexing (but not
commonly seen in the user interface.)

Conclusion: use the notation that makes the most sense to you for the
task at hand.

 

[GS]

Of course, you'll need to replace

Set wksSource = Sheets("Sheet2")
Set wksTarget = Sheets("Sheet1")

with...

Set wksSource = Workbooks("source.xls").Sheets("mysource")
Set wksTarget = Workbooks("desti.xls").Sheets("mydesti")

 

[bpascal123]

Hi,

I replaced
wks2.Range(Cells(1, 1), Cells(10, 3)) = wks1.Range(Cells(1, 1),
Cells(10, 3)).value

with
wks2.Range(wks2.Cells(1, 1), wks2.Cells(10, 1)) =
wks1.Range(wks1.Cells(1, 1), wks1.Cells(10, 3)).Value

Honestly, i haven't been able to see i needed to link the cell
property in a range to a specific worksheet. Because to me, the range
object is already linked to a specific worksheet then the cells
property in the range object doesn't to be linked to a specific range
on a worksheet

thanks
Pascal

 

[Clif McIrvin]

Hi,

I replaced
wks2.Range(Cells(1, 1), Cells(10, 3)) = wks1.Range(Cells(1, 1),
Cells(10, 3)).value

with
wks2.Range(wks2.Cells(1, 1), wks2.Cells(10, 1)) =
wks1.Range(wks1.Cells(1, 1), wks1.Cells(10, 3)).Value

Honestly, i haven't been able to see i needed to link the cell
property in a range to a specific worksheet. Because to me, the range
object is already linked to a specific worksheet then the cells
property in the range object doesn't to be linked to a specific range
on a worksheet

Pascal, does the built-in help make sense to you? I found that it took
me quite a significant period of time before the object model and the
distinctions between objects, properties and methods began to actually
make sense to me. By "quite a significant period of time" memory tells
me that means some months, not days or even a few weeks.

From the VBE window enter [ range property ] in the help search box --
you should get a list of articles to choose from. Quoting from that help
text:

<q>When used without an object qualifier, this property is a shortcut
for ActiveSheet.Range (it returns a range from the active sheet; if the
active sheet isn't a worksheet, the property fails).</q>

In other words, when you use Range (or Cell) in code without a
qualifier, VBA uses the ActiveSheet object -- which may or may not be
what you expect it to be.

What you said above is an excellent illustration of why it is necessary
to develop an understanding of the rules that govern the compiler. In
this case, what seems to be "common sense" to you is in fact a violation
of the rules that the complier follows.

wks2.Range(Cells(1, 1), Cells(10, 3)) = wks1.Range(Cells(1, 1),
Cells(10, 3)).value

*cannot* work, because you are expecting the Cells property to return
cells from two different worksheets ... but there is only one
AcitveSheet (and it could easily be neither wks1 nor wks2!)

One of the things that was difficult for me to grasp is that there are
both Range properties and Range objects ... and they do entirely
different things. Let's see if I can summarize: The Cells property
returns a Range Object consisting of a single cell. The Range property
(many objects have a Range property- for instance: Excel Application
Window objects, Worksheet objects and Range objects to name three) also
returns a Range object - which may contain one or many cells, included
in one or many areas. VBA rules allow you to use Range and Cells with or
without object qualifiers - but if you do not qualify (disambiguate) the
default object is always the active sheet.

HTH!