+ Reply to Thread
Results 1 to 7 of 7

Altering a VLOOKUP value in a macro

  1. #1
    Registered User
    Join Date
    03-10-2017
    Location
    London
    MS-Off Ver
    15.32
    Posts
    8

    Altering a VLOOKUP value in a macro

    Hi

    I have a spreadsheet displaying jobs ( Runs ) that gets added to on a daily basis.
    Each row contains a serial no of a stock item that is linked to another sheet displaying current Stock quantity.

    What Iím trying to do is add a checkbox on each row that is linked to a macro that performs a Vlookup on the serial number
    And reduces the current stock quantity for that product by 1 when the checkbox is pressed.

    Is this possible and if so is there any example I can download please.
    Attached Files Attached Files

  2. #2
    Forum Moderator Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    9,727

    Re: Altering a VLOOKUP value in a macro

    Could you also please post the macro you are talking about?
    You really don't have to give me rep points for moderating. It's part of the job. Keep rep points for those who find solutions

  3. #3
    Registered User
    Join Date
    03-10-2017
    Location
    London
    MS-Off Ver
    15.32
    Posts
    8

    Re: Altering a VLOOKUP value in a macro

    Hi
    Thats my problem as I don't have the Macro. Ive created one that just subtracts 1 from a cell value on the same sheet
    but it would need to perform a VLOOKUp on another sheet and then subtract from that found value.

  4. #4
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    1,711

    Re: Altering a VLOOKUP value in a macro

    I've create something like this:

    Please Login or Register  to view this content.
    BUT checkboxes names has to be named as is now, I mean 'Check Box X' where x = RowNumber-2 (you've got exactly like this now) that's why SerialRow above has -2 at end.
    If you change pattern of check boxes names you have to adapt it.
    I think should work fine.
    Deduction is made when check box is marked. Unmarking does not run anything.
    Happy with my answer * Add Reputation.
    If You are happy with solution, please use Thread tools and mark thread as SOLVED.

  5. #5
    Registered User
    Join Date
    03-10-2017
    Location
    London
    MS-Off Ver
    15.32
    Posts
    8

    Re: Altering a VLOOKUP value in a macro

    Hi Kokosek

    Thank you for this. I've added the Macro and it works on the first row ( Run 958 ) but when I add the checkbox to the below rows nothing happens.

    What am I doing wrong?
    Attached Files Attached Files

  6. #6
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    1,711

    Re: Altering a VLOOKUP value in a macro

    Sorry, I forget to tell you (I assume that you know this) you have to assign this macro into each checkbox.
    Right click on check box, choose assign macro then instead of Checkbox5_Click choose ReduceStockLevel macro for each of them.

  7. #7
    Registered User
    Join Date
    03-10-2017
    Location
    London
    MS-Off Ver
    15.32
    Posts
    8

    Re: Altering a VLOOKUP value in a macro

    Hi
    Ive assigned ReduceStockLevel in the Assign Macro window and it worked on the original few rows but if I create some new rows and copy and paste the checkbox
    again and then assign the macro it doesn't work.
    Row 6 ( 961 ) is the same as row above but doesn't register the reduction.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1