Snasui Excel Forum

Code: Select all

  Dim rngCmpr As Range
    Dim rngBrcd As Range
    With Workbooks("DataX.xlsx").Worksheets("Sheet1")
        Set rngvlp = .Range("a2", .Range("d" & .Rows.Count).End(xlUp))
        Set rngBrcd = rngvlp.Resize(, 1)
        Set rngCmpr = .Range("f2", .Range("f" & .Rows.Count).End(xlUp))
    End With
    With Application
        If .CountIf(rngCmpr, Me.TextBox5.Text) < 0 And _
            .CountIf(rngBrcd, Me.TextBox5.Text) < 2 Then
            MsgBox "<Your message>", vbInformation
            Exit Sub
        End If
    End With
'Other code

Code: Select all

Private Sub TextBox5_AfterUpdate()
    Dim rngCmpr As Range
    Dim rngBrcd As Range
    With Workbooks("Needle - Copy.xlsm").Worksheets("IN")
        Set rngvlp = .Range("a2", .Range("d" & .Rows.Count).End(xlUp))
        Set rngBrcd = rngvlp.Resize(, 1)
        Set rngCmpr = .Range("a2", .Range("d" & .Rows.Count).End(xlUp))
    End With
    With Application
        If .CountIf(rngCmpr, Me.TextBox5.Text) < 0 And _
           .CountIf(rngBrcd, Me.TextBox5.Text) > Workbooks("DataX.xlsm").Worksheets("sheet1").Range("F2:F1048576") Then
            MsgBox "<Your message>", vbInformation
            Exit Sub
        End If
    End With
'Other code

Code: Select all

Private Sub TextBox5_AfterUpdate()
    Dim rngBrcd As Range
    Dim rngBox As Range
    With Workbooks("DataX.xlsx").Worksheets("Sheet1")
        Set rngvlp = .Range("a2", .Range("d" & .Rows.Count).End(xlUp))
        Set rngBrcd = rngvlp.Resize(, 1)
        Set rngBox = .Range("e2", .Range("e" & .Rows.Count).End(xlUp))
    End With
    With Application
        If .CountIfs(rngBrcd, Me.TextBox5.Text, rngBox, Me.TextBox2.Text) > 1 Then
            MsgBox "<Your message countifs>", vbInformation
            Exit Sub
        End If
    End With

'With Workbooks("DataX.xlsx").Worksheets("Sheet1")
'Set rngvlp = .Range("a2", .Range("d" & .Rows.Count).End(xlUp))
'End With
'Other code