cyclechris
11-01-2013, 03:25 AM
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.
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.