About Us

Dennis S.

Tom,

It works great, thanks again for your help.

"Tom Ogilvy" wrote:

I see there was a typo in the code:

Dim col as Range
Dim i as Long, lastrow as Long
On error resume next
set col = Application.InputBox("Select a cell in the column to process" & _
" using the mouse", Type:=8)
On Error goto 0
if col is nothing then
msgbox "You selected nothing, quitting"
exit sub
end if
lastrow = cells(rows.count,col.column).End(xlup).Row
for i = lastrow to 2 step -1
if cells(i,col.column).value < cells(i-1,col.column).Value then
rows(i).PageBreak = xlPageBreakManual
end if
Next

Should do it.

--
Regards,
Tom Ogilvy

"Dennis S." wrote in message
...
Tom,

You're correct, I'm wanting to start a new page each time the value
changes,
not fit all the same values on the same page.

I tried it again. This time it hangs up on the "If Cells(i,
col.Column).Value < Cells(i - 1, col.Column).Value Then" line.

I'm using this by creating a macro in Excel, opening the editor and
copying
and pasting the code from this thread. Could I be doing something wrong?

Thanks for yor patience.

"Tom Ogilvy" wrote:

Answered inline:
"Dennis S." <Dennis wrote in message
...
Hello Everyone,

I have three questions concerning a macro I'm trying to write.

I'm importing a text file daily into XL 2003 and saving using the same
filename each day. I'm using the macro recorder to write the macros,
since
I'm unfamiliar with VB, then trying to edit.

1. What is the command I need to use to always select the whole sheet,
since
the number of lines changes each day?

If it is organized like a database then
Dim rng as Range
set rng = Activesheet.Range("A1").currentRegion

if you just opened the text file in Excel you can also use

set rng = Activesheet.UsedRange

If you then want to select that range (and that is rarely necessary)

rng.select

2.I'm using a vlookup formula. When I run the macro It opens a "Update
Values" window a few times asking for the file reference in vlookup.
Once
I
respond, it takes forever to continue. Any way to fix this? It
doesn't
matter if the referenced file is already opened.

This would be hard to advise on. It sounds like you are entering a
formula
with an invalid reference. You should adjust your code to build a valid
reference before your code enters the formula. For example, instead of

ActiveCell.formula = "=Vlookup(A1,[Book1.xls]Sheet1!A1:Z26,5,False)"

you could have

Dim bkName as String
bkName = InputBox("Enter Workbook Name:")
ActiveCell.Formula = "=Vlookup(A1,[" & bkName &
"]Sheet1!A1:Z26,5,False)"

3. Is there a way to automatically insert page breaks each time the
data
in
a column changes, and ask the user which column to reference?

Dim col as Range
Dim i as Long, lastrow as Long
On error resume next
set col = Application.InputBox("Select a cell in the column to process"
& _
" using the mouse", Type:=8)
On Error goto 0
if col is nothing then
msgbox "You selected nothing, quitting"
exit sub
end if
lastrow = cells(rows.count,col.column).End(xlup).Row
for i = lastrow to 1 step -2
if cells(i,col.column).value < cells(i-1,col.column).Value then
rows(i).PageBreak = xlPageBreakManual
end if
Next

Thanks for your help,

Dennis

--
Regards,
Tom Ogilvy

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
A macro code question from a novice	Richard	Excel Discussion (Misc queries)	2	December 24th 07 07:22 PM
Novice needs help.	aphwood	Excel Discussion (Misc queries)	1	October 4th 05 07:56 PM
Macro Help for a Novice	Ann	New Users to Excel	4	July 19th 05 02:36 PM
Hoping a guru can help this novice out with a simple macro	slim[_2_]	Excel Programming	5	February 24th 04 12:53 AM

Menu

About Us