A code problem with short Estimating worksheets
 

This is the problem code sequence:

Range("E" & EndToCollectionRow).Formula = _
"=sum(E" & StartToCollectionRow & ":E" & EndToCollectionRow - 1 & ")"
Range("F" & EndToCollectionRow).Formula = _
"=sum(F" & StartToCollectionRow & ":F" & EndToCollectionRow - 1 & ")"
Range("G" & EndToCollectionRow).Formula = _
"=sum(G" & StartToCollectionRow & ":G" & EndToCollectionRow - 1 & ")"
etc
Range("O" & EndToCollectionRow + 1).Formula = _
"=sum(O" & StartToCollectionRow & ":O" & EndToCollectionRow - 1 & ")"

An error is generated on the line ...Range("O..... ) etc, with the
message
Method Range of Object_Global failed.

EndToCollectionRow holds the value 65536, so hence the error.

Working back through the routine, this is the code that sets the variable
EndToCollectionRow:

TargetCell = Columns(ActiveCell.Column).Find(What:="£", After:=ActiveCell, _
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False).Select
EndToCollectionRow = ActiveCell.Row

For some reason, even though there is a later '£' value, the code 'misses'
it
and finds 65536.

Can anyone help, or would anyone be prepared to accept a small
attachment with a complete data/code example, please?

Regards.



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.501 / Virus Database: 299 - Release Date: 14/07/2003

A code problem with short Estimating worksheets
 

would be happy to look at the attachment... suspect it can be done an easier
way.

Mark D

"Stuart" wrote in message
...
This is the problem code sequence:

Range("E" & EndToCollectionRow).Formula = _
"=sum(E" & StartToCollectionRow & ":E" & EndToCollectionRow - 1 & ")"
Range("F" & EndToCollectionRow).Formula = _
"=sum(F" & StartToCollectionRow & ":F" & EndToCollectionRow - 1 & ")"
Range("G" & EndToCollectionRow).Formula = _
"=sum(G" & StartToCollectionRow & ":G" & EndToCollectionRow - 1 & ")"
etc
Range("O" & EndToCollectionRow + 1).Formula = _
"=sum(O" & StartToCollectionRow & ":O" & EndToCollectionRow - 1 & ")"

An error is generated on the line ...Range("O..... ) etc, with the
message
Method Range of Object_Global failed.

EndToCollectionRow holds the value 65536, so hence the error.

Working back through the routine, this is the code that sets the variable
EndToCollectionRow:

TargetCell = Columns(ActiveCell.Column).Find(What:="£", After:=ActiveCell,
_
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False).Select
EndToCollectionRow = ActiveCell.Row

For some reason, even though there is a later '£' value, the code 'misses'
it
and finds 65536.

Can anyone help, or would anyone be prepared to accept a small
attachment with a complete data/code example, please?

Regards.



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.501 / Virus Database: 299 - Release Date: 14/07/2003