ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Another runtime 1004 error (https://www.excelbanter.com/excel-discussion-misc-queries/45880-another-runtime-1004-error.html)

[email protected]

Another runtime 1004 error
 
Hello all,

I am using an Excel 2003 sheet from a company. When ever I choose a
cell to answer a yes/no question, I get the following error.

"Runtime error 1004"

application-defined or object defined error"

When I hit debug, it shows me the place in the code causing the problem

(this is just part of the code)
' initialise tallies
noRed = 0
noYellow = 0
noGreen = 0
noNeutral = 0

For Each thisRow In Range(sheet & "_Descriptions").Cells
' for each question

' If level 3 set the score cell
If (thisRow.Cells(1, colDescription).Interior.Color = ilevel3) Then
' remember the cell
Set L3Cell = thisRow.Cells(1, colScore)
' Restore original colour in case it had been highlighted
L3Cell.Interior.Color = ilevel3

End If

With thisRow.Cells(1, colScore).Interior
' tally up cells in each of the three colours

If (.Color = iRed) Then
noRed = noRed + 1
' and set the Level3 score cell to highlight
L3Cell.Interior.Color = ihighlight

ElseIf (.Color = iYellow) Then
noYellow = noYellow + 1
ElseIf (.Color = iGreen) Then
noGreen = noGreen + 1
ElseIf (.Color = iNeutral) Then
noNeutral = noNeutral + 1
End If
End With

Next

' Put tallies in the sheet's tally cells
Range(sheet & "_Reject").Value = noRed
Range(sheet & "_Caution").Value = noYellow
Range(sheet & "_Accept").Value = noGreen
Range(sheet & "_Unanswered").Value = noNeutral <---- **it shows
error here**

End Sub

I do not know how to write macros, so I have no idea what is wrong. I
am just the end user. It does this everytime I try to answer a question
in the spreadsheet. The sheet tabulates a score as I answer each
question.
If I delete the offending line of code, the error goes away, but then I
have no idea how I have effected the macro.

any help would be greatly appreciated.

thanks
Paulbill


[email protected]

BTW the spreadsheet/macro was written using Excel 2002


Bob Phillips

It suggests that you don't have a range named sheet & "_Unanswered".

As you profess no ability with VBA you might struggle to resolve it. If you
like you could send it to me and I could look at it for you.

--
HTH

Bob Phillips

wrote in message
ups.com...
BTW the spreadsheet/macro was written using Excel 2002





All times are GMT +1. The time now is 08:22 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com