| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select method of Range class failure
With ws
loads of code then 'Locate the end of each page and insert the Collection formulae For j = 1 To UBound(Pagevarr) - 1 StartToCollectionRow = Pagevarr(j).Offset(1, 6).Row EndToCollectionRow = Pagevarr(j + 1).Offset(-3, 6).Row Pagevarr(j + 1).Offset(-1, 7).Formula = "=sum(H" & _ StartToCollectionRow & ":H" & EndToCollectionRow & ")" Next which works fine and them immediately after: 'Transfer the page totals to the Collection page Pagevarr(j).Offset(1, 1).End(xlDown).Select If Not (Pagevarr(j).Offset(1, 1).End(xlDown).Value = "COLLECTION" Or _ ActiveCell.Value = "COLLECTION (Cont.)" Or _ ActiveCell.Value = "GENERAL SUMMARY") Then MsgBox "There is a missing COLLECTION in: " & ws.Name & "..." & _ ActiveCell.Address GoTo Line5 Else etc The code fails on Pagevarr(j).Offset(1, 1).End(xlDown).Select Why is this, please? Regards. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.593 / Virus Database: 376 - Release Date: 20/02/2004 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Select method of Range class failure
Check the value of j at that point
msgbox j, ubound(pagevarr) msgbox Pagevarr(j).Address msgbox Pagevarr(j).Offset(1,1).End(xldown).Address Pagevarr(j).Offset(1, 1).End(xlDown).Select If Not (Pagevarr(j).Offset(1, 1).End(xlDown).Value = "COLLECTION" Or _ ActiveCell.Value = "COLLECTION (Cont.)" Or _ ActiveCell.Value = "GENERAL SUMMARY") Then MsgBox "There is a missing COLLECTION in: " & ws.Name & "..." & _ ActiveCell.Address GoTo Line5 Else I am not sure I would depend on the value of j being what you would expect it to be or, if it is what you expect it to be, is Pagevarr(j) set to a range. If it is, is the range on the last row or column? -- Regards, Tom Ogilvy "Stuart" wrote in message ... With ws loads of code then 'Locate the end of each page and insert the Collection formulae For j = 1 To UBound(Pagevarr) - 1 StartToCollectionRow = Pagevarr(j).Offset(1, 6).Row EndToCollectionRow = Pagevarr(j + 1).Offset(-3, 6).Row Pagevarr(j + 1).Offset(-1, 7).Formula = "=sum(H" & _ StartToCollectionRow & ":H" & EndToCollectionRow & ")" Next which works fine and them immediately after: 'Transfer the page totals to the Collection page Pagevarr(j).Offset(1, 1).End(xlDown).Select If Not (Pagevarr(j).Offset(1, 1).End(xlDown).Value = "COLLECTION" Or _ ActiveCell.Value = "COLLECTION (Cont.)" Or _ ActiveCell.Value = "GENERAL SUMMARY") Then MsgBox "There is a missing COLLECTION in: " & ws.Name & "..." & _ ActiveCell.Address GoTo Line5 Else etc The code fails on Pagevarr(j).Offset(1, 1).End(xlDown).Select Why is this, please? Regards. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.593 / Virus Database: 376 - Release Date: 20/02/2004 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Select method of Range class failure
Many thanks.
In this case: msgbox j, ubound(pagevarr) returns '4' which is correct msgbox Pagevarr(j).Address returns A145, also correct msgbox Pagevarr(j).Offset(1,1).End(xldown).Address returns B148, also correct. ws is referencing the correct worksheet. Pagevarr(4) is shown as Variant/Object/Range in Locals In this case, I have data down in H200. Regards. "Tom Ogilvy" wrote in message ... Check the value of j at that point msgbox j, ubound(pagevarr) msgbox Pagevarr(j).Address msgbox Pagevarr(j).Offset(1,1).End(xldown).Address Pagevarr(j).Offset(1, 1).End(xlDown).Select If Not (Pagevarr(j).Offset(1, 1).End(xlDown).Value = "COLLECTION" Or _ ActiveCell.Value = "COLLECTION (Cont.)" Or _ ActiveCell.Value = "GENERAL SUMMARY") Then MsgBox "There is a missing COLLECTION in: " & ws.Name & "..." & _ ActiveCell.Address GoTo Line5 Else I am not sure I would depend on the value of j being what you would expect it to be or, if it is what you expect it to be, is Pagevarr(j) set to a range. If it is, is the range on the last row or column? -- Regards, Tom Ogilvy "Stuart" wrote in message ... With ws loads of code then 'Locate the end of each page and insert the Collection formulae For j = 1 To UBound(Pagevarr) - 1 StartToCollectionRow = Pagevarr(j).Offset(1, 6).Row EndToCollectionRow = Pagevarr(j + 1).Offset(-3, 6).Row Pagevarr(j + 1).Offset(-1, 7).Formula = "=sum(H" & _ StartToCollectionRow & ":H" & EndToCollectionRow & ")" Next which works fine and them immediately after: 'Transfer the page totals to the Collection page Pagevarr(j).Offset(1, 1).End(xlDown).Select If Not (Pagevarr(j).Offset(1, 1).End(xlDown).Value = "COLLECTION" Or _ ActiveCell.Value = "COLLECTION (Cont.)" Or _ ActiveCell.Value = "GENERAL SUMMARY") Then MsgBox "There is a missing COLLECTION in: " & ws.Name & "..." & _ ActiveCell.Address GoTo Line5 Else etc The code fails on Pagevarr(j).Offset(1, 1).End(xlDown).Select Why is this, please? Regards. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.593 / Virus Database: 376 - Release Date: 20/02/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.593 / Virus Database: 376 - Release Date: 20/02/2004 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Select method of Range class failure
Don't know if this will shed any light on my errors, but this
alteration appears to work: 'TEST CODE omitting the Select statement If Not (Pagevarr(j).Offset(1, 1).End(xlDown).Value = "COLLECTION" Or _ Pagevarr(j).Offset(1, 1).End(xlDown).Value = _ "COLLECTION (Cont.)" Or Pagevarr(j).Offset(1, 1).End(xlDown) _ Value = "GENERAL SUMMARY") Then MsgBox "There is a missing COLLECTION in: " & ws.Name & "..." & _ Pagevarr(j).Offset(1, 1).End(xlDown).Address GoTo Line5 Else Set Target = Pagevarr(j).Offset(1, 1).End(xlDown).End(xlDown) _ .Offset(0, 6) For j = 1 To UBound(Pagevarr) - 1 Target.Value = Pagevarr(j + 1).Offset(-1, 7).Value Set Target = Target.Offset(2, 0) Next End If 'Set the formula for the COLLECTION page StartToCollectionRow = Pagevarr(j).Offset(1, 6).Row EndToCollectionRow = .Range("F65536").End(xlUp).Offset(-2, 0).Row ..Range("H" & EndToCollectionRow + 2).Value = "=sum(H" _ & StartToCollectionRow & ":H" & EndToCollectionRow & ")" Regards. "Tom Ogilvy" wrote in message ... Check the value of j at that point msgbox j, ubound(pagevarr) msgbox Pagevarr(j).Address msgbox Pagevarr(j).Offset(1,1).End(xldown).Address Pagevarr(j).Offset(1, 1).End(xlDown).Select If Not (Pagevarr(j).Offset(1, 1).End(xlDown).Value = "COLLECTION" Or _ ActiveCell.Value = "COLLECTION (Cont.)" Or _ ActiveCell.Value = "GENERAL SUMMARY") Then MsgBox "There is a missing COLLECTION in: " & ws.Name & "..." & _ ActiveCell.Address GoTo Line5 Else I am not sure I would depend on the value of j being what you would expect it to be or, if it is what you expect it to be, is Pagevarr(j) set to a range. If it is, is the range on the last row or column? -- Regards, Tom Ogilvy "Stuart" wrote in message ... With ws loads of code then 'Locate the end of each page and insert the Collection formulae For j = 1 To UBound(Pagevarr) - 1 StartToCollectionRow = Pagevarr(j).Offset(1, 6).Row EndToCollectionRow = Pagevarr(j + 1).Offset(-3, 6).Row Pagevarr(j + 1).Offset(-1, 7).Formula = "=sum(H" & _ StartToCollectionRow & ":H" & EndToCollectionRow & ")" Next which works fine and them immediately after: 'Transfer the page totals to the Collection page Pagevarr(j).Offset(1, 1).End(xlDown).Select If Not (Pagevarr(j).Offset(1, 1).End(xlDown).Value = "COLLECTION" Or _ ActiveCell.Value = "COLLECTION (Cont.)" Or _ ActiveCell.Value = "GENERAL SUMMARY") Then MsgBox "There is a missing COLLECTION in: " & ws.Name & "..." & _ ActiveCell.Address GoTo Line5 Else etc The code fails on Pagevarr(j).Offset(1, 1).End(xlDown).Select Why is this, please? Regards. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.593 / Virus Database: 376 - Release Date: 20/02/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.593 / Virus Database: 376 - Release Date: 20/02/2004 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| "Select method of Range class failed" Error | Excel Discussion (Misc queries) | |||
| Run-Time error '1004' : Select method of Range class failed | Excel Discussion (Misc queries) | |||
| Run-time error "1004" Select method of range class failed | Excel Discussion (Misc queries) | |||
| excel97: runtime error 1004 select method of range class failed | Excel Programming | |||
| error 1004 Select method of Range class failed | Excel Programming | |||
![Stuart[_5_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
