Performing some data validations.

[srosk]

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

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

 

[mikerickson]

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

Change to

Dim z As String

As to the other question

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

 

[Fluff]

How about

   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]

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

How about

   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

 

[Fluff]

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

 

[srosk]

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]

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?

Dim z As String

z = "SUM(C

 

[Fluff]

Did you try what I suggested in post#3?

 

[srosk]

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.

Function DataValidations()

Dim z As Long


z = "SUM(COUNTIF

 

[Fluff]

What you have just posted is not what I suggested.

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