Type mismatch error

[moneytastesbad]

I have a similer situation that I am having trouble with.

I am running a macro from a form button. Its basic purpose is to select another worksheet. However, I want it to varify that there is a value within a range of cells on another worksheet.

The worksheets are titled:

"Steps" - This is where the button that runs the macro is located
"Menu" - This is the sheet that I want to check for a value
"GenCharts" - This is the sheet that I want to go to if there is a value within the range.

The range that I am checking is "B11:B5010" in the sheet "Menu"
The value can be anything, and can be located in any cell within the range.

The sheet "Menu" is a list of tasks that the end user can choose from to display in a chart. As long as at least one task is selected then I want the macro to proceed to the "GenCharts" sheet. If no tasks are selected I want it to display an error message stating that they need to choose a task before proceding.

I have added a formula to "Menu A11:A5010" that checks for a value in the corosponding cell in column B. If there is a value there then the cell in Column A = 1, if no value the cell = 0

I also have a formula in cell A1 that SUMs the values in A11:A5010

Here is the code that I am using:

Sub GenCharts()

If Sheets("Menu").Cells("A1") > 0 Then
Sheets("GenCharts").Select

Else
MsgBox "You have not selected any tasks to display."
End If



End Sub

The code stops on the IF line and gives me a type mismatch error.


What am I doing wrong?


Thanks,

Travis

 

[Von Pookie]

The way you have reference the cell, you should be using the Range property, not Cells:

Sheets("Menu").Range("A1")

 

[moneytastesbad]

Thank you very, very much.

I should have posted this question hours ago!!!!

 

[moneytastesbad]

One more question...


Is there any way to check for a value in the range (B11:B5010) with out doing all the other steps.

eg. some code or formula that will search a range for any value in any cell with in the range?

 

[Von Pookie]

I have added a formula to "Menu A11:A5010" that checks for a value in the corosponding cell in column B. If there is a value there then the cell in Column A = 1, if no value the cell = 0
...
Is there any way to check for a value in the range (B11:B5010) with out doing all the other steps.

eg. some code or formula that will search a range for any value in any cell with in the range?

Well--if you simply want to see if there are values anywhere within that range, you could use the COUNTA worksheet function. It counts the number of non-blank cells within the range--formulas in the cell that appear blank as a result ("") will throw it off, however.

Maybe:

If Application.CountA(Sheets("Menu").Range("B11:B5010")) > 0 Then
    Sheets("GenCharts").Select
Else
    MsgBox "You have not selected any tasks to display."
End If