Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sheet metal estimating problem | Excel Discussion (Misc queries) | |||
Can I short worksheets ascending or descending order ? | Excel Worksheet Functions | |||
How do I alpha short worksheets in a workbook: | Excel Worksheet Functions | |||
Short cut for switching between worksheets | Excel Worksheet Functions | |||
Is there a short cut to switch between worksheets? | Excel Worksheet Functions |