none
Formula for validation data

    Question

  • Hello , i need put in validation data a formula or vba a code for check the next :

    In sheet “Codes” put the code for example in column C “asdf” and in the column J put 2.
    In the second sheet “Pool” put the next below:
    Codes Number
    Asdf 100

    I need check for the same code if in column J (“Codes”) is 2 or 3 , the cell (Number) in sheet ”Pool” must have a value greater than zero and less than 101. Otherwise if colunn J in sheet “Codes” have another value , the cell (Number) have the value 0.
    Thank you
    Wednesday, February 20, 2019 4:37 PM

Answers

  • Please give this a try...

    Right Click on Pool Tab --> View Code --> Paste the code given below into the opened code window --> Close the VB Editor --> Save your workbook as Macro-Enabled Workbook.

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    Dim wsCodes As Worksheet
    Dim CodeRng As Range
    Dim Code
    
    Set wsCodes = Worksheets("Codes")
    
    'Assuming, on Pool Tab, Column A has Codes and Column B has Number
    
    If Not Intersect(Target, Range("B:B")) Is Nothing Then
        Application.EnableEvents = False
        If Target.Row > 1 Then
            If Target <> "" Then
                If Target.Offset(0, -1) <> "" Then
                    Code = Target.Offset(0, -1).Value
                    Set CodeRng = wsCodes.Range("C:C").Find(what:=Code, lookat:=xlWhole)
                    If Not CodeRng Is Nothing Then
                        If wsCodes.Cells(CodeRng.Row, "J") = 2 Or wsCodes.Cells(CodeRng.Row, "J") = 3 Then
                            If Target < 0 Or Target >= 101 Then
                                MsgBox "You must enter a number which is greather than 0 and less than 101.", vbExclamation
                                Target = ""
                                GoTo Skip
                            End If
                        Else
                            If Target <> 0 Then
                                MsgBox "You must enter 0 only.", vbExclamation
                                Target = 0
                                GoTo Skip
                            End If
                        End If
                    Else
                        MsgBox "The code " & Code & " was not found on " & wsCodes & " Sheet.", vbExclamation, "Code Not Found!"
                    End If
                Else
                    MsgBox "Please input a Code in Column A first.", vbExclamation, "Code Required!"
                    Target = ""
                    GoTo Skip
                End If
            End If
        End If
    End If
    Skip:
    Application.EnableEvents = True
    End Sub


    Subodh Tiwari (Neeraj) sktneer

    • Marked as answer by LoganTRK Friday, February 22, 2019 10:57 AM
    Thursday, February 21, 2019 12:07 PM

All replies

  • Hi LoganTRK,

    Could you provide a decision table (condition table) in order to make your requirements clear, understandable?
    It would be like this.
        
        If you cannot insert an image, please share it via cloud storage such as OneDrive, Dropbox, etc.

    Regards,

    Ashidacchi -- https://ssl01.rocketnet.jp/hokusosha.com/default.html

    Wednesday, February 20, 2019 11:21 PM
  • Please give this a try...

    Right Click on Pool Tab --> View Code --> Paste the code given below into the opened code window --> Close the VB Editor --> Save your workbook as Macro-Enabled Workbook.

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    Dim wsCodes As Worksheet
    Dim CodeRng As Range
    Dim Code
    
    Set wsCodes = Worksheets("Codes")
    
    'Assuming, on Pool Tab, Column A has Codes and Column B has Number
    
    If Not Intersect(Target, Range("B:B")) Is Nothing Then
        Application.EnableEvents = False
        If Target.Row > 1 Then
            If Target <> "" Then
                If Target.Offset(0, -1) <> "" Then
                    Code = Target.Offset(0, -1).Value
                    Set CodeRng = wsCodes.Range("C:C").Find(what:=Code, lookat:=xlWhole)
                    If Not CodeRng Is Nothing Then
                        If wsCodes.Cells(CodeRng.Row, "J") = 2 Or wsCodes.Cells(CodeRng.Row, "J") = 3 Then
                            If Target < 0 Or Target >= 101 Then
                                MsgBox "You must enter a number which is greather than 0 and less than 101.", vbExclamation
                                Target = ""
                                GoTo Skip
                            End If
                        Else
                            If Target <> 0 Then
                                MsgBox "You must enter 0 only.", vbExclamation
                                Target = 0
                                GoTo Skip
                            End If
                        End If
                    Else
                        MsgBox "The code " & Code & " was not found on " & wsCodes & " Sheet.", vbExclamation, "Code Not Found!"
                    End If
                Else
                    MsgBox "Please input a Code in Column A first.", vbExclamation, "Code Required!"
                    Target = ""
                    GoTo Skip
                End If
            End If
        End If
    End If
    Skip:
    Application.EnableEvents = True
    End Sub


    Subodh Tiwari (Neeraj) sktneer

    • Marked as answer by LoganTRK Friday, February 22, 2019 10:57 AM
    Thursday, February 21, 2019 12:07 PM