# Performing some data validations.

#### srosk

##### Board Regular
Hi there. I am performing data validations on a sheet, and created the following code. I created a variable 'z', and added a formula that works. When I run the code in the debugger, I get a type mismatch error.

Expanding on this a little, I would like to add vbyesno to the MsgBox, and if no, end sub. Is it possible to essentially nest another if statement in here?

Thank you kindly Code:
``````Dim z As Integer

z = "SUM(COUNTIF('121'!D:J,""<-50"")+COUNTIF('121'!D:J,"">50""))"

If z > 1 Then
MsgBox ("There are " & z & " strings greater than 50. Is this valid?")

End If``````

### Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the \$ sign).

#### mikerickson

##### MrExcel MVP
z is dimensioned as an Integer and the next line assigns a string value to it.

Change to
Code:
``Dim z As String``

As to the other question

Code:
``If MsgBox("There are " & z & " strings greater than 50. Is this valid?", vbYesNo) = vbNo then Exit Sub``

Last edited:

#### Fluff

##### MrExcel MVP, Moderator
Code:
``````   Dim z As Long

z = Evaluate("SUM(COUNTIF('121'!D:J,""<-50"")+COUNTIF('121'!D:J,"">50""))")

If z > 1 Then
If MsgBox("There are " & z & " strings greater than 50. Is this valid?", vbYesNo) = vbNo Then Exit Sub
End If``````

#### srosk

##### Board Regular
I updated Dim z As String, and still get the mismatch error. I wonder what is causing that?

Code:
``````   Dim z As Long

z = Evaluate("SUM(COUNTIF('121'!D:J,""<-50"")+COUNTIF('121'!D:J,"">50""))")

If z > 1 Then
If MsgBox("There are " & z & " strings greater than 50. Is this valid?", vbYesNo) = vbNo Then Exit Sub
End If``````

Last edited:

#### Fluff

##### MrExcel MVP, Moderator

You get that message because
a) You did not post your entire code
b) You did not mention that it was a Function, rather than a sub
c) You wanted to End Sub if no was clicked on the msgbox.

To resolve the problem change Exit Sub to Exit Function

Last edited:

#### srosk

##### Board Regular
I realized my error with that, my apologies.

With that said, I actually want it to end the Sub, not specifically the function that the validation code was a part of. With that said, I think I will have to transfer this out of its own function and into the larger part of code. I'm learning.. slowly.

#### srosk

##### Board Regular

With all of that said, any idea why I can't get the right type here? Is it because of the formula? For testing, I made z=2, and ran the function and the prompts worked fine... so maybe it has to me using a formula in there?

Code:
``````Dim z As String

z = "SUM(C``````

#### Fluff

##### MrExcel MVP, Moderator
Did you try what I suggested in post#3?

#### srosk

##### Board Regular
Did you try what I suggested in post#3?

Sorry, yes I did. I've tried, string, long, integer, etc. I've been googling all possible options to no avail. I know one option is to put the formula in a cell, copy paste value, and reference that instead of the variable Z, but was trying to avoid that, if possible.

Code:
``````Function DataValidations()

Dim z As Long

z = "SUM(COUNTIF``````

#### Fluff

##### MrExcel MVP, Moderator
What you have just posted is not what I suggested.
Code:
``````Dim z As Long

z = Evaluate("SUM(COUNTIF('121'!D:J,""<-50"")+COUNTIF('121'!D:J,"">50""))")``````

• srosk

Replies
5
Views
78
Replies
4
Views
63
Replies
0
Views
157
Replies
17
Views
154
Replies
12
Views
127