+ Reply to Thread
Results 1 to 5 of 5

Search a range in one sheet. if any cell in range is greater then 0 copy that row to new

  1. #1
    Forum Contributor
    Join Date
    06-25-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2003
    Posts
    179

    Search a range in one sheet. if any cell in range is greater then 0 copy that row to new

    Hello,
    I've been trying to do this with if statements and some code i saw somewhere else but im struggling to convert it to my use.
    Basically I have three sheets. MAIN, Sheet 1 and Sheet 2

    Sheet 1 and 2 are in the same format

    A3 down is a list of country names and then B3:I71 contains the data im interested in.

    I've been trying to create a function that looks at B3:i71 to see if any cell in that range contains a value greater then $0.00. If it does then the row that contains the cell with a value greater then $0.00 (between col A to K) should be copied to sheet MAIN from cells B3 down. This should ultimatley produce a list of data for any row containing a value greater then $0.00. This process should then be repeated on Sheet 2 and should join the list below sheet 1.

    Everything i've tried (non vba) just brings in everthing and the idea is i was to make the data easier to read.

    Any pointers would be great.

  2. #2
    Valued Forum Contributor
    Join Date
    10-26-2008
    Location
    Birmingham, UK
    MS-Off Ver
    All versions up to 2010
    Posts
    1,025

    Re: Search a range in one sheet. if any cell in range is greater then 0 copy that row to n

    Hi
    Can you post a copy of your workbook so we can see exactly what we are dealing with.
    Tony

  3. #3
    Forum Contributor
    Join Date
    10-12-2012
    Location
    Bournemouth
    MS-Off Ver
    Excel 2010 / Excel 2007
    Posts
    126

    Re: Search a range in one sheet. if any cell in range is greater then 0 copy that row to n

    Hi

    I've recently had to do something similar for an accountancy system I was working on, but it looked at dates rather than monetary value. Anyway, the way I did it in the end was got VBA to use an IF function in an empty cell, so if for arguments sake your $ is in cell E, it would be:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Then delete any row that contains 'No, then copy over

    The code you'd need to achieve all this would be something like:

    Please Login or Register  to view this content.
    The above should add the formula, delete any 'No' fields, count how many rows you have left, insert that many rows into Main then copy the information over.

    You may need to play around with it a little

  4. #4
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Search a range in one sheet. if any cell in range is greater then 0 copy that row to n

    Maybe:

    Please Login or Register  to view this content.
    Last edited by JOHN H. DAVIS; 09-24-2013 at 06:28 AM.

  5. #5
    Forum Contributor
    Join Date
    06-25-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2003
    Posts
    179

    Re: Search a range in one sheet. if any cell in range is greater then 0 copy that row to n

    Hey everyone thanks for the updates and code.
    I semi solved the problem with a lot of if functions but it leaves with a lot of white space which i then have to use the filter on (which is a bit ugly).

    John unfortunatley your code gave me some weird errors where it started copying down sections of one line onto the next. I think its because each row can contain more then one 0.00 or more then one price greater then 0.00.

  6. #6
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Search a range in one sheet. if any cell in range is greater then 0 copy that row to n

    Can you attach a sample file as suggested in Post #2. And maybe provide the code that you currently have?

+ 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: 9
    Last Post: 04-04-2013, 11:47 PM
  2. Replies: 9
    Last Post: 10-26-2012, 12:52 PM
  3. Replies: 3
    Last Post: 08-16-2012, 03:25 PM
  4. [SOLVED] Search for heading in data sheet and copy range to corresponding sheet in master workbook
    By sans in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 07-07-2012, 10:02 AM
  5. Search within range for first populated cell and copy to new workbook
    By 1ceman in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-08-2006, 07:20 PM

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