Autofilter and return cell values using VBA

  • There are values in Column KP which has "service_type" as a header, those values look as if they should be the OP values and should be in Column KO! Is that correct?- Yes


    As for the NUMBERS, PEG_3 all start with a "3". PEG_5 with a "4" and PEG_7 with a "2", is that correct?- Yes

  • Some of the text has obviously been transposed, this is what that section should look like:


  • Here is your file with new code and a couple of other changes.


    I made Dashboard C4 to C13 named ranges (simplifies the code a bit) and you will note values in K2 to K4, these are the integers that NUMBER must start with for each given PEG (PEGs are in J2 to J4). You can change the font colour of K2 to K4 to hide the text. This is to check that a valid NUMBER has been entered for the entered PEG.


    Further checks for other validities can be incorporated into the main code.


    The NUMBER check code is a Worksheet_Change Event Code in the Dasboard Sheet Module, it is fired when a new NUMBER is entered. The code is:

    The code assigned to the button is still in Module 1, and is

    Let me know if you have any issues.

    Files

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • You're welcome, hope you can transfer everything to you actual workbook ok :)

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • I've just noticed that if you are using the code in my examples, you need to change this part in the Add sub from:


    Code
    1. If Not FindInRange(rRng, TTime, rFind) Then lTTime = 0


    To:


    Code
    1. If Not FindInRange(rRng, TTime, rFind) Then
    2. lTTime = 0
    3. Else
    4. lTTime = rFind.Column
    5. End If


    Otherwise Transit Time will never get updated even when it exists in the LEG sheet