Hello wise folks,

With WinXP Pro SP2 and Office 2003 SP1, I have written following code
that works (watch out for line wraps):

Option Explicit
Option Base 1

Sub Copy_Values_To_Billing_Sheet()

Dim firstCell As Range
Dim lastCell As Range
Dim i As Long
Dim myCounter As Long
Dim myCopyArray As Variant

myCounter = 3
i = 0

Application.ScreenUpdating = False

With Sheets("Client")
Set firstCell = Range("C" & Rows.Count).End(xlUp).Offset(1, 0)
Set lastCell = Range("C3")
For i = firstCell.Row To lastCell.Row Step -1
If Cells(i, firstCell.Column).Interior.ColorIndex = 15 Then
myCounter = myCounter + 2
With Cells(i, firstCell.Column)
myCopyArray = Array(.Offset(-1, 0), .Offset(-1, -1),
..Offset(0, 4), .Offset(0, 6))
End With
End If
Debug.Print myCopyArray(1), myCopyArray(2), myCopyArray(3),
myCopyArray(4)
Sheets("monthly billing").Cells(5, myCounter) = myCopyArray
(1)
Sheets("monthly billing").Cells(6, myCounter) = myCopyArray
(2)
Sheets("monthly billing").Cells(14, myCounter + 1) =
myCopyArray(3)
Sheets("monthly billing").Cells(27, myCounter) = myCopyArray
(4)
Next i
End With

Set firstCell = Nothing
Set lastCell = Nothing
Set myCopyArray = Nothing

Application.ScreenUpdating = True

End Sub


The code above works perfectly - when it does work, that is (and that is
the mystery). What happens is that suddenly, for no apparent reason, when
I try to simply re-run my code, it will throw up a Runtime 13 error
('Type mismatch') with the offending line in yellow being "Sheets
("monthly billing").Cells(5, myCounter) = myCopyArray(1)" above.

Through trial, I have found that if I now go and activate Sheets
("Client") and then simply click once anywhere in this sheet to *change*
the existing activecell on this sheet, and then go back to re-run my
code, it runs perfectly once again!! This solution has worked every
single time when this 'mystery' error occurs.

FYI, I have cleaned up the code detritus using the excellent Code Cleaner
add-in, but the fact is that even after doing this, this mystery error
still occurs suddenly for no apparent reason. Can anyone throw some light
on this behaviour please?

Thanks,
Amit