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.
Related
- Search function in VBA/Excel
- Search function in vba excel - Guide
- Excel - The SEARCH function - How-To - Excel
- How combine IF function, SEARCH and ISERROR in Excel - Guide
- Excel 2007 Using vlookup function in VBA ✓ - Forum - Excel
- VBA Excel Search Select Paste Delete ✓ - Forum - Excel
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 (ccm.net).