+ Reply to Thread
Results 1 to 10 of 10

problem with union method in data filter

  1. #1
    Registered User
    Join Date
    06-13-2014
    Posts
    23

    problem with union method in data filter

    Hello all,
    I am definitely a novice when it comes to writing VBA code. Generally, I search for an answer if I can't write it and if I can't find it, post a question with code to this forum. This has worked great, but this time I feel that I need to post the entire spread sheet so the results can be seen in context.

    "Primary Filter Beta 3" is designed to download data from the financial site Finviz.com and filter it by sector based on performance. I have tried to label the sections code as best as possible.

    The problem is that when the code tries to create a range that has no qualifying stocks it populates the section with the prior range even though it has a different name and different filters.

    For example, there are four filters used to create four ranges BullR, BullC, BearR, and BearC (in that order) before the resulting filtered stocks are placed on the respective sector sheets. However, if BearC has no selections it will paste BearR in the spot where BearC should go rather than leave the space blank. (It's a little different than that, but that's the best way I can describe it without looking at the sheet.) As long as at least one stock qualifies for an individual filters, it's fine.

    The code loops through the data 8 times; once for each sector running the for four filters for each sector. At the end of each loop I reset the ranges to Nothing (that solved a different problem).

    I suspect the problem is in the filter sections. It is the first time I have used the Union method, but logically it seems like what I need. The formatting sections work fine.

    Any insights are appreciated. Thanks in advance.
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor natefarm's Avatar
    Join Date
    04-22-2010
    Location
    Wichita, Kansas
    MS-Off Ver
    2016
    Posts
    1,020

    Re: problem with union method in data filter

    I did not try running your code, and didn't study it extensively, but I believe the problem is with the following:

    Please Login or Register  to view this content.
    What will happen if BullR IS Nothing? The code will skip selecting BullR, but will proceed through the subsequent lines with whatever was previously selected. The IF statement needs to encompass all subsequent code that should only be run if BillR is Not Nothing. You'll need to figure out what all to include before the End If. Apply the same logic to the BullC, BearR, and BearC sections.

    Also, in coding situations like you have, where you are performing four variations of the same operation four times, it would be good practice to put the operation in a subroutine and call it four times, passing parameters or making adjustments to variables as needed between the calls. It makes the code much easier to read and maintain (e.g., the above fix would only need to be done in one place instead of four).

    Have fun!
    Acts 4:12
    Salvation is found in no one else, for there is no other name under heaven given to mankind by which we must be saved.

  3. #3
    Registered User
    Join Date
    06-13-2014
    Posts
    23

    Re: problem with union method in data filter

    Hi Nate,
    I tried writing it as:

    <code>
    If BullR Is Nothing Then End If
    Else
    BullR.Select
    Selection.Copy Destination:=Sheets(strFin(Z)).Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
    End If
    </code>

    and it gave me an Else without If error before it even got tot the line. That made no sense to me.

    With regard to using subroutines and calling:
    I tried that, but ran into difficulties of where to define the variable that are used. It didn't seem that the variables in the main routine could be applied in the subs. If there is some way to avoid that problem I would love to know, way since as you said its much easier to read and maintain.
    Thanks

  4. #4
    Valued Forum Contributor natefarm's Avatar
    Join Date
    04-22-2010
    Location
    Wichita, Kansas
    MS-Off Ver
    2016
    Posts
    1,020

    Re: problem with union method in data filter

    End If terminates the If, so the Else you have there is all alone. You need:

    Please Login or Register  to view this content.
    Or better yet:

    Please Login or Register  to view this content.
    However, I think you need to include all the code for the section in the If statement, rather than just that one line.

    I'm posting this for you to think about, and maybe respond, and I'll follow with another one with a suggested subroutine solution.

  5. #5
    Registered User
    Join Date
    06-13-2014
    Posts
    23

    Re: problem with union method in data filter

    I used the second version with the BullR.Copy and added the End If. That seems to have worked with no values showing up when there are none that fit the criteria. Thank you.
    Is it possible that the only thing that I really needed was and End If? or does putting the Copy and Destination commands on a different line than the If/Then have an impact?

  6. #6
    Valued Forum Contributor natefarm's Avatar
    Join Date
    04-22-2010
    Location
    Wichita, Kansas
    MS-Off Ver
    2016
    Posts
    1,020

    Re: problem with union method in data filter

    Your code had two End Ifs with an Else in between, and wasn't valid code. I'm not following you. The Copy line wasn't and wouldn't be on the same line as the If/Then.

  7. #7
    Registered User
    Join Date
    06-13-2014
    Posts
    23

    Re: problem with union method in data filter

    The original line was:

    Please Login or Register  to view this content.
    Which you noted in your first response. There was no End If and BullR.Select was on the same line as the If/Then. Then I used your second suggestion.

    Please Login or Register  to view this content.
    The "BullR.Copy . . . " is on a different line, whereas "BullR.Select" was on the same line as the If/Then. While it certainly is more clear, does that have any impact on the actual running of the code. And the End If was not in the original.

  8. #8
    Valued Forum Contributor natefarm's Avatar
    Join Date
    04-22-2010
    Location
    Wichita, Kansas
    MS-Off Ver
    2016
    Posts
    1,020

    Re: problem with union method in data filter

    Ok, I'm with you now, and I see why you're confused.

    The single-line format of an If statement consists of:
    If [condition] Then [statement]

    The If begins and ends with that single line. Subsequent lines are executed regardless of the results of the condition.

    The more typical multi-line format consists of:
    If [condition] Then
    [statement(s)]
    End If

    Or optionally:
    If [condition] Then
    [statement(s)]
    Else
    [statement(s)]
    End If

    Your original code was the single-line format, so it always did the Copy, even if BullR was Nothing. The new code only does the copy if BullR is Not Nothing.

    Hope that clarifies it.

  9. #9
    Valued Forum Contributor natefarm's Avatar
    Join Date
    04-22-2010
    Location
    Wichita, Kansas
    MS-Off Ver
    2016
    Posts
    1,020

    Re: problem with union method in data filter

    Edopts, below is revised GetData code to replace what you have in that module. I have tested it by bypassing the portion that went out to pull data off the web (I just ran the code against what was already in the Raw sheet), so it mostly seems to work, but there could be things you still need to resolve. I located three variations in your code between the four sectors: The RowToSub index, the tint color, and the If statement for the performance conditions, so I built those into the subroutine. I hope it works for you.

    Please Login or Register  to view this content.

  10. #10
    Valued Forum Contributor natefarm's Avatar
    Join Date
    04-22-2010
    Location
    Wichita, Kansas
    MS-Off Ver
    2016
    Posts
    1,020

    Re: problem with union method in data filter

    I did notice one other variation that I didn't account for. In your original code, in the BullR section, it had the following:
    Please Login or Register  to view this content.
    Whereas the other three sections just had:
    Please Login or Register  to view this content.
    I don't know the purpose of that line, so I don't know if leaving it out is an issue or not.

  11. #11
    Registered User
    Join Date
    06-13-2014
    Posts
    23

    Re: problem with union method in data filter

    Thank you nate! You have been extremely helpful. I am going to have to take some time to review the revised GetData code. I can implement that when I have a better understanding of what is happening there. I am just glad to have it working.
    With regard to the Do Until: I think in some iteration I was getting too many results because something else was not working and it was occuring on RowTotSub(1), so I stuck that in to break the code when that error happened. After that was fixed, I kind of forgot about it. I don't think it will matter if it is removed at this point.
    Thanks again!
    Best,
    Edopts

+ 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. Help me to print a range before the other in a Union method.
    By OFUENT13 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-19-2012, 05:03 PM
  2. Setting Ranges With Union Method
    By Preston900 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-11-2008, 09:17 AM
  3. [SOLVED] Union Method
    By Noah in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-03-2006, 10:45 AM
  4. [SOLVED] RE: Union Method
    By Gary''s Student in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-03-2006, 07:40 AM
  5. [SOLVED] Union method for Range Object
    By Chad in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-10-2005, 04:06 PM

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