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
tiehazeAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
also, you don't need the .select:


Sub SetRange()
Dim r As Range
Set r = sheet1.Range("N1")

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
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>nd it keeps on giving me errors
what errors?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
note that I would change the Sub name from RANGE to something else, like SetRange for example...
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
tiehazeAuthor Commented:
'object required' ===> Set r = Range("N1").Select
0
 
Arthur_WoodCommented:
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
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
So I confirm my suggestion of changing the procedure name (same what Arthur_Wood suggests)
angelIII
0
 
tiehazeAuthor Commented:
Doesn't change anything... I am still getting the error. If it helps, it is a run time error 424
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.