Menu

Search function in VBA/Excel

April 2018




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

Published by aakai1056. Latest update on February 22, 2012 at 09:30 AM by aakai1056.
This document, titled "Search function in VBA/Excel," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (https://ccm.net/).
Excel - A macro to create new workbook and copy data
VBA - A macro to create multi-sheet pivot tables