-2

So I have 2 sets of data: 1) amount spent 2) card number.

in a1 and b1

I want to make a userform that has 2 fields that will do the following:

field number 1: user enters card number and the system will look for the corresponding card number in column a.

field number 2: user enters amount spent and the system will use the corresponding card number located in field number 1 and add entered amount into field number 2 to the existing value.

how to does one go about doing this??

Desandwich
  • 3
  • 5

1 Answers1

0

Although using a TextBox is okay to find the corresponding Values, A Combobox would be better to decrease the chances of wrong Card Numbers being entered.

UserForm with Combobox

Use this code in the UserForm module.

Private Sub CommandButton1_Click()
    Dim Rws As Long, Rng As Range, sh1 As Worksheet, r As Range
    Set sh1 = Worksheets("Sheet1")
    With sh1
        Rws = .Cells(Rows.Count, "D").End(xlUp).Row
        Set Rng = .Range(.Cells(5, "D"), .Cells(Rws, "D"))
    End With
    Set r = Rng.Find(what:=ComboBox1, lookat:=xlWhole)
    If r.Offset(, -1) <> "" Then
        r.Offset(, -1) = r.Offset(, -1) + TextBox1.Value
    Else: r.Offset(, -1) = TextBox1.Value
    End If

End Sub

Private Sub UserForm_Initialize()
    Dim Rws As Long, Rng As Range, sh1 As Worksheet
    Set sh1 = Worksheets("Sheet1")
    With sh1
        Rws = .Cells(Rows.Count, "D").End(xlUp).Row
        Set Rng = .Range(.Cells(5, "D"), .Cells(Rws, "D"))
    End With
    ComboBox1.List = Rng.Value

End Sub
Davesexcel
  • 6,896
  • 2
  • 27
  • 42
  • hey davesexcel, i've tested the program and it sort of works but your amount spent simply replaces the existing value. im looking to take the existing value and add the entered value in the user form to it. thanks for taking the time to deal with my incompetence! – Desandwich Jan 26 '15 at 19:37
  • `r.Offset(, 1) = r.Offset(, 1) + TextBox1.Value` this part of the code adds textbox1 to the existing amount. – Davesexcel Jan 26 '15 at 20:52
  • ok sorry for being an excel idiot im really bad at this hahahaha my card number column is at row D and amount spend it at column C and both entries start populating at row 5 how do i tweak the code so that it starts there? thanks! – Desandwich Jan 27 '15 at 05:32
  • Your original question says the columns are "A" & "B" – Davesexcel Jan 27 '15 at 07:57
  • i was using an example and i thought i could change it myself but apparently overestimated myself hahaha – Desandwich Jan 27 '15 at 09:04