Snasui Excel Forum

Code: Select all

Dim rngCmpr As Range
  Dim rngBrcd As Range
  Dim rngTotal 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("e2", .Range("e" & .Rows.Count).End(xlUp))
      Set rngTotal = .Range("f2", .Range("f" & .Rows.Count).End(xlUp))
End With
If Me.TextBox5.Text = "" Then Exit Sub
With Application
If .SumIfs(rngTotal, rngBrcd, Me.TextBox5, rngCmpr, Me.TextBox2) > 2 Then
    MsgBox "Please Check Information", vbInformation
    Exit Sub
    End If
End With
'Other code
'With Workbooks("DataX.xlsx").Worksheets("Sheet1")
'Set rngvlp = .Range("a2", .Range("d" & .Rows.Count).End(xlUp))
'End With

Code: Select all

Dim rngCmpr01 As Range
  Dim rngBrcd01 As Range
  Dim rngTotal01 As Range
  With Workbooks("DataX.xlsx").Worksheets("Sheet1")
      Set rngvlp2 = .Range("a2", .Range("d" & .Rows.Count).End(xlUp))
      Set rngBrcd01 = rngvlp2.Resize(, 1)
      Set rngCmpr01 = .Range("e2", .Range("e" & .Rows.Count).End(xlUp))
      Set rngTotal01 = .Range("f2", .Range("f" & .Rows.Count).End(xlUp))
End With
If Me.TextBox5.Text = "" Then Exit Sub
With Application
If .SumIfs(rngTotal01, rngBrcd01, Me.TextBox5, rngCmpr01, Me.TextBox2) > 2 Then
    MsgBox "Please Check Information", vbInformation
    Exit Sub
    End If
End With

Code: Select all

Dim cnt As Integer
Dim rngCmpr As Range
Dim rngBrcd As Range
Dim rngTotal 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("e2", .Range("e" & .Rows.Count).End(xlUp))
    Set rngTotal = .Range("f2", .Range("f" & .Rows.Count).End(xlUp))
End With
If Me.TextBox5.Text = "" Then Exit Sub

With Application
    cnt = .SumIfs(rngTotal, rngBrcd, Me.TextBox5, rngCmpr, Me.TextBox2)
End With

With ThisWorkbook.Sheets("IN")
    With Application
        If .CountIfs(.Range("b3:b1000"), Me.TextBox2.Text, .Range("d3:d1000"), _
            Me.TextBox5.Text) >= cnt Then
            MsgBox "<Your text>"
            Exit Sub
        End If
    End With
End With