带有组合框和Vlookup的Excel VBA Userform

I try to make my first UserForm with combo box, I already made this:

我尝试用组合框制作我的第一个UserForm，我已经做了这个：

   Private Sub cmdClose_Click()
   Unload Me
   End Sub

  Private Sub Reg1_AfterUpdate()
  If WorksheetFunction.CountIf(Stock.Range("A:A"), Me.Range.Value) = 0 Then
  NsgBox "This is an incorrect item"
  Me.Reg1.Value = ""
  Exit Sub
  End If

  With Me
  .Reg2 = Application.WorksheetFunction.VLookup(CLng(Me.Reg1), 
  Stock.Range("Lookup"), 2, 0)
  End Sub 

  Private Sub UserForm_Click()
  End Sub

The thing is that I don't have any idea how to use combo box with Vlookup function.

问题是我不知道如何使用具有Vlookup功能的组合框。

I need a combo box because I would like to change a number on Sheet1.

我需要一个组合框，因为我想更改Sheet1上的数字。

For example:

例如：

       Harry      10
       David      20

  A1 Harry   B1  10
  A2 David   B2  20

So I would like to select a name from a Combo Box. After I selected a name I would like to type in a number into a textbox and this number is going to belong for the selected name and sum up with the existing number.

所以我想从组合框中选择一个名字。在我选择了一个名称后，我想在一个文本框中键入一个数字，这个数字将属于所选名称，并与现有数字相加。

So Harry has 10. After I selected Harry from combo box and set 90 in TextBox the number is going to change for 100 for Harry. That's why I think I have to use Vlookup somehow in VBA.

所以Harry有10个。在我从组合框中选择Harry并在TextBox中设置90后，Harry的数字将改为100。这就是为什么我认为我必须在VBA中以某种方式使用Vlookup。

Thank you

谢谢

1 个解决方案

#1

The code below should work as you asked

下面的代码应该按照你的要求工作

Sub ChangeValue()
    Dim sheetName As String

    sheetName = "Name of your sheet"

    With ThisWorkbook.Sheets(sheetName)
       'For each name in your range
        For Each cell In .Range("Your range where names are") 
           'If the name is the one selected in your combobox
            If (cell = YourComboBox.Text) Then
                'Increment value 
                .Range(cell.Address).Offset(0, 1).Value = _
                .Range(cell.Address).Offset(0, 1).Value + YourTextBoxValue.Text
            End If
        Next cell
    End With
End Sub

---

Usage

Replace Name of your sheet with the name of the sheet where the names are.

使用名称所在的工作表名称替换工作表的名称。

Replace Your range where names are with the range where we can find all the names in your sheet.

替换名称所在的范围，我们可以在工作表中找到所有名称。

Replace YourComboBox and YourTextBoxValue with the names of your components in your userForm.

将YourComboBox和YourTextBoxValue替换为userForm中组件的名称。