+ Reply to Thread
Results 1 to 6 of 6

vba to fetch number based on criteria

  1. #1
    Forum Contributor
    Join Date
    11-17-2015
    Location
    India
    MS-Off Ver
    2013
    Posts
    365

    vba to fetch number based on criteria

    In sheet “Data” I have my actual data.
    Column “A” is a Serial number, Column “B” is a Code, Column “L” is the Size, Column “M” is the “Category” and Column “N” is the “Fees”

    In Sheet “Fees”, I have Fees table.
    In Column “A”, I have category, which is mentioned in column M of Sheet “Data”.
    In Row number 2, band of size mentioned which is available in Column L of Sheet “Data”.
    From Row number 3 to 16, given a fees applicable against respective category and size.

    I need code for below task.

    In sheet “Data, Insert fees in column “N” as per the Size mentioned in Column L. Have to fetch the fees from Sheet “Fees”.
    For Examples in first row size is 75 and Category is 200000. The size 75 is falls under Size band of 71-75.
    Hence the Fees against category of 200000 and size band of 71-75 is 9,887.

    in Second row size is 67 and Category is 300000. The size 67 is falls under Size band of 66-70.
    Hence the Fees against category of 300000 and size band of 66-70 is 12576.

    Like wise have to fetch applicable fees in Column “N”.

    Incase the Category mentioned in Column M is Not available in Sheet “Fees” Insert String “Category Not Available”.
    For example, please refer Cell N12 . Here the Category mentioned is 4500000, which is not available in sheet “fees”.
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    11-17-2015
    Location
    India
    MS-Off Ver
    2013
    Posts
    365

    Re: vba to fetch number based on criteria

    The Above formula should run till end of the row of Serial Number in Column A.

  3. #3
    Forum Contributor
    Join Date
    11-17-2015
    Location
    India
    MS-Off Ver
    2013
    Posts
    365

    Re: vba to fetch number based on criteria

    Hi can any one help me on this?

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,554

    Re: vba to fetch number based on criteria

    For this proposal to work the values in Fees!B3:K3 are changed to 0, 21, 36 etc (also changed from text to general)
    The formula that populates N2 and down on the Data sheet is:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Forum Contributor
    Join Date
    11-17-2015
    Location
    India
    MS-Off Ver
    2013
    Posts
    365

    Re: vba to fetch number based on criteria

    thanks a ton

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,554

    Re: vba to fetch number based on criteria

    You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

+ 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. How to Fetch last status Based on criteria
    By Sanjibghosh in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-05-2018, 01:49 AM
  2. [SOLVED] Count number of lines based on text and number criteria, without duplicates
    By HVCompleto in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-28-2017, 07:17 AM
  3. [SOLVED] Create formula that can fetch the data on criteria
    By leprince2007 in forum Excel Formulas & Functions
    Replies: 19
    Last Post: 12-04-2016, 03:30 AM
  4. Fetch an alphabet followed by 3-digit number from a cell value
    By RDevi in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-12-2015, 03:38 PM
  5. Replies: 0
    Last Post: 01-11-2012, 09:55 AM
  6. based on a selection criteria fetch matching rows
    By shruthitulsi in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 02-11-2011, 11:09 AM
  7. Replies: 1
    Last Post: 01-07-2011, 09:01 AM

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