Hi Tom,
You hit the nail right on the head. When I was receiveng
the error the code would bring me straight to cell
A65536. I ended up changing the code which I guess
changed how my data ranges were being referred to. I
really appreciate the replies I have received from this
newsgroup. You guys are great! It inspires me to keep
trying to learn and get better. Thank you very much.
-----Original Message-----
If you have the code in a worksheet module, then there
are a lot of reasons
it won't work. every unqualified
Range("whatever").Select
will refer to the sheet containing the code - not the
activesheet, even
though that is what you intended.
So when you do
Range("A2").Select
Selection.End(xldown)
cellloc2 = ActivceCell.Row
the active cell is probably A65536 which causes an
overflow error when
cellloc2 is dimensioned as Integer.
If you declare it as long, you won't get the overflow
error on that line.
You may get other errors or have an overflow error for
some other variable
declared as Integer in another location.
Move you code into a general module. If you need to
execute it from a
commandbutton or something, then just call it - but
leave it in a general
module or learn real fast how to reference cells without
use select and
activate.
--
Regards,
Tom Ogilvy
"Dave Y" wrote in message
...
Hi Tom,
Thanks for your reply. I tried changing the variable
type
from Integer to Long, but still receive the same Run-
time
error. I don't understand why the macro works until I
move
the formulas to a different worksheet. But thanks for
the
input. Any other suggestions will be appreciated.
-----Original Message-----
Dim cellloc As Integer
Dim cellloc2 As Integer
can only hold a value up to 32767
but rows can be up to 65536 - so
Dim cellloc as Long
Dim celloc2 as Long
would be the first thing I would try.
--
Regards,
Tom Ogilvy
"Dave Y" wrote in message
...
I have an Excel wookbook that has three tabs. Two
of the
tabs are for doing manual input such as adding and
deleting rows, or adding/deleting data. The third
tab
(called Print) is meant to be read only where no
manual
input is to be done. I have a macro that is run
from a
button on the Print tab that combines all the manual
input
from the other two tabs and formats it all nicely
on the
Print tab for the purpose of updating the data and
printing it out. This macro works great. The
problem I
have is that the macro formuls are located on the
Print
tab and occasionally people who work in this
workbook
forget not to do anything on the Print tab and they
sometimes add or delete rows and throw off the
formulas.
So I created another tab called lookups and cut &
pasted
the macro formulas to that tab with the intent of
hiding
the lookups worksheet and eliminate the user
errors. My
problem is that after I moved the formulas from the
Print
worksheet to the lookups worksheet I am receiving a
Run-
time error '6': Overflow when I run the macro. I
have
included the code from the VB editor and I put ***
before
the line of code that is highlighted when I run the
Debugger. I know this is a long post and I
appreciate
the
patience in reading it. Any help in resolving this
macro
issue will be greatly appreciated.
Thanks, Dave (code below)
Dim cellloc As Integer
Dim cellloc2 As Integer
Application.Goto Reference:="clear"
Selection.RemoveSubtotal
Range("clear").Select
Selection.ClearContents
Application.Goto Reference:="formats"
Selection.Copy
Range("clear").Select
Selection.PasteSpecial Paste:=xlPasteFormats,
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("LTOB").Select
Range("A2:B2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Print").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.End(xlDown).Select
cellloc = ActiveCell.Row
Range("A" & cellloc + 1).Select
Sheets("LtrOC").Select
Range("A1").Select
Range("A2:B2").Activate
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Print").Select
ActiveSheet.Paste
Application.Goto Reference:="lookups"
Selection.Copy
Range("A2").Select
Selection.End(xlDown).Select
*** cellloc2 = ActiveCell.Row
Range("C2:C" & cellloc2).Select
ActiveSheet.Paste
Range("C2").Select
Range("A1:R" & cellloc2).Sort Key1:=Range("A2"),
Order1:=xlAscending, Key2:=Range _
("B2"), Order2:=xlAscending,
Header:=xlGuess,
OrderCustom:=1, MatchCase _
:=False, Orientation:=xlTopToBottom,
DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal
Selection.Subtotal GroupBy:=1, Function:=xlSum,
TotalList:=Array(7, 8, 9, 10 _
), Replace:=True, PageBreaks:=False,
SummaryBelowData:=True
End Sub
.
.