Which VBA code is better
 

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

Which VBA code is better
 

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

--
Message posted via http://www.officekb.com

Which VBA code is better
 

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" wrote in message
...
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

Which VBA code is better
 

Thanks to both Bob and Crowbar

Andy
"Bob Phillips" wrote in message
...
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" wrote in message
...
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

Which VBA code is better
 

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

Regards,
Peter T

"Andy" wrote in message
...
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

Which VBA code is better
 

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.



Andy wrote:

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

--

Dave Peterson

Which VBA code is better
 

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

Dave Peterson wrote:

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.

Andy wrote:

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

--

Dave Peterson

--

Dave Peterson

Which VBA code is better
 

Thanks Dave for the detailed explanation.

"Dave Peterson" wrote in message
...
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.



Andy wrote:

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

--

Dave Peterson

Which VBA code is better
 

Andy wrote:
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

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

Which VBA code is better
 

"Alan Beban" wrote in message
...
Andy wrote:
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

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