question on VB codes

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

hi,
Can anybody tell me what the following code does?
Set myRng=Me.Range("a3:a5,a7:a19,a22:a33")
thanks
 
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
 
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
 
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...
 
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...
 
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
 
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.
 
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
 
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...
 
Back
Top