Which VBA code is better

[Andy]

Hi,

I have the following codes and would like to know which one is more
effective and the difference.

Sub Test1()
Dim myRow As Single

Range("A1") = Range("ItemTitle").Offset(myRow)
Range("B1") = Range("QtyTitle").Offset(myRow)

End Sub



Sub Test2()
Dim myRow As Single

Range("A1") = Cells(myRow, Range("ItemTitle").Column)
Range("B1") = Cells(myRow, Range("QtyTitle").Column)

End Sub

I like Test1 more as it is easier to write and seems to me that Test2 may
need a worksheet qualifier if it's referencing another worksheet.

Thanks
Andy, HK

 

[Crowbar via OfficeKB.com]

I prefer to use Cells as to Range, its easier when you work with looping
procedures i.e.

Dim y as lnteger
Dim y as Integer

For x = 1 to 10
y =y +1
cells(x,y) = x
Next x


Pretty pointless results from this but this wouldn't be as simple to achieve
using range

 

[Bob Phillips]

I don't see that either is 'better', one may suit one circumstance, one may
suit another.

main thing, ALWAYS use worksheet qualifiers, even if you are expecting the
activesheet, it is far clearer, and less difficult to debug.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Andy]

Thanks to both Bob and Crowbar

Andy

I don't see that either is 'better', one may suit one circumstance, one may
suit another.

main thing, ALWAYS use worksheet qualifiers, even if you are expecting the
activesheet, it is far clearer, and less difficult to debug.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

 

[Peter T]

I trust the respective methods are not intended to refer to the same pair of
cells !

Regards,
Peter T

 

[Dave Peterson]

I'd either version, but I'd modify them slightly.

Option Explicit
Sub Test1()
Dim myRow As Long
myrow = somenumberhere
with activesheet
.Range("A1").value = .Range("ItemTitle").Offset(myRow).value
.Range("B1").value = .Range("QtyTitle").Offset(myRow).value
end with
End Sub

I wouldn't use "As Integer". "As long" is faster for the computer to work
with. And I'd qualify the ranges (like Bob wrote) and I'd specify the property
that I wanted.

And I'd add "option explicit" to the top of the module--I want to be forced to
declare my variables.

 

[Dave Peterson]

I'd either version, but I'd modify them slightly.
should be:
I'd USE either version, but I'd modify them slightly.

 

[Andy]

Thanks Dave for the detailed explanation.

I'd either version, but I'd modify them slightly.

Option Explicit
Sub Test1()
Dim myRow As Long
myrow = somenumberhere
with activesheet
.Range("A1").value = .Range("ItemTitle").Offset(myRow).value
.Range("B1").value = .Range("QtyTitle").Offset(myRow).value
end with
End Sub

I wouldn't use "As Integer". "As long" is faster for the computer to work
with. And I'd qualify the ranges (like Bob wrote) and I'd specify the
property
that I wanted.

And I'd add "option explicit" to the top of the module--I want to be
forced to
declare my variables.

 

[Alan Beban]

Notwithstanding advice about ALWAYS using worksheet qualifiers, another
tool you might want to consider, if using the code in a General Module,
is the following, which doesn't require such qualification, without
regard to which sheet is active:

Set rngA = Sheets(2).range("A1")
rngA.Value = rngA(myRow, range("ItemTitle").Column)

Alan Beban

 

[Andy]

Notwithstanding advice about ALWAYS using worksheet qualifiers, another
tool you might want to consider, if using the code in a General Module, is
the following, which doesn't require such qualification, without regard to
which sheet is active:

Set rngA = Sheets(2).range("A1")
rngA.Value = rngA(myRow, range("ItemTitle").Column)

Alan Beban

Thanks Alan, I'll certainly consider this approach. Learned few things
today, how lucky I am, thank you guys.

Andy