Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Performing some data validations.

  1. #1
    Board Regular
    Join Date
    Sep 2018
    Posts
    132
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Performing some data validations.

    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

  2. #2
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    22,590
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    15 Thread(s)

    Default Re: Performing some data validations.

    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 by mikerickson; Mar 11th, 2019 at 10:55 AM.

  3. #3
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,173
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Performing some data validations.

    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
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  4. #4
    Board Regular
    Join Date
    Sep 2018
    Posts
    132
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Performing some data validations.

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

    Quote Originally Posted by Fluff View Post
    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 by srosk; Mar 11th, 2019 at 12:00 PM.

  5. #5
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,173
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Performing some data validations.

    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 by Fluff; Mar 11th, 2019 at 12:02 PM.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  6. #6
    Board Regular
    Join Date
    Sep 2018
    Posts
    132
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Performing some data validations.

    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.

  7. #7
    Board Regular
    Join Date
    Sep 2018
    Posts
    132
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Performing some data validations.

    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

  8. #8
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,173
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Performing some data validations.

    Did you try what I suggested in post#3?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  9. #9
    Board Regular
    Join Date
    Sep 2018
    Posts
    132
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Performing some data validations.

    Quote Originally Posted by Fluff View Post
    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

  10. #10
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,173
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Performing some data validations.

    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""))")
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •