Please solve this VBA mystery!

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

Try fully qualifying your sheet references with the workbook.

Eg.

With Activeworkbook.Sheets

instead of

With Sheets

--
Tim Williams
Palo Alto, CA


"Amit Shanker" <amitshanker@netscapeNOSPAM.net> wrote in message news:Xns97A4EE2B7A2D7shnkntscp@207.46.248.16...
> 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
>

Amit,

To add to Tim, also qualify the references within your With statement, e.g,:

Set lastCell = .Range("C3") 'period before "Range"

hth,

Doug

"Tim Williams" <timjwilliams at gmail dot com> wrote in message
news:e%23trt20XGHA.4324@TK2MSFTNGP03.phx.gbl...
> Try fully qualifying your sheet references with the workbook.
>
> Eg.
>
> With Activeworkbook.Sheets
>
> instead of
>
> With Sheets
>
> --
> Tim Williams
> Palo Alto, CA
>
>
> "Amit Shanker" <amitshanker@netscapeNOSPAM.net> wrote in message
> news:Xns97A4EE2B7A2D7shnkntscp@207.46.248.16...
>> 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
>>
>
>

Amit,
I think you are going to have your best luck by moving "End If"
from above the Debug statement
to just before "Next i"
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"Amit Shanker" wrote in message
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),myCopyArray4)
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

Thanks,
Amit

Thanks all for your suggestions.

Code is now working fine - no more mysterious errors. I think qualifying my
sheets fully and adding the 'dot' before the objects hit the target.

Regards,
Amit