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

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

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

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

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

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

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