+ Reply to Thread
Results 1 to 16 of 16

Offset and If/Then Statements

  1. #1
    Registered User
    Join Date
    01-28-2011
    Location
    Indy, IN
    MS-Off Ver
    Excel 2003
    Posts
    23

    Offset and If/Then Statements

    Hey guys,

    I'm pretty much halfway there with this program I am attempting to complete. What it does right now is finds every row that has a "1" in the "H" column and copy and pastes that row into a new worksheet. Here's the code for it.

    Please Login or Register  to view this content.
    What I also want the program to do is to copy the 5 rows before the "1" and the 5 rows after the "1" as well. I believe that this is possible using the Offset function to select other rows in relation to the row with the "1," however I've been having trouble getting it to work correctly.

    The other thing about this program is that I don't want it to copy the 5 rows before or after the "1" if any of those rows have a "1" in them. I believe that this is possible using If/Then statements, but the tutorials I've read about using that have been very confusing and not very helpful.

    Any help would be much appreciated guys. I have basically no experience using this program, so again I would be grateful for any tips or help. Thanks!
    Last edited by mundellj; 02-06-2011 at 08:11 PM.

  2. #2
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,218

    Re: Help with Offset and If/Then Statements

    Try this
    Please Login or Register  to view this content.

    Adjust the range refs and sheet names to suit your situation.

    This is as it stands a bit dodgey.
    1/. If the first instance of "1" is before Row 5 then you obviously cannot copy 5 rows before that, I have allowed for this situation by copying all the preceding rows.
    2/. If instances of "1" are less than 11 rows apart then you will get duplication where the conditions overlap.

    You used Ismissing(), that is one way to set optional values, my preference is to set the values in the Sub arguements, it's up to you how you want to do this.

    When naming subs and functions it isn't a good idea to use names that are standard function names.

    Hope this helps.
    If you need any more information, please feel free to ask.

    However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....

    Also
    اس کی مدد کرتا ہے اگر
    شکریہ کہنے کے لئے سٹار کلک کریں
    If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.

  3. #3
    Registered User
    Join Date
    01-28-2011
    Location
    Indy, IN
    MS-Off Ver
    Excel 2003
    Posts
    23

    Re: Help with Offset and If/Then Statements

    Marcol,

    Thank you very much for your response! I tried what you said (using your code) and adjusted the sheet name to match what I needed in my data, but when I ran the program it ran without any errors but did not copy any of my data into the new sheet. However, I did not quite understand what you meant by "adjust the range refs." Perhaps I didn't set those correctly and that's why nothing got copied? Could you please elaborate on that point? Again, I very much appreciate the response, this has been frustrating me for the past week and I am at a loss at what to do.

    EDIT: If you think it would be more helpful/easier, I could attach the data we are using this program with so we are both on the same page. Should I post it?
    Last edited by mundellj; 02-03-2011 at 08:41 PM. Reason: adding information

  4. #4
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,218

    Re: Help with Offset and If/Then Statements

    Range refs = The Range Addresses you need to use in your situation.

    Can you post a sample of your workbook, with any sensitive data disguised?

    We should then be able to offer you an applied example you could easily follow.

    Cheers

  5. #5
    Valued Forum Contributor
    Join Date
    09-21-2003
    Location
    British Columbia , Canada
    MS-Off Ver
    03,07,10,13
    Posts
    727

    Re: Help with Offset and If/Then Statements

    As long as the "1"s start no Higher than row 6 then you should get the range results you want by modifiing this one line in your code ....

    FROM ...Set Find_Range = Union(Find_Range, c)

    TO ...
    Set Find_Range = Union(Find_Range, Range(Cells(c.Offset(-5, 0).Row, c.Column), Cells(c.Offset(5, 0).Row, c.Column)))

    Does that help ?
    Last edited by nimrod; 02-03-2011 at 09:01 PM.

  6. #6
    Registered User
    Join Date
    01-28-2011
    Location
    Indy, IN
    MS-Off Ver
    Excel 2003
    Posts
    23

    Re: Help with Offset and If/Then Statements

    I've attached one of our data sets. It is the raw data that we recieve from the tests we run. I chose this data set since I think it would be the easiest to work with (the rows with the 1s are spaced out). However, sometimes the data will have the 1s very close together. FYI, these rows of 1s will always be in sets of fives (five 1s in a row, you'll better understand if you look at the data).

    EDIT: The 1s are in column H, under "CS."
    Attached Files Attached Files
    Last edited by mundellj; 02-03-2011 at 09:21 PM. Reason: additional information

  7. #7
    Registered User
    Join Date
    01-28-2011
    Location
    Indy, IN
    MS-Off Ver
    Excel 2003
    Posts
    23

    Re: Help with Offset and If/Then Statements

    Nimrod,

    That works perfectly in this data set! However, I think it is running into trouble with data sets that have the 1s closer than five rows together. When the 1s are close together like that, it will give me less than 5 rows of zeros (which is unusable for the data we want). Is there a way to just not copy those rows of zeros if they are within the 5 row range of the nearest 1? (I hope that wasn't too confusing to understand, if so I'll try to explain better).

    I really appreciate all the help guys, great community here.

  8. #8
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,218

    Re: Help with Offset and If/Then Statements

    What are you looking for as a result?

    Is it rows that return this sequence
    1/. 0,0,0,0,0,1,1,1,1,1,0,0,0,0,0
    Or
    2/. 0,0,0,0,0,1,1,1,1,1

    This workbook returns the second option
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    01-28-2011
    Location
    Indy, IN
    MS-Off Ver
    Excel 2003
    Posts
    23

    Re: Help with Offset and If/Then Statements

    I'll try to clarify what I want.

    If the H column looks like this: 0,0,0,0,0,1,1,1,1,1,0,0,0,0,0
    Then I want it to return these rows: 0,0,0,0,0,1,1,1,1,1,0,0,0,0,0

    If the H column looks like this: 1,1,0,0,0,1,1,1,1,1,0,0,0,0,0
    Then I want it to return these rows: 1,1,1,1,1,0,0,0,0,0

    If the H column looks like this: 0,0,0,0,0,1,1,1,1,1,0,0,1,1,1
    Then I want it to return these rows: 0,0,0,0,0,1,1,1,1,1

    If the H column looks like this: 1,1,1,0,0,1,1,1,1,1,0,0,0,1,1
    Then I want it to return these rows: 1,1,1,1,1

    Basically, I always want the five rows that have the 1s to always be in my data set. However, if the five previous rows or the five rows after are only zeros, then I want them as well. If they have some 1s mixed in, then the data is not usable. As long as this data remains in order, then whatever data that is returned using these rules will be usable.

    Is this more clear? I still feel like I'm being confusing, so just let me know if that's not clear enough.

  10. #10
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,218

    Re: Help with Offset and If/Then Statements

    Try this workbook

    With the criteria given the result could have 10 or 15 consecutive 1s in certain situations, is that acceptable?
    Attached Files Attached Files
    Last edited by Marcol; 02-04-2011 at 10:14 PM.

  11. #11
    Registered User
    Join Date
    01-28-2011
    Location
    Indy, IN
    MS-Off Ver
    Excel 2003
    Posts
    23

    Re: Offset and If/Then Statements

    Marcol,

    This works just how I want it, thank you so much! The only other thing I could possibly ask is how can I make sure this works on every worksheet? Right now, this code only works in this worksheet and not others unless I change:

    Sheets("D6-12box2-113")

    to

    Sheets("Whatever the other worksheets are called")

    Is there a way for this code to work on every worksheet without changing that code every time? Thanks again for your help, this is going to save me so much time.

  12. #12
    Valued Forum Contributor
    Join Date
    09-21-2003
    Location
    British Columbia , Canada
    MS-Off Ver
    03,07,10,13
    Posts
    727

    Re: Offset and If/Then Statements

    If the sheet is the active sheet when the process is done than replace Sheets("D6-12box2-113")
    with ActiveSheet

    i.. Sheets("D6-12box2-113").Range("A3").value = demo would be replaced with Activesheet.Range("A3").value

    another solution would to pass the name of the sheet as an variable ...

    ie. SheetName = "D6-12box2-113" ... Sheets(SheetName).Range("A3").value = demo

  13. #13
    Registered User
    Join Date
    01-28-2011
    Location
    Indy, IN
    MS-Off Ver
    Excel 2003
    Posts
    23

    Re: Offset and If/Then Statements

    Nimrod,

    Those two options both give me errors.

    The first one says : Type mismatch

    The second one says: Compile error: variable not defined

  14. #14
    Valued Forum Contributor
    Join Date
    09-21-2003
    Location
    British Columbia , Canada
    MS-Off Ver
    03,07,10,13
    Posts
    727

    Re: Offset and If/Then Statements

    hi mundellj:
    Could you provide a sample or code snippet so I can see what you're doing ?

  15. #15
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,218

    Re: Offset and If/Then Statements

    Change the calling macro to this
    Please Login or Register  to view this content.
    This assumes that "Sheet1" is where you want the result written and appended for every sheet in the book.

    There is also a change in the Sub Find_Range
    This line
    Please Login or Register  to view this content.
    Now
    Please Login or Register  to view this content.
    If the destination sheet is not "Sheet1" change to suit throughout the code.

    See the attached.
    Attached Files Attached Files

  16. #16
    Registered User
    Join Date
    01-28-2011
    Location
    Indy, IN
    MS-Off Ver
    Excel 2003
    Posts
    23

    Re: Offset and If/Then Statements

    Wow, thanks so much for all the help Marcol and Nimrod. It amazes me that there are people out there willing to help and I'm very grateful for your generosity. Thanks again!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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