question on VB codes

P

peyman

hi,
Can anybody tell me what the following code does?
Set myRng=Me.Range("a3:a5,a7:a19,a22:a33")
thanks
 
J

Jim Thomlinson

It initializes a range object made up of the cells a3:a5,a7:a19,a22:a33.

Try this...

dim rng as range
dim myRng as rng

Set myRng=Me.Range("a3:a5,a7:a19,a22:a33")

for each rng in myRng
msgbox rng.Address & vbcrlf & rng.value
next rng
 
J

Jim Thomlinson

oops...

dim rng as range
dim myRng as range 'Booboo here

Set myRng=Me.Range("a3:a5,a7:a19,a22:a33")

for each rng in myRng
msgbox rng.Address & vbcrlf & rng.value
next rng
 
P

peyman

thanx Jim

Jim Thomlinson said:
oops...

dim rng as range
dim myRng as range 'Booboo here

Set myRng=Me.Range("a3:a5,a7:a19,a22:a33")

for each rng in myRng
msgbox rng.Address & vbcrlf & rng.value
next rng
Click to expand...
 
J

Jim Thomlinson

The biggest step you can make in taking your code to the next level is to get
your head around objects like range, worksheet and workbook. When you do this
you can get away from activecell, activesheet and active book. This will make
your code a lot more compact and efficient while opening up a whole pile of
new possibilities...
 
P

peyman

hi Jim,
i used the code as:
Private Sub CommandButton15_Click()
Dim rng As Range
Dim myrng As Range
Set myrng = Me.Range("a90:a92,a94:101,a105:a115")
For Each rng In myrng
MsgBox rng.Address & vbCrLf & rng.Value
Next rng

End Sub
but there is an error on line "Set myrng =
Me.Range("a90:a92,a94:101,a105:a115")"
with this message:
method 'range' of object '_worksheet' failed
 
J

Jim Thomlinson

Get rid of the word me. Since you are coding a command button Me will refer
to the command button and not the sheet. So try this...

Private Sub CommandButton15_Click()
Dim rng As Range
Dim myrng As Range
Set myrng = Range("a90:a92,a94:101,a105:a115")
For Each rng In myrng
MsgBox rng.Address & vbCrLf & rng.Value
Next rng

End Sub

Since your code resides within a sheet any time you use range without a
sheet referenced it will refer to the sheet that the code is in.
 
J

Jim Thomlinson

There is an error in your range... a90:a92,a94:101,a105:a115

a94:A101??? so try this...

Private Sub CommandButton15_Click()
Dim rng As Range
Dim myrng As Range
Set myrng = Range("a90:a92,a94:a101,a105:a115")
For Each rng In myrng
MsgBox rng.Address & vbCrLf & rng.Value
Next rng

End Sub
 
P

peyman

yeah, it works!
thanx

Jim Thomlinson said:
There is an error in your range... a90:a92,a94:101,a105:a115

a94:A101??? so try this...

Private Sub CommandButton15_Click()
Dim rng As Range
Dim myrng As Range
Set myrng = Range("a90:a92,a94:a101,a105:a115")
For Each rng In myrng
MsgBox rng.Address & vbCrLf & rng.Value
Next rng

End Sub
Click to expand...
 
Top