tiehaze
asked on
Problems setting ranges
This is the beginning of my macro, and it keeps on giving me errors. I am trying to scroll down column N until the two cells to the right are blank. I cannot figure out why this isn't working....
Sub Range()
Dim r As Range
Set r = Range("N1").Select
Do Until r.Offset(0, 1).value = "" And r.Offset(0, 2).value = ""
Set r = r.Offset(1, 0)
Loop
Set r = r.Offset(1, 0)
End Sub
Sub Range()
Dim r As Range
Set r = Range("N1").Select
Do Until r.Offset(0, 1).value = "" And r.Offset(0, 2).value = ""
Set r = r.Offset(1, 0)
Loop
Set r = r.Offset(1, 0)
End Sub
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
note that I would change the Sub name from RANGE to something else, like SetRange for example...
ASKER
'object required' ===> Set r = Range("N1").Select
Range is an Excel KEYWORD, and you will encounter all kinds of problems by using that as the name of a Proceude in Excel.
make this change:
Sub My_Range()
Dim r As Range
Set r = Range("N1").Select
Do Until r.Offset(0, 1).value = "" And r.Offset(0, 2).value = ""
Set r = r.Offset(1, 0)
Loop
Set r = r.Offset(1, 0)
End Sub
AW
make this change:
Sub My_Range()
Dim r As Range
Set r = Range("N1").Select
Do Until r.Offset(0, 1).value = "" And r.Offset(0, 2).value = ""
Set r = r.Offset(1, 0)
Loop
Set r = r.Offset(1, 0)
End Sub
AW
So I confirm my suggestion of changing the procedure name (same what Arthur_Wood suggests)
angelIII
angelIII
ASKER
Doesn't change anything... I am still getting the error. If it helps, it is a run time error 424
you might want to specify the worksheet you want to handle, for example sheet1.range instead of simply raying range
Sub SetRange()
Dim r As Range
Set r = sheet1.Range("N1").Select
Do Until r.Offset(0, 1).value = "" And r.Offset(0, 2).value = ""
Set r = r.Offset(1, 0)
Loop
Set r = r.Offset(1, 0)
End Sub
Sub SetRange()
Dim r As Range
Set r = sheet1.Range("N1").Select
Do Until r.Offset(0, 1).value = "" And r.Offset(0, 2).value = ""
Set r = r.Offset(1, 0)
Loop
Set r = r.Offset(1, 0)
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
what errors?