Macro - Look for value match and if not found, find nearest match using variables

    Hello and thank you for any help you may be able to provide!


    I'm new to VBA and I've tried several different ways to accomplish this using EXCEL functions but I would like to simplify my workbook by using VBA.


    I have a workbook with 3 varying data sources and I need to match values to determine volumes and then calculate billing details. The data sources are on separate sheets but to the attached sample workbook has the data sources on all one sheet. I need to calculate the values in yellow by matching up the account number in the order of priority as listed below:


    1) Account Number_Meter Number_Location Number (If found use this to search for volumes in column M)

    2) Account Number_Any combination of Meter Number or Location Number (If found use this to search for volumes in column M)

    3) If none of the variables (Account Number, Meter Number, Location Number) match then just use Account Number to search for volumes


    Once matches are found then find volumes in column M and calculate values in yellow highlighted fields.


    Thank you very much to anyone that can help me solve this.

    Here is one approach. I am not saying it is the best approach.


    Have the code pass the sheet as you have it ByVal to a subroutine. I am doing it this way so that when the program finishes the sheet as you have it now will remain the same, without the additions that are to come below.

    Then,

    First, add three columns to the right of column "C". They would be titled "Account _Meter_Location", "Account_Meter", and "Account_Location".


    Second, place the 3 formulas "=A2&"-"&B2&"-"&C2", "=A2&"-"&B2", and "=A2&"-"C2" (omit the inside and outside quotes) in "D2", "E2" and "F2".

    Third, copy "D2:F2" (note the colon, not comma) and paste it in the remaining Range("D3:F(last row in "F")

    Here is one approach. I am not saying it is the best approach.


    Have the code pass the sheet as you have it ByVal to a subroutine. I am doing it this way so that when the program finishes the sheet as you have it now will remain the same, without the additions that are to come below.

    Then,

    First, add three columns to the right of column "C". They would be titled "Account _Meter_Location", "Account_Meter", and "Account_Location".

    Second, place the 3 formulas "=A2&"-"&B2&"-"&C2", "=A2&"-"&B2", and "=A2&"-"C2" (omit the inside and outside quotes) in "D2", "E2" and "F2".

    Third, copy "D2:F2" (note the colon, not comma) and paste it in the remaining Range("D3:F(last row in "F")


    Column "D" can now be searched using Account-Meter-Location as the search value, and the dashes must be there. If found, there is your most desirable choice, else search "E", else "F" and if that returns empty search "A", your least desirable choice.


    There are several things that would be nice to know to really work the problem. How big is column "A"? For a given account number are they all grouped together as in your example, or can they, might they be scattered throughout the sheet? It might be desirable to sort the sheet before getting started. Lastly, you mentioned that your example is a composite of several sheets. Where are these other sheets; are they in the same book?

Participate now!

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

Register Yourself Login