Autofilter and return cell values using VBA

  • Hi All,


    I have a VBA code that applies filters the required cells from Sheet "Peg_3" . Then locates/finds the header "35001" (which referenced in Dashboard sheet) and update the price and its currency in next column (35001 +1) . However the code updates header names (35001) instead of price and currency. (Kindly refer image "Incorrect Output")


    Please tell me what I should update in the code to reflect the required output.


    Your thoughts are highly appreciated.


    Attached the code, required output and sample workbook for reference.




  • An alternative method not using Autofilter


    Try this

    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.

  • gijsmo and KjBox Both macros are working perfect, This made my day. Thank you so much!!!


    gijsmo - I would like to know how it works without using autofilter? Just for understanding purpose.


    Also,


    Just wondering whether it is possible to reference a sheet name in C4?? In which sheet the macro should work


    Lets say I have PEG_3, PEG_5,_PEG_7 sheets are available. In C4, sheet name has to be selected.


    If I select PEG_7 in C4, The details should be updated only on PEG_7 sheet.


    Thanks again!!

  • I think you meant to ask me how the macro works without autofilter!


    It loads all the data on the "PEG" sheet into an array (Named x). The code then loops through that array and for rows where there is a match for the OP, DP & d_type entered in the dashboard it first locates the column for the entered Number then adds the values for Price, Currency, Transit Time, Expiry and Effective Date to the array in the correct positions.


    When the loop completes all iterations the values in it are written back to the PEG sheet updating it.


    It is possible to amend the code to work on a particular sheet where the sheet name is entered in Dashboard Sheet Cell C4.


    I will post the amended code shortly, and include some code comments to help you see what the code is doing where.

    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.

  • In the most recent file you posted, I see that the column headers are not consistent. So you need the code to first identify the correct column for OP, DP, d_type, Expiry & Effective date (as well as the Number Column)?


    Also, you have entered sheet PEG_7 and NUMBER 35037. There is no column with "35037" as the column header, what do you want the code to do in cases such as that? A message to say that the NUMBER is invalid?

    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.

  • gijsmo - Thank you so much for your efforts..! Works perfect!!


    @kjBox- Yes the formats change for each sheet,


    "Also, you have entered sheet PEG_7 and NUMBER 35037. There is no column with "35037" as the column header, what do you want the code to do in cases such as that? A message to say that the NUMBER is invalid?"


    -Please consider available header number from the sheet.


    For

    PEG_7 - number- 20007


    PEG_5 - number - 40141


    Please let me know what should I update if the format changes.


    Thanks a lot.

  • Best to specify as many requirements in one go in order to minimise re-coding.

    The assumption based on the original sample was that the macro had to :

    1. Autofilter PEG sheet on OP, DP, d_type

    2. Find the number to be matched column (eg, 35037)

    3. Update the number to be matched column with price & the adjacent column with the currency

    4. Update the transit_time, expiry & effective_date columns with data from the Dashboard sheet

    All the columns were located using a Range.Find method, if one or more of the columns on the PEG sheet were missing, nothing would get updated


    So :

    1. Is Transit Time the only column that may or may not exist on the PEG sheets ?

    2. Will the column names on the other PEG sheets be the same ? eg OP, DP, d_type, etc ?

  • If you want to eg change 93 AUD to 157 CAD (for the same OP, DP & d_type values) then just enter 157 CAD and click on the Add button - this will overwrite the 93 AUD with 157 CAD.


    If you want to clear those values then one way is to clear the Price and Currency values on the Dashboard sheet and then click the Add button. The only small issue here is that it will need a small tweak to the code to actually clear the price - the current code will set the values to 0 (zero). This tweak has been made in the version attached below.


    As for the other change, the attached version will ignore Transit Time column if it is not found.


    Dashboard TEST #4.xlsb

  • Your Sheet PEG_5 has "DP" in Column A, "d_type" in Column B, no "Transit Time" (that can be ignored if not present) then "OP" in Column KO but there are no values in that column. 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?


    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?

    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.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!