Performing some data validations.

srosk

Board Regular
Joined
Sep 17, 2018
Messages
132
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

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
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:
Upvote 0
How about
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
 
Upvote 0
I updated Dim z As String, and still get the mismatch error. I wonder what is causing that?

How about
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:
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
Did you try what I suggested in post#3?
 
Upvote 0
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
 
Upvote 0
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""))")
 
Upvote 0

Forum statistics

Threads
1,213,501
Messages
6,114,010
Members
448,543
Latest member
MartinLarkin

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
Back
Top