restrict populating values into textbox based on combobox & condition

[leap out]

Hi experts
I need add condition to my code when I select item from combobox1 based on matching column D should populate values into textbox1 based on matching column I , but the populated values into textbox1 should be bigger or equal 1,000.00 .
this is my code

Private Sub combobox1_Change()
Dim f As Range
  With Worksheets("bmn")
    Set f = .Columns(4).Find(combobox1.Value, , xlValues, xlWhole)
    If Not f Is Nothing Then
       Me.TextBox1.Value=.Cells(f.Row, "I") 
    End If
  End With
End Sub

any suggestion guys ?

 

[DanteAmor]

How about:

Private Sub combobox1_Change()
  Dim f As Range
  Me.TextBox1.Value = ""
  With Worksheets("bmn")
    Set f = .Columns(4).Find(combobox1.Value, , xlValues, xlWhole)
    If Not f Is Nothing Then
      If .Cells(f.Row, "I") >= 1000 Then
        Me.TextBox1.Value = .Cells(f.Row, "I")
      Else
        MsgBox "the populated values into textbox1 should be bigger or equal 1,000.00 "
      End If
    End If
  End With
End Sub

 

[leap out]

Thanks and sorry I don't ask the question is very will . I want selecting the last value if the item is duplicated based on condition . for instance if I select item QW-100 and will repeate more than one time like

QW-100 =1,000.00
QW-100= 1,200.00
QW-100=10,000.00 then should show the last value into textbox = 10,000.00

 

[DanteAmor]

Try this:

Private Sub combobox1_Change()
  Dim f As Range
  Me.TextBox1.Value = ""
  With Worksheets("bmn")
    Set f = .Range("D:D").Find(combobox1.Value, , xlValues, xlWhole, xlByRows, xlPrevious, False)
    If Not f Is Nothing Then
      Me.TextBox1.Value = .Cells(f.Row, "I")
    End If
  End With
End Sub

 

[leap out]

no I want the condition . I try modifying your code , but doesn't work
should populate the last value >= 1000 for duplicates items.

Private Sub combobox1_Change()
  Dim f As Range
  Me.TextBox1.Value = ""
  With Worksheets("bmn")
    Set f = .Range("D:D").Find(Combobox1.Value, , xlValues, xlWhole, xlByRows, xlPrevious, False)
     If .Cells(f.Row, "I") >= 1000 Then
        Me.TextBox1.Value = .Cells(f.Row, "I")
    End If
  End With
End Sub

 

[DanteAmor]

I think I already understood.
Try this:

Private Sub combobox1_Change()
  Dim f As Range
  Dim i As Long
  
  Me.TextBox1.Value = ""
  If combobox1.ListIndex = -1 Then Exit Sub
  
  With Worksheets("bmn")
    For i = .Range("D" & Rows.Count).End(3).Row To 1 Step -1
      If .Range("D" & i).Value = combobox1.Value And .Range("I" & i).Value >= 1000 Then
        Me.TextBox1.Value = .Range("I" & i).Value
      End If
    Next
  End With
End Sub

 

[DanteAmor]

Sorry, one detail was missing:

Private Sub combobox1_Change()
  Dim f As Range
  Dim i As Long
  
  Me.TextBox1.Value = ""
  If combobox1.ListIndex = -1 Then Exit Sub
  
  With Worksheets("bmn")
    For i = .Range("D" & Rows.Count).End(3).Row To 1 Step -1
      If .Range("D" & i).Value = combobox1.Value And .Range("I" & i).Value >= 1000 Then
        Me.TextBox1.Value = .Range("I" & i).Value
        Exit Sub
      End If
    Next
  End With
End Sub

 

[leap out]

yes this is exactly what I want . just the message box can you add to the code ,pleas?
actually I like it as in orginal code .

 

[DanteAmor]

just the message box can you add to the code ,pleas?

ok, try this:

Private Sub combobox1_Change()
  Dim f As Range
  Dim i As Long
  
  Me.TextBox1.Value = ""
  If combobox1.ListIndex = -1 Then Exit Sub
  
  With Worksheets("bmn")
    For i = .Range("D" & Rows.Count).End(3).Row To 1 Step -1
      If .Range("D" & i).Value = combobox1.Value And .Range("I" & i).Value >= 1000 Then
        Me.TextBox1.Value = .Range("I" & i).Value
        Exit For
      End If
    Next
  End With
  If TextBox1.Value = "" Then
    MsgBox "the populated values into textbox1 should be bigger or equal 1,000.00 "
  End If
End Sub

 

[leap out]

awesome !
many thanks buddy