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
 

Some videos you may like

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
Joined
Jan 15, 2007
Messages
23,780
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
Joined
Jun 12, 2014
Messages
46,340
Office Version
  1. 365
Platform
  1. Windows
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
 

srosk

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,340
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

ADVERTISEMENT

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
Joined
Jun 12, 2014
Messages
46,340
Office Version
  1. 365
Platform
  1. Windows
Did you try what I suggested in post#3?
 

srosk

Board Regular
Joined
Sep 17, 2018
Messages
132
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
Joined
Jun 12, 2014
Messages
46,340
Office Version
  1. 365
Platform
  1. Windows
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""))")
 

Watch MrExcel Video

Forum statistics

Threads
1,109,030
Messages
5,526,341
Members
409,697
Latest member
christopherlewis1620

This Week's Hot Topics

Top