Type mismatch error

moneytastesbad

Board Regular
Joined
Aug 28, 2006
Messages
106
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
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Von Pookie

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
The way you have reference the cell, you should be using the Range property, not Cells:

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

moneytastesbad

Board Regular
Joined
Aug 28, 2006
Messages
106
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

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
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:

Code:
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
 

Forum statistics

Threads
1,137,332
Messages
5,680,875
Members
419,937
Latest member
Talic

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top