Entering variable value in cell to right of 2nd variable

[CompleteNewb]

Greetings VBA experts.

After a couple hours of attempts, I've gotten close but have no cigar.

I'm trying to make it so that a user enters a value (text) in a particular
cell (let's say F3), then clicks one of two buttons (a PLUS button or MINUS
button) a certain number of times. Each click adds 1 (clicking 5 times = 5,
clicking 7 times = 7, etc.), and, depending on which button it is, the
number of clicks is either added to or subtracted from the current value in
the cell 3 columns to the right of the cell that contains the value in F3.

Examples:

Column A has text values all the way down. Cell A24 has "truck" in it.
User enters "truck" in F3, then clicks the PLUS button 5 times. Each click
adds 1 to the number in cell D24. So if D24's value is 12 before user
clicks the PLUS button for the first time, it has 13 when user clicks once,
14 when user clicks twice, etc.

Let's say cell A46 has "drag". User enters "drag" in F3, then clicks the
MINUS button 7 times. If D46 had 30 before user started clicking the MINUS
button, it has 29 the first click, 28 the second click, etc., and the final
value whne user is done is 23.

Now, I can use any number of methods that might be easier. For instance,
the value doesn't have to be incremented each click, the variable of click
counting can just add up the clicks, and then when the user hits "Enter" on
the keyboard, the total number of clicks is added or subtracted from the
correct cell.

OR, if it's easier, I could have the user enter the F3 value, then just
enter a number in another cell (let's say F4), and click a "PLUS" or "MINUS"
button. Then, on buttonclick, the value in F4 is either added to or
subtracted from the right D cell (in the row where the A cell has the value
in F3).

As you can see, I'm just trying to make it so that numbers can be added to
or subtracted from a D cell where the A cell value matches a specified
criteria. In VBA I'm having a very hard time doing this, and it's probably
a very simple goal. I'm currently having a problem using the VLOOKUP
concept to CHANGE values, as opposed to just looking them up.

Any ideas or input or (would love it) code snippets?

Thanks all for any help, and for taking the time to read.

The Complete Newb

 

[Don Guillett]

This may be easier. Right click sheet tab>view code>copy/paste this. SAVE
workbook.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$F$3" Then Exit Sub
mv = InputBox("Enter plus or minus number")
mr = Columns(1).Find(Target).Row
Cells(mr, 4) = Cells(mr, 4) + mv
End Sub

 

[Wild Bill]

Using application.worksheetfunction you can do the following MATCH
(we'll not use vlookup) in code, but for now you will find it more
instructive to just do this:

Define range MatchRow as cell F4, and in F4 enter =MATCH($F$3,A:A,FALSE)
You don't need VLOOKUP in this case, as it's the same as MATCH but is
used to gather a value to the right of the match (e.g. "truck" in A24).
You won't need to. So now you know the row, and you're almost done.

The plus button code is
Cells(Range("MatchRow"), 1 + 3) = Cells(Range("MatchRow"), 1 + 3) + 1
and the other is
Cells(Range("MatchRow"), 1 + 3) = Cells(Range("MatchRow"), 1 + 3) - 1

I deliberately wrote 1+3 because if the matches at some time are changed
to column B, it would need to be 2+3 or 5. The solution would be to name
a range atop the matches - right now, that's cell A1 - and call it
SearchCol or something. Then instead of 1+3 use
range("SearchCol").column+3.

While you're at it, instead of 3 you could similarly use
range("Quantity").column - range("SearchCol").column
if you've followed along so far - so the 3 is not hardwired either.

You're right, VLOOKUP is not used to CHANGE values, as opposed to just
looking them up.

 

[CompleteNewb]

Thanks to both of you for your suggestions, they helped VERY much.

I'm using a modification of Wild Bill's, and it's SUPERB. Thanks Wild Bill,
and I appreciate your additional explanations and tips, it was very awesome
and helpful. It gave me excellent insight into how to incorporate future
changes without having to track down every value and variable. In this
regard, you've taught me to fish.

Much appreciated.