Baffling problem inserting Formula into Range, error 1004

[cyclechris]

Hello, I have a macro that steps through sheets in a workbook and inserts values and formulas to certain cells. The wosrksheet is fully referenced and in one part of he code the insertion works fine yet in another part it fails with this error:

error 1004 Method 'Range' of object '_Worksheet' failed

Here's some code, any ideas? This really has me stumped! :banghead:

Private Sub writeSheet(count As Long, sheet As String)
Dim i As Long
Dim j As Long
Dim siteNo As Long
siteNo = Application.Match(sheet, getSites, False) - 1
Dim ws As Worksheet
Set ws = Worksheets(sheet)
Dim szCell As String

For i = 0 To UBound(arrQtys)
j = i + 5
szCell = "K" & j
ws.Range(szCell).Value = arrQtys(i)
ws.Range(szCell).Interior.Color = 13561798
ws.Range(szCell).Font.Color = 24832
ws.Range(szCell).HorizontalAlignment = xlCenter
Next i

For i = 0 To UBound(arrQtys)
j = i + 5
szCell = "L" & j
ws.Range(szCell).Value = 48
ws.Range(szCell).Interior.Color = 13561798
ws.Range(szCell).Font.Color = 24832
ws.Range(szCell).HorizontalAlignment = xlCenter
ws.Range(szCell).Select
Selection.NumberFormat = "0"
Next i

For i = 0 To UBound(arrQtys)
j = i + 5
szCell = "N" & j
szFormula = "='Power Specs'!C" & (j - 3)
ws.Range(szCell).Formula = szFormula
ws.Range(szCell).Interior.Color = 13561798
ws.Range(szCell).Font.Color = 24832
ws.Range(szCell).HorizontalAlignment = xlCenter
ws.Range(szCell).Select
Selection.NumberFormat = "0.0"
Next i

For i = 0 To UBound(arrQtys)
j = i + 5
szCell = "0" & j
szFormula = "=Product(N" & j & "," & "L" & j & ")"
ws.Range(szCell).Formula = szFormula ' <---- THIS IS WHERE IT CHOKES
ws.Range(szCell).Interior.Color = 13561798
ws.Range(szCell).Font.Color = 24832
ws.Range(szCell).HorizontalAlignment = xlCenter
ws.Range(szCell).Select
Selection.NumberFormat = "0.0"
Next i
End sub

The formula itself looks fine when I add a watch, say "=Product(N5,L5)"
AND... I use the fully qualified ws, both sheet and count are passed into the function fine/correctly

This kind of thing gets me, something that seems simple, especially it had worked in the prior loops! just cruising along and then BAM!

Any help and/or thoughts will really be appreciated!

... The entire module is about 600 lines, that wouldn't have anything to do with it would it? In past when my modules got too big, I think I'd get a compile error.

[Aflatoon]

szCell = "0" & j

That's a zero, not an O. :)

And that's why I use Consolas as the font for the VBE - zeroes show up with a line through them so you can easily see what's an O and what's a 0.

[cyclechris]

you.... totally.... ROCK! :cool:

Thank you SO much! Wow that totally slipped past me, an extra pair of eyes is so helpful! Wow! Thanks, you made my morning!