A code problem with short Estimating worksheets

  • Thread starter Thread starter Stuart
  • Start date Start date
S

Stuart

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.
 
Back
Top