0
Thanks

A few words of thanks would be greatly appreciated.

Search function in VBA/Excel




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.
0
Thanks

A few words of thanks would be greatly appreciated.

Ask a question
CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jeff Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.

Related

Published by . Latest update on 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/).

0 Comments