Search function in VBA/Excel

December 2016




I try to make a macro that looks for a value in a database and that copies data in two columns after the reference.

example:

database:
PS85
ps86
ps77
ps52
...
  • In a userform, a drop menu with value "ps86", another with the number "5"
  • A submit button
  • when I click the button I would get in the database:


PS85
ps86 5
ps77
ps52
...

Solution


Try this code:
ComboBox1 = droplist with value (ps85, ps86 ...)
ComboBox2 = Quantity list
CommandButton1 = The submit button

Adapt the following code:

Private Sub CommandButton1_Click()
Dim RngTrouve As Range
If ComboBox1 <> "" And ComboBox2 <> "" Then
    With Sheets(NomDeTaFeuil).Columns(1)
        Set RngTrouve = .Cells.Find(ComboBox1.Value, lookat:=xlWhole)
        If RngTrouve Is Nothing Then
            MsgBox "valeur inexistante"
        Else
            RngTrouve.Offset(0, 2).Value = ComboBox2.Value
        End If
    End With
End If
Set RngTrouve = Nothing
End Sub


Thanks to pijaku for this tip.

Related :

This document entitled « Search function in VBA/Excel » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.