none
"Is Not" Assistance RRS feed

  • Question

  • Hello - So I will try to keep this short...anyone have any ideas how or if the below can actually work?

     If Sheets("Employee Change Form").SalaryInc_ChkBx.Value = True And _
                        Sheets("Employee Change Form").Range("M8").Value = Not "Corporate - Accounting" Or _
                        Sheets("Employee Change Form").Range("M8").Value = Not "Corporate - Asset Management" Or _
                        Sheets("Employee Change Form").Range("M8").Value = Not "Corporate - Capital Improvments" Or _
                        Sheets("Employee Change Form").Range("M8").Value = Not "Corporate - Compliance" Or _
                        Sheets("Employee Change Form").Range("M8").Value = Not "Corporate - Construction" Or _
                        Sheets("Employee Change Form").Range("M8").Value = Not "Corporate - Development" Or _
                        Sheets("Employee Change Form").Range("M8").Value = Not "Corporate - Executive Management" Or _
                        Sheets("Employee Change Form").Range("M8").Value = Not "Corporate - General" Or _
                        Sheets("Employee Change Form").Range("M8").Value = Not "Corporate - HOPE Through Housing" Or _
                        Sheets("Employee Change Form").Range("M8").Value = Not "Corporate - Human Resources" Or _
                        Sheets("Employee Change Form").Range("M8").Value = Not "Corporate - IT" Or _
                        Sheets("Employee Change Form").Range("M8").Value = Not "Corporate - Marketing" Or _
                        Sheets("Employee Change Form").Range("M8").Value = Not "Corporate - Purchasing" Then
                        Sheets("Employee Change Form").Range("A30").EntireRow.Hidden = False
    Else
                If Sheets("Employee Change Form").SalaryInc_ChkBx.Value = True Then
                    Sheets("Employee Change Form").Range("A31").EntireRow.Hidden = False
                End If

    End If

    Any ideas will be much appreciated. Thank you for your time :)

    Note: Cell "M8" referred to above is a drop down menu!


    • Edited by rstreets2 Friday, September 1, 2017 7:55 PM Added note
    Friday, September 1, 2017 5:52 PM

Answers

  • Hi rstreets,

    It seems Value in M8 is the value list of A6-A100 and so the value in M8 might be many kinds.

    I think you’d better look for regular rule to handle this. Checking begin word is a good idea.

    For instance, if value in M8 begins with “Corporate”, then do something, if begins with “Personal”, then do other something.

    Dim M8Value As String
    
    M8Value = Range("M8")
    
    If InStr(M8Value, "Corporate") = 1 Then
    
    ' do what you want to do
    
    MsgBox "Value in M8 starts with Corporate"
    
    ElseIf InStr(M8Value, "Personal") = 1 Then
    
    ' do what you want to do
    
    MsgBox "Value in M8 starts with Personal"
    
    End If

    Best Regards,

    Terry

    • Marked as answer by rstreets2 Wednesday, September 6, 2017 7:27 PM
    Wednesday, September 6, 2017 9:19 AM
  • Terry you are wonderful!! Thank you thank you thank you! Your idea sparked an outcome for me that works perfectly. See below :)

    Dim M8Value As String   
    M8Value = Sheets("Employee Change Form").Range("M8")

    If Sheets("Employee Change Form").Transfer_ChkBx.Value = True And _
                    Sheets("Employee Change Form").SalaryInc_ChkBx.Value = True And _
                        InStr(M8Value, "Corporate") = 1 Then
                        Sheets("Employee Change Form").Range("A13").EntireRow.Hidden = True
                        Sheets("Employee Change Form").Range("A14").EntireRow.Hidden = False
                Else
                    Sheets("Employee Change Form").Range("A13").EntireRow.Hidden = False
                    Sheets("Employee Change Form").Range("A14").EntireRow.Hidden = True
                End If

    Thanks again Terry for your time and assistance. This has been most helpful. Thank you thank you thank you!

    • Marked as answer by rstreets2 Wednesday, September 6, 2017 7:27 PM
    Wednesday, September 6, 2017 7:27 PM

All replies

  • rstreets2,
    re: something

    Don't you have a linked cell for the dropdown?
    Saturday, September 2, 2017 2:07 PM
  • Hi rstreets2,

    You could put "Corporate - Accounting","Corporate - Asset Management"... into an array, and then check if value in M8 is in the array. You could use Match method to confirm if the array has the value quickly.

    Here is example and you need adjust it for your need.

    Sub IsNotCheck()
    
    Dim M8Value As String
    
    CorporateArr = Array("Corporate - Accounting", "Corporate - Asset Management", "Corporate - Capital Improvments")
    
    M8Value = "Corporate - Accounting"
    
    If IsInArray(M8Value, CorporateArr) Then
    
    MsgBox M8Value & " is in the array"
    
    Else
    
    MsgBox M8Value & " is not in the array"
    
    End If
    
    End Sub
    
    Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
    
        IsInArray = Not IsError(Application.Match(stringToBeFound, arr, 0))
    
    End Function


    Please refer to below link.

    https://stackoverflow.com/questions/38267950/check-if-a-value-is-in-an-array-or-not-with-excel-vba

    Besides, it seems that Cell M8 has data validation, so I don’t understand why need to check the value “is not” something

    There would be error when inputting values that is not in the list.

    Best Regards,

    Terry


    Monday, September 4, 2017 3:27 AM
  • For the drop down in cell M8 I am using a List through Data Validation

    Tuesday, September 5, 2017 6:07 PM
  • Hi Terry and thank you for your response/time.

    Cell M8 is actually a drop down menu tied to a list using data validation. I am attempting to have the code do something as long as the selection from the drop down in M8 is not a particular selection. I am trying to use the Is Not method as the list is much shorter than if I were to do the other way around.

    For instance, as long as the selection in M8 Is Not, "this", do this function; Otherwise do this other function....

    Does that make sense?

    In regards to your Array suggestion....looks like could work but I am not understanding the highlighted area below...seems this would limit to the one Selection? or would I have to list each one separately?

    Tuesday, September 5, 2017 6:25 PM
  • I was wondering...might there be a way to have it look for just the word "Corporate"? Meaning, If the selection in M8 begins with "Corporate" do this...otherwise this?
    Tuesday, September 5, 2017 6:39 PM
  • Hi rstreets,

    It seems Value in M8 is the value list of A6-A100 and so the value in M8 might be many kinds.

    I think you’d better look for regular rule to handle this. Checking begin word is a good idea.

    For instance, if value in M8 begins with “Corporate”, then do something, if begins with “Personal”, then do other something.

    Dim M8Value As String
    
    M8Value = Range("M8")
    
    If InStr(M8Value, "Corporate") = 1 Then
    
    ' do what you want to do
    
    MsgBox "Value in M8 starts with Corporate"
    
    ElseIf InStr(M8Value, "Personal") = 1 Then
    
    ' do what you want to do
    
    MsgBox "Value in M8 starts with Personal"
    
    End If

    Best Regards,

    Terry

    • Marked as answer by rstreets2 Wednesday, September 6, 2017 7:27 PM
    Wednesday, September 6, 2017 9:19 AM
  • Terry you are wonderful!! Thank you thank you thank you! Your idea sparked an outcome for me that works perfectly. See below :)

    Dim M8Value As String   
    M8Value = Sheets("Employee Change Form").Range("M8")

    If Sheets("Employee Change Form").Transfer_ChkBx.Value = True And _
                    Sheets("Employee Change Form").SalaryInc_ChkBx.Value = True And _
                        InStr(M8Value, "Corporate") = 1 Then
                        Sheets("Employee Change Form").Range("A13").EntireRow.Hidden = True
                        Sheets("Employee Change Form").Range("A14").EntireRow.Hidden = False
                Else
                    Sheets("Employee Change Form").Range("A13").EntireRow.Hidden = False
                    Sheets("Employee Change Form").Range("A14").EntireRow.Hidden = True
                End If

    Thanks again Terry for your time and assistance. This has been most helpful. Thank you thank you thank you!

    • Marked as answer by rstreets2 Wednesday, September 6, 2017 7:27 PM
    Wednesday, September 6, 2017 7:27 PM