Please solve this VBA mystery!

[Amit Shanker]

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

 

[Tim Williams]

Try fully qualifying your sheet references with the workbook.

Eg.

With Activeworkbook.Sheets

instead of

With Sheets

 

[Doug Glancy]

Amit,

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

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

hth,

Doug

 

[Jim Cone]

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



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

 

[Amit Shanker]

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