+ Reply to Thread
Results 1 to 2 of 2

Using AutoFilter with multiple Criteria & two different FIELD target ranges

  1. #1
    Registered User
    Join Date
    10-04-2014
    Location
    Atlanta GA
    MS-Off Ver
    MS office 365
    Posts
    9

    Using AutoFilter with multiple Criteria & two different FIELD target ranges

    Using AutoFilter with multiple Criteria & two different FIELD target ranges.

    The application is for collecting equipment hours and fuel usage and posting them into a data table on a different sheet, same workbook. I have a table where the user can input “ENDING METER” readings and Fuel usage for a piece of heavy equipment. Most of the time the user will enter Hours and gallons. Sometimes in the real-world, the equipment does not get fueled up on a day that it has “Hours” logged to it. So, on a given day, you may enter gallons of fuel but no hours.

    If the user enters Fuel and Hours for the same piece of equipment (same row), my VBA works and data gets moved to the table. If the user enters Hours and no fuel, that VBA scenario works as well. If the user enters FUEL USAGE only and no hours for that equipment (same ROW) along with other equipment hours, the stand-alone fuel doesn't get recorded.

    I cannot figure a way to deal with two FIELDS within the AutoFilter programmatically, or maybe you have a much better way to approach what I am trying to accomplish.

    I have tried to clean up the attached workbook as much as possible.
    Knightflier
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

    Re: Using AutoFilter with multiple Criteria & two different FIELD target ranges

    Hi Knightflier,

    Great Question. Another case where a 'Helper Column' might help you. Try the following formula for the Helper Column:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    The formula yields the following results:
    0 = No Data
    1 = End Meter
    2 = Fuel Only
    3 = Both End Meter and Fuel

    Run the Macro Twice using the Helper Column as the AutoFilter Field.
    a. First Pass - Value = 2 (Fuel Only) - Now all the 2 columns are reset
    b. Second Pass = Value > 0

    Lewis

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 11
    Last Post: 06-30-2016, 07:07 PM
  2. Replies: 11
    Last Post: 02-12-2015, 10:44 AM
  3. [SOLVED] Multiple Target Intersect Ranges on Same Worksheet
    By Mistweaver in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-28-2013, 04:35 PM
  4. Replies: 2
    Last Post: 09-28-2013, 03:39 PM
  5. [SOLVED] Autofilter with multiple criteria - how to ignore blank criteria
    By MOONDRIFT47 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-31-2012, 12:20 PM
  6. DSUM database field across multiple ranges
    By fynn in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-18-2008, 06:34 AM
  7. Ranges:Target in Worksheet_SelectionChange(ByVal Target As Range)
    By Kevin McCartney in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-15-2005, 09:06 AM

Tags for this Thread

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