ExcelBanter - HEEEEEEEELP

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - HEEEEEEEELP (https://www.excelbanter.com/excel-programming/333629-heeeeeeeelp.html)

Excel is no longer my friend. I have written some pretty cool code
(albeit of probably a very basic nature) and it sometimes works and
sometimes does not. As far as a basic user such as myself can see there
is absolutely no difference in the circumstances when it works and when
it does not. Is there some sort of cache thing that needs to be cleared
down (and can be through some code) that anyone knows of?

Many thanks in advance.

So are you going to show us your pretty cool code, or keep it a secret?

Sorry- my assumption is based on the fact that the code works sometimes
ergo there's nothing wrong with it but somthing wrong with some cache
or some condition outside the code. Any idea about some sort of cache
thing?

If not I can paste the full code.

Thanks.

OK- it would make sense to put the Run-time error in I guess...
'-2147417848 (80010108)':

Automation error
THe object invoked has disconnected from its clients.

Paha??

Start at the beginning. Post the code.

--

HTH

RP
(remove nothere from the email address if mailing direct)

"solomon_monkey" wrote in message
oups.com...
Sorry- my assumption is based on the fact that the code works sometimes
ergo there's nothing wrong with it but somthing wrong with some cache
or some condition outside the code. Any idea about some sort of cache
thing?

If not I can paste the full code.

Thanks.

Sub CashCheques()
'
'

'
Application.ScreenUpdating = False

result = MsgBox("Did you mean to press the 'CASH & CHEQUES' Button?", _
vbYesNoCancel, "CASH & CHEQUES")
If result = vbYes Then
GoTo continue
Else
MsgBox "'CASH & CHEQUES' cancelled", vbOKOnly, "CASH & CHEQUES"
GoTo exit1
End If

continue:

Sheets("Monthly Receipts").Copy After:=Sheets(2)
ActiveSheet.Unprotect

Range("A9:E9").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1
Selection.AutoFilter
Selection.AutoFilter

Dim num As Long

Selection.AutoFilter Field:=5, Criteria1:="="
ActiveSheet.AutoFilter.Range.Select
Range(Selection, Selection.End(xlDown)).Select
Selection.EntireRow.Delete

If Range("B9") = 0 Then
MsgBox "No Cash transactions this period", vbOKOnly, "Cash"
GoTo continue1
Else
End If

Range("A9:E9").Select

If Range("B10") = 0 Then
GoTo continue2
Else
Range(Selection, Selection.End(xlDown)).Select
End If

continue2:

Selection.Copy
Sheets("Cash").Select
Range("A9").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

MsgBox Range("C4") & " Cash transactions this period", vbOKOnly, "Cash"

continue1:

Application.DisplayAlerts = False
Sheets("Monthly Receipts (2)").Delete
Application.DisplayAlerts = True

Sheets("Cash").Select

Dim rng As Range
Set rng = Range("A7").End(xlDown)

If rng = Range("A8") Then
rng.Offset(1, 0).Select
Else
rng.Offset(0, 6).Select
End If

Sheets("Monthly Receipts").Copy After:=Sheets(3)
ActiveSheet.Unprotect

Range("A9:E9").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1
Selection.AutoFilter
Columns("E:E").Delete
Selection.AutoFilter

'HERE

Selection.AutoFilter Field:=5, Criteria1:="="
ActiveSheet.AutoFilter.Range.Select
Range(Selection, Selection.End(xlDown)).Select
Selection.EntireRow.Delete

If Range("B9") = 0 Then
MsgBox "No Cheque transactions this period", vbOKOnly, "Cheques"
GoTo continue3
Else
End If

Range("A9:E9").Select

If Range("B10") = 0 Then
GoTo continue4
Else
Range(Selection, Selection.End(xlDown)).Select
End If

If Range("B9") = 0 Then
MsgBox "No Cheque transactions this period", vbOKOnly, "Cheques"
GoTo continue3
Else
End If

Range("A9:E9").Select

If Range("B10") = 0 Then
GoTo continue4
Else
Range(Selection, Selection.End(xlDown)).Select
End If

continue4:

Selection.Copy
Sheets("Cheques").Select
Range("A9").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

MsgBox Range("C4") & " Cheque transactions this period", vbOKOnly,
"Cheques"

continue3:

Application.DisplayAlerts = False
Sheets("Monthly Receipts (2)").Delete
Application.DisplayAlerts = True

Sheets("Cheques").Select

Dim rng2 As Range
Set rng2 = Range("A7").End(xlDown)

If rng2 = Range("A8") Then
rng2.Offset(1, 0).Select
Else
rng2.Offset(0, 6).Select
End If

Sheets("Monthly Receipts").Select

exit1:

Sheets("Monthly Receipts").Select

Application.ScreenUpdating = True

End Sub

I have indicated where it runs the error. It is the second time I copy
the same worksheet. I do not understand why it does not always work or
always fail.

Try qualifying all of your ranges by explicitly including the worksheet.
Not doing so is asking for problems.

Tim

--
Tim Williams
Palo Alto, CA

"solomon_monkey" wrote in message
oups.com...
Sub CashCheques()
'
'

'
Application.ScreenUpdating = False

result = MsgBox("Did you mean to press the 'CASH & CHEQUES' Button?", _
vbYesNoCancel, "CASH & CHEQUES")
If result = vbYes Then
GoTo continue
Else
MsgBox "'CASH & CHEQUES' cancelled", vbOKOnly, "CASH & CHEQUES"
GoTo exit1
End If

continue:

Sheets("Monthly Receipts").Copy After:=Sheets(2)
ActiveSheet.Unprotect

Range("A9:E9").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1
Selection.AutoFilter
Selection.AutoFilter

Dim num As Long

Selection.AutoFilter Field:=5, Criteria1:="="
ActiveSheet.AutoFilter.Range.Select
Range(Selection, Selection.End(xlDown)).Select
Selection.EntireRow.Delete

If Range("B9") = 0 Then
MsgBox "No Cash transactions this period", vbOKOnly, "Cash"
GoTo continue1
Else
End If

Range("A9:E9").Select

If Range("B10") = 0 Then
GoTo continue2
Else
Range(Selection, Selection.End(xlDown)).Select
End If

continue2:

Selection.Copy
Sheets("Cash").Select
Range("A9").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

MsgBox Range("C4") & " Cash transactions this period", vbOKOnly, "Cash"

continue1:

Application.DisplayAlerts = False
Sheets("Monthly Receipts (2)").Delete
Application.DisplayAlerts = True

Sheets("Cash").Select

Dim rng As Range
Set rng = Range("A7").End(xlDown)

If rng = Range("A8") Then
rng.Offset(1, 0).Select
Else
rng.Offset(0, 6).Select
End If

Sheets("Monthly Receipts").Copy After:=Sheets(3)
ActiveSheet.Unprotect

Range("A9:E9").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1
Selection.AutoFilter
Columns("E:E").Delete
Selection.AutoFilter

'HERE

Selection.AutoFilter Field:=5, Criteria1:="="
ActiveSheet.AutoFilter.Range.Select
Range(Selection, Selection.End(xlDown)).Select
Selection.EntireRow.Delete

If Range("B9") = 0 Then
MsgBox "No Cheque transactions this period", vbOKOnly, "Cheques"
GoTo continue3
Else
End If

Range("A9:E9").Select

If Range("B10") = 0 Then
GoTo continue4
Else
Range(Selection, Selection.End(xlDown)).Select
End If

If Range("B9") = 0 Then
MsgBox "No Cheque transactions this period", vbOKOnly, "Cheques"
GoTo continue3
Else
End If

Range("A9:E9").Select

If Range("B10") = 0 Then
GoTo continue4
Else
Range(Selection, Selection.End(xlDown)).Select
End If

continue4:

Selection.Copy
Sheets("Cheques").Select
Range("A9").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

MsgBox Range("C4") & " Cheque transactions this period", vbOKOnly,
"Cheques"

continue3:

Application.DisplayAlerts = False
Sheets("Monthly Receipts (2)").Delete
Application.DisplayAlerts = True

Sheets("Cheques").Select

Dim rng2 As Range
Set rng2 = Range("A7").End(xlDown)

If rng2 = Range("A8") Then
rng2.Offset(1, 0).Select
Else
rng2.Offset(0, 6).Select
End If

Sheets("Monthly Receipts").Select

exit1:

Sheets("Monthly Receipts").Select

Application.ScreenUpdating = True

End Sub

I have indicated where it runs the error. It is the second time I copy
the same worksheet. I do not understand why it does not always work or
always fail.

Apologies for the very novice like comment but I don't know what you
are talking about. It sounds like it would solve my problems though.

Dont't write

Range("A9:E9").Select

but

ActiveSheet.Range("A9:E9").Select

etc etc

Please quote the post you're replying to in your own posts - it's difficult
to know what you're replying to otherwise, and you're more likely to get
help if you do.
If you're posting through some kind of web interface (ugh) there's most
likely an option to include this.

Tim

--
Tim Williams
Palo Alto, CA

"solomon_monkey" wrote in message
ups.com...
Apologies for the very novice like comment but I don't know what you
are talking about. It sounds like it would solve my problems though.

Range("whatever")

will always refer to a range in the *active sheet*, so if you write
your
code like this you're making the assumption that the active sheet is
what
you think it is. It's very easy to later modify your code in some way
which
changes the activesheet at some point, so all your subsequent
references get
screwed up.

Always better to be specific when writing code - Excel is not aware of
your
assumptions...

Hmmm, you would have to be pretty obtuse or just plain thick to not
realise that when you change code it may change the position of other
code... fortunately (on this point at least) I am not and had been
through this with a fine F8ing and even now with qualifying the ranges
it will sometimes work and sometimes not work... :-(

Did you ever solve the problem? I have similar problems with my Excel code;
errors are pseudorandom, usually terminating with the error code that Solomon
quotes, but sometimes a different error (depends on what datafiles are fed
into the macro). It seems like a defect in how Excel/VB is tracking what the
active selections are - code breaks always occur at/after cell
selections/copies/pastes/inserts. The error remained the same in Excel 2002,
Excel 2003, and after installation of all updates.

I note that stock11r has a poste (8/12/05) where he notes simlar problems
(same error code).

Does anyone know how we can get some help on this issue? I have written
similar routines in the past and had no problems!

"solomon_monkey" wrote:

Range("whatever")

will always refer to a range in the *active sheet*, so if you write
your
code like this you're making the assumption that the active sheet is
what
you think it is. It's very easy to later modify your code in some way
which
changes the activesheet at some point, so all your subsequent
references get
screwed up.

Always better to be specific when writing code - Excel is not aware of
your
assumptions...

Hmmm, you would have to be pretty obtuse or just plain thick to not
realise that when you change code it may change the position of other
code... fortunately (on this point at least) I am not and had been
through this with a fine F8ing and even now with qualifying the ranges
it will sometimes work and sometimes not work... :-(

If you still have this problem, I think the solution *might* be answered
under the thread titled, "Where else to get help?". See the last post by
Reuel for the summary.
-Reuel

"solomon_monkey" wrote:

Range("whatever")

will always refer to a range in the *active sheet*, so if you write
your
code like this you're making the assumption that the active sheet is
what
you think it is. It's very easy to later modify your code in some way
which
changes the activesheet at some point, so all your subsequent
references get
screwed up.

Always better to be specific when writing code - Excel is not aware of
your
assumptions...

Hmmm, you would have to be pretty obtuse or just plain thick to not
realise that when you change code it may change the position of other
code... fortunately (on this point at least) I am not and had been
through this with a fine F8ing and even now with qualifying the ranges
it will sometimes work and sometimes not work... :-(