Decrease value in cell based on VLookup (2002/2003)

[kweaver]

I am suspecting this might require a VBA solution and am far from skilled in that to create it. Hopefully, a clear explanation follows:
Ideally, I'd like to "prompt" my user for three different criteria, each of which would be a column (e.g., Name, State, Title).
Then, where there is a unique match in the spreadsheet, subtract yet another value provided by the user from yet a fourtth cell (e.g., inventory).
So, for "Weaver", "Florida", "Manager" and Inventory = 10, find the unique row matching the first three criteria and subtract 10 from a specific column in that row.

[kweaver]

Thanks, Hans, for the quick reply. However, I got a run-time error 1004 in the code:

Range("A1").CurrentRegion.AdvancedFilter _
Action:=xlFilterInPlace, CriteriaRange:=Range("G1:I2")

[HansV]

Sorry, my bad. I changed something in the worksheet without modifying the code accordingly. I have replaced the attachment.

[HansV]

Edited by HansV - replaced attachment because the original version contained an error.

You'd probably want to initiate this by clicking a command button or something like that, for having it occur automatically as the user enters data would be dangerous.

See the attached workbook. Next to the area where the user can enter the criteria, there is a command button that will subtract the supplied value from the appropriate row. The code uses Advanced Filter to obtain this row.

I didn't build in extensive error checking, for example to test whether the user entered a valid value to subtract.

[kweaver]

Thanks for the quick fix. In the "while we're at it" mode...
What would I write in the macro if the macro and the data triggering it resided in one sheet and the data being compared and decreased where in another?

Thanks.

[HansV]

You'd have to prefix each occurrence of Range(...) in the code with the worksheet it refers to: instead of

Range("J2")

use

Worksheets("ThisSheet").Range("J1")

etc.

[kweaver]

Thanks for your help and patience.
I'm now trying to modify your code to what I need. Does the use of "value" in: .value = .value - etc. match the heading name of "value" ?
The heading name that is being depleated in my real sheet is called "Qty. OnHand" and I can't seem to make that function properly in the macro.
I've tried leaving the code as is, but changed the .value (I suspect my period in "Qty." is an issue) and that didn't work. I also tried to preceed the .value with the Worksheets("etc") statement, but that didn't work either.

[kweaver]

Let's try a sample sheet and maybe it'll be clearer and ultimately help me. Thanks again.

[rory]

Try the attached tweak of Hans' code. I've also adjusted some of the column headings on your criteria sheet because they have to match the actual headings in the data sheet.
HTH

[kweaver]

Thanks, Rory...
Question...why does the range in Set rngData reference "A3" when the data actually begins in the second row?

[rory]

As long as the cell in question is within a solid block of data, then <code>Range(whatever).CurrentRegion</code> will return the entire data range - it's the code equivalent of pressing Ctrl+Shift+*. A3 happened to be the cell that Hans chose in his original code!

[kweaver]

I feel so stupid sometimes...
The button I "assume" is a CommandButton. Then, with that toolbar open, shouldn't I be able to put Subtract() in between the Sub and End Sub to have it run on a click?

[HansV]

The command button has been created from the Forms toolbar. The Subtract macro has been assigned directly to this button:
- Right-click the command button.
- Select Assign Macro from the popup menu.
- Select Subtract in the list of macros.
- Click OK.
- Click outside the button to deselect it.

Can I have a Joseph Phelps Insignia? (Just kidding!)

[kweaver]

Thanks, Hans. I thought it was from the other toolbar not the forms. Light getting through! Thanks again.