Results 1 to 14 of 14
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

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

    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.

  2. #2
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

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

    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")

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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.

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

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

    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.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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.

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

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

    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.

  8. #8
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

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

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

  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

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

    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
    Regards,
    Rory

    Microsoft MVP - Excel

  10. #10
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

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

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

  11. #11
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

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

    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!
    Regards,
    Rory

    Microsoft MVP - Excel

  12. #12
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

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

    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?

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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!)

  14. #14
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

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

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •