Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add New Rows
Can someone tell me why the following code generates the following error:
Dim regTopRow Dim regRows 'OptionVal is a variable that stores the worksheet from another part of the code 'OptoinVal does work Worksheets(OptionVal).Activate regTopRow = ActiveSheet.Row regRows = ActiveSheet.Rows.Count The error that is returned is a: run-time error '438' Object Doesn't support this Property or Method. I am assuming that I don't have the correct 'reference' set. Basically I am just trying to activate the indicated sheet, move to the last record then move back up 3 rows and insert a new row... the last row is a "totals line" that keeps a running total and I want the new records inserted above that record. Thanks for any help. -- Thanks, Gabriel |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add New Rows
With OptionVal
regTopRow = .Row regRows = .Rows.Count End With -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Gabriel" wrote in message ... Can someone tell me why the following code generates the following error: Dim regTopRow Dim regRows 'OptionVal is a variable that stores the worksheet from another part of the code 'OptoinVal does work Worksheets(OptionVal).Activate regTopRow = ActiveSheet.Row regRows = ActiveSheet.Rows.Count The error that is returned is a: run-time error '438' Object Doesn't support this Property or Method. I am assuming that I don't have the correct 'reference' set. Basically I am just trying to activate the indicated sheet, move to the last record then move back up 3 rows and insert a new row... the last row is a "totals line" that keeps a running total and I want the new records inserted above that record. Thanks for any help. -- Thanks, Gabriel |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add New Rows
Bob,
Thanks for the reply, but the code you wrote efectively does the same thing as mine. It still generates the run-time error. -- Thanks, Gabriel "Bob Phillips" wrote: With OptionVal regTopRow = .Row regRows = .Rows.Count End With -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Gabriel" wrote in message ... Can someone tell me why the following code generates the following error: Dim regTopRow Dim regRows 'OptionVal is a variable that stores the worksheet from another part of the code 'OptoinVal does work Worksheets(OptionVal).Activate regTopRow = ActiveSheet.Row regRows = ActiveSheet.Rows.Count The error that is returned is a: run-time error '438' Object Doesn't support this Property or Method. I am assuming that I don't have the correct 'reference' set. Basically I am just trying to activate the indicated sheet, move to the last record then move back up 3 rows and insert a new row... the last row is a "totals line" that keeps a running total and I want the new records inserted above that record. Thanks for any help. -- Thanks, Gabriel |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add New Rows
The problem is in understanding what exactly OptionVal is declared as and
what it is set to. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Gabriel" wrote in message ... Bob, Thanks for the reply, but the code you wrote efectively does the same thing as mine. It still generates the run-time error. -- Thanks, Gabriel "Bob Phillips" wrote: With OptionVal regTopRow = .Row regRows = .Rows.Count End With -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Gabriel" wrote in message ... Can someone tell me why the following code generates the following error: Dim regTopRow Dim regRows 'OptionVal is a variable that stores the worksheet from another part of the code 'OptoinVal does work Worksheets(OptionVal).Activate regTopRow = ActiveSheet.Row regRows = ActiveSheet.Rows.Count The error that is returned is a: run-time error '438' Object Doesn't support this Property or Method. I am assuming that I don't have the correct 'reference' set. Basically I am just trying to activate the indicated sheet, move to the last record then move back up 3 rows and insert a new row... the last row is a "totals line" that keeps a running total and I want the new records inserted above that record. Thanks for any help. -- Thanks, Gabriel |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add New Rows
Dim regTopRow as Long
Dim regRows as Long 'OptionVal is a variable that stores the worksheet from another part of the code 'OptoinVal does work Worksheets(OptionVal).Activate regTopRow = ActiveSheet.UsedRange(1).Row regRows = ActiveSheet.UsedRange.Rows.Count Activesheet.Row is your culprit. That isn't a valid command Activesheet.Rows.count always will return 65536 in Excel 97 to 2007, 16384 in earlier versions. Activesheet.UsedRange.Rows.Count will give you the number of rows that Excel considers to be in use. If that isn't what you want, then post back. -- Regards, Tom Ogilvy "Gabriel" wrote in message ... Bob, Thanks for the reply, but the code you wrote efectively does the same thing as mine. It still generates the run-time error. -- Thanks, Gabriel "Bob Phillips" wrote: With OptionVal regTopRow = .Row regRows = .Rows.Count End With -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Gabriel" wrote in message ... Can someone tell me why the following code generates the following error: Dim regTopRow Dim regRows 'OptionVal is a variable that stores the worksheet from another part of the code 'OptoinVal does work Worksheets(OptionVal).Activate regTopRow = ActiveSheet.Row regRows = ActiveSheet.Rows.Count The error that is returned is a: run-time error '438' Object Doesn't support this Property or Method. I am assuming that I don't have the correct 'reference' set. Basically I am just trying to activate the indicated sheet, move to the last record then move back up 3 rows and insert a new row... the last row is a "totals line" that keeps a running total and I want the new records inserted above that record. Thanks for any help. -- Thanks, Gabriel |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add New Rows
Activesheet.Rows.count will be about a million in xl2007.
Tom Ogilvy wrote: Dim regTopRow as Long Dim regRows as Long 'OptionVal is a variable that stores the worksheet from another part of the code 'OptoinVal does work Worksheets(OptionVal).Activate regTopRow = ActiveSheet.UsedRange(1).Row regRows = ActiveSheet.UsedRange.Rows.Count Activesheet.Row is your culprit. That isn't a valid command Activesheet.Rows.count always will return 65536 in Excel 97 to 2007, 16384 in earlier versions. Activesheet.UsedRange.Rows.Count will give you the number of rows that Excel considers to be in use. If that isn't what you want, then post back. -- Regards, Tom Ogilvy "Gabriel" wrote in message ... Bob, Thanks for the reply, but the code you wrote efectively does the same thing as mine. It still generates the run-time error. -- Thanks, Gabriel "Bob Phillips" wrote: With OptionVal regTopRow = .Row regRows = .Rows.Count End With -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Gabriel" wrote in message ... Can someone tell me why the following code generates the following error: Dim regTopRow Dim regRows 'OptionVal is a variable that stores the worksheet from another part of the code 'OptoinVal does work Worksheets(OptionVal).Activate regTopRow = ActiveSheet.Row regRows = ActiveSheet.Rows.Count The error that is returned is a: run-time error '438' Object Doesn't support this Property or Method. I am assuming that I don't have the correct 'reference' set. Basically I am just trying to activate the indicated sheet, move to the last record then move back up 3 rows and insert a new row... the last row is a "totals line" that keeps a running total and I want the new records inserted above that record. Thanks for any help. -- Thanks, Gabriel -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add New Rows
More rows, more columns and from what I hear a slower calc engine, Whoo
Hoo!! -- Regards, Tom Ogilvy "Dave Peterson" wrote in message ... Activesheet.Rows.count will be about a million in xl2007. Tom Ogilvy wrote: Dim regTopRow as Long Dim regRows as Long 'OptionVal is a variable that stores the worksheet from another part of the code 'OptoinVal does work Worksheets(OptionVal).Activate regTopRow = ActiveSheet.UsedRange(1).Row regRows = ActiveSheet.UsedRange.Rows.Count Activesheet.Row is your culprit. That isn't a valid command Activesheet.Rows.count always will return 65536 in Excel 97 to 2007, 16384 in earlier versions. Activesheet.UsedRange.Rows.Count will give you the number of rows that Excel considers to be in use. If that isn't what you want, then post back. -- Regards, Tom Ogilvy "Gabriel" wrote in message ... Bob, Thanks for the reply, but the code you wrote efectively does the same thing as mine. It still generates the run-time error. -- Thanks, Gabriel "Bob Phillips" wrote: With OptionVal regTopRow = .Row regRows = .Rows.Count End With -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Gabriel" wrote in message ... Can someone tell me why the following code generates the following error: Dim regTopRow Dim regRows 'OptionVal is a variable that stores the worksheet from another part of the code 'OptoinVal does work Worksheets(OptionVal).Activate regTopRow = ActiveSheet.Row regRows = ActiveSheet.Rows.Count The error that is returned is a: run-time error '438' Object Doesn't support this Property or Method. I am assuming that I don't have the correct 'reference' set. Basically I am just trying to activate the indicated sheet, move to the last record then move back up 3 rows and insert a new row... the last row is a "totals line" that keeps a running total and I want the new records inserted above that record. Thanks for any help. -- Thanks, Gabriel -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
colating multi rows of data into single rows - no to pivot tables! | Excel Worksheet Functions | |||
Auto extract data & inserts rows additional rows automatically | Excel Discussion (Misc queries) | |||
Enabling option „Format rows“ to hide/unhide rows using VBA-code? | Excel Discussion (Misc queries) | |||
Excel 2003 -Rows hidden. Scrolling unhides rows ! How do I stop th | Excel Discussion (Misc queries) | |||
Pivot Tables: How do I show ALL field rows, including empty rows?? | Excel Worksheet Functions |