Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / General Excel Questions / March 2008

Tip: Looking for answers? Try searching our database.

Experimenting with (offset in VBA)

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Brad - 20 Mar 2008 22:14 GMT
What I'm trying to change a heading on sheet 1 and print the page- the
"variable" heading information is in column A on sheet 3 and the first time
that is comes to a blank - end the loop.  If print fine the first time and
then it gives me a Run-time error '13" on the Loop Until line

Am I even close with the logic found below?

Sub print_many()
 Dim iRow As Long
 iRow = 0
 With Range("NameList")
   Do
     Range("TestName").Value = Sheet3.Range("a" & iRow + 1).Value
     Sheet1.PrintOut
     iRow = iRow + 1
   Loop Until .Offset(iRow, 0).Value = ""
 End With

End Sub
Don Guillett - 20 Mar 2008 22:53 GMT
Can't quite tell what's going on here but, assuming your "rangelist" is
defined, why not just something like:

sub printem()
for each c in range("rangelist")
Range("TestName").Value = c
Sheet1.PrintOut
next c
end sub

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

>
> What I'm trying to change a heading on sheet 1 and print the page- the
[quoted text clipped - 17 lines]
>
> End Sub
Jim Thomlinson - 20 Mar 2008 23:16 GMT
From the code posted I don't think NameList is a dynamic named range. I think
it is a single cell on sheet 3...
Signature

HTH...

Jim Thomlinson

> Can't quite tell what's going on here but, assuming your "rangelist" is
> defined, why not just something like:
[quoted text clipped - 26 lines]
> >
> > End Sub
Jim Thomlinson - 20 Mar 2008 23:14 GMT
There is an inherant paradox in using named ranges in code. A global named
range is visible from any sheet. When you write code however if you have a
line like

Range("NameList")
it is the same as
ActiveSheet.Range("NameList")

if NameList does not exist on the active sheet then the code will fail. So
as a guess your sheet 1 is active when you run this code. Since NameList is
on sheet 3 the code dies.

Take a look at this code to see if it makes sense to you...

Sub PrintMany()
   Dim rng As Range
   Dim rngToSearch As Range
   
   With Sheet3
   Set rngToSearch = .Range(.Range("NameList"), _
    .Range("NameList").End(xlDown))
   End With
   
   For Each rng In rngToSearch
       Sheet1.Range("TestName").Value = rng.Value
       Sheet1.PrintPreview
   Next rng
End Sub

or with your code...

Sub print_many()
 Dim iRow As Long
 iRow = 0
 With Sheet3.Range("NameList")
   Do
     Sheet1.Range("TestName").Value = Sheet3.Range("a" & iRow + 1).Value
     Sheet1.PrintOut
     iRow = iRow + 1
   Loop Until .Offset(iRow, 0).Value = ""
 End With

End Sub
Signature

HTH...

Jim Thomlinson

> What I'm trying to change a heading on sheet 1 and print the page- the
> "variable" heading information is in column A on sheet 3 and the first time
[quoted text clipped - 15 lines]
>
> End Sub
Brad - 21 Mar 2008 14:44 GMT
I posted a thank you - but don't think that the message stuck.

It took a minute for me to understand rng as a rng and a counter, but the
lightbulb went off (at least I think that it did).

> There is an inherant paradox in using named ranges in code. A global named
> range is visible from any sheet. When you write code however if you have a
[quoted text clipped - 59 lines]
> >
> > End Sub
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.