Posts by gliffix101

  • Create Line Chart based on unique value

    I do not. The Locations showing may vary - some will show Locations 1 thru 10, each with weeks 1 thru 4. Some months, Location 9 may be closed for a week or two for maintenance. This is why I need that code to find a value in A, run through the chart logic, and then find the next unique value in Column A to identify the next location to create the chart for. I could filter on Location 1, show the charts, then filter for the next value manually, but I do not know how to do that via code.

  • Create Line Chart based on unique value

    As an update: I finally came up with base code that will pull for the defined ranges of Weeks 1 thru 4, but clearly this is all hard coded based on Location 1. I would need this to be dynamic enough that it would create a new chart for Location 1, Location 2, Location 3 - regardless of the number of weeks:


  • Create Line Chart based on unique value

    Hi All -


    I could really use some help here as with all things vba, loops and graphs are my biggest nightmare. Any help is appreciated.


    Below is the data set I receive each week. My goal is to create a unique line chart based on Site location (i.e. Location 1 has a chart, Location 2 has a chart, etc). ROR, TimePeriod, ActualAmounts, ProposedAmounts, and DueDates are my lines, with Weeks 1 thru 4 running across the bottom. Below is my sample data sheet (Column A to I)


    Site Manager Week Total Valid IncidentsRORTimePeriod ActualAmounts Proposed Amounts DueDates
    Location 1 GliffWeek 1 6 17% 17% 33% 17% 50%
    Location 1 GliffWeek 2 17 29% 29% 35% 53% 94%
    Location 1 GliffWeek 3 6 0% 0% 0% 33% 83%
    Location 1 GliffWeek 4 17 6% 6% 12% 24% 88%
    Location 2 Gary Week 1 75 91% 92% 91% 89% 89%
    Location 2 Gary Week 2 60 85% 80% 85% 85% 82%
    Location 2 Gary Week 3 47 77% 77% 72% 79% 79%
    Location 2 Gary Week 4 64 88% 88% 88% 89% 91%


    Here is my sample graph (attached).



    I would like to have a macro that I can run that will create an individual graph for each unique location in a new workbook on separate tabs. The tabs would be named as the site location. I'm hoping that someone has some code they already run and can share to help me out. Below is the code I have created so far, but this doesn't even come close to creating the type of graph I am looking for. I have been doing this manually for 120 unique site locations, so if there is another way I beg for help.


    Here is the code:


    Forever grateful for this site!

    ~gliffy

  • Filter & Email Loop

    Hello World,


    I have a data set that I currently filter on (Data/Column B) and then email that sheet out to that individual. I have two tabs. The first tab is the data, the second tab is a user list (Column A) and email list (Column B). I manually filter Data/Column B on User/Column A, email to User/Email Column B, and then go back thru (A2 to A50). I'm looking for a loop code that I can run that will grab filter my Data/Column B by UserList/A2 and then email it to UserList/B2, and then loop through until I've filtered/sent on all users. Can anyone give me a jumping point to start from?


    Thanks,


    Bill

  • Conditional Format Formula Help

    Hello All,


    Posting here to see if it can be done through this method first before going the Macro route. I'm looking to add a Conditional Format formula to Column CI that highlights based on the following criteria:


    If Column P2 <= 1/22/17 And Column CI2 = "Option A" Or Column CI2 = "Option B" Hightlight Green
    Or
    If Column P2 >= 1/23/17 And Column CI2 = "Option A" Or Column CI2 = "Option B" Highlight Red
    Or
    If Column P2 = "" Then Column O2 <= 1/22/17 And Column CI2 = "Option A" Or Column CI2 = "Option B" Highlight Green
    Or
    If Column P2 = "" Then Column O2 >= 1/23/17 And Column CI2 = "Option A" Or Column CI2 = "Option B" Highlight Red


    The critical piece that's holding me back here is that if P2 is blank then look at O2 and do the comparison. I'm not even too worried about the green highlights, I mostly need the red highlights as these are problem/issues that need correcting.


    Basically, I need to look at Column P2's date and if it's On or After 1/23/17 and Column CI reflects Option A or Option B (instead of showing Option C as it's supposed to) I need it to highlight red.
    Subsequently, there is a chance Column P2's date is blank. If it's blank, I need to look at Column O2's date for the same situation (if it's On or After 1/23/17 and reflects Option A or Option B) and highlight red.


    Thanks,


    Bill

  • Excel VBA Autofilter Greater than, Less than, and Blanks

    Re: Excel VBA Autofilter Greater than, Less than, and Blanks


    If I run that IF you provided above. There are 20,000 rows of data. The code I attached in my last post works exactly as I need it to for other columns. I'd like to have it apply to this column but instead of it searching for Vendor A, Vendor B, and Vendor C, I'd like for it to search for >10,<1, and blanks.

  • Excel VBA Autofilter Greater than, Less than, and Blanks

    Re: Excel VBA Autofilter Greater than, Less than, and Blanks


    Hello,


    Thank you for the post but this doesn't exactly solve my problem. The reason being is that the code takes a while to run and doesn't properly highlight what it needs to. I also need it to conditional format so that I can easily remove the CF and maintain the original highlight and formatting. This formula overrides the highlights. I'd prefer to modify logic I already have, but I just can't seem how to incorporate a formula into the search criteria. Instead of saying Vendor A, Vendor B, and Vendor C, I'd like it to update with logic that searches for >1, <10, and Blank:


    Code
    With Columns("C:C")
    .FormatConditions.Add Type:=xlExpression, Formula1:="=OR(ISNUMBER(SEARCH(""VendorA"",$C1)),ISNUMBER(SEARCH(""VendorB"",$C1)),ISNUMBER(SEARCH(""VendorC"",$C1)))"
    With .FormatConditions(.FormatConditions.Count)
        .SetFirstPriority
        With .Interior
            .Color = RGB(255, 217, 102)
        End With
        StopIfTrue = False
    End With
End With

  • Excel VBA Autofilter Greater than, Less than, and Blanks

    Re: Excel VBA Autofilter Greater than, Less than, and Blanks


    Hi Bryce,


    I grabbed this formula from another filter I'm using. In that one I'm doing a search for three different letter combinations, highlighting them, and then removing them from the population. I figured I could adapt that to this scenario. At the end of the day, as long as I have all results that are greater than 10, less than 1, or blank, it doesn't matter to me how I get there. You know? Once filtered, the remaining data is copy/pasted along with the other rows into a new sheet.


    Bill

  • Excel VBA Autofilter Greater than, Less than, and Blanks

    Re: Excel VBA Autofilter Greater than, Less than, and Blanks


    Perhaps a simpler solution. I have the following code that searches a field for a string of text. It then conditional formats this field and filters them out. How can I apply this to conditional format by formula, so highlight everything between 1 and 10 and then filter it out?



    I tried something like this, but it doesn't work:


  • Excel VBA Autofilter Greater than, Less than, and Blanks

    Cross posting as the last post received no responses. Link is here: http://www.mrexcel.com/forum/e…han-less-than-blanks.html


    Hello All - Happy Friday! Looking for some quick assistance. I have a macro that is autofiltering for all values greater than 10 or less than 1, but I also need it to include blanks. Code is embedded below. Any ideas? I am successfully filtering everything greater than 10 and less than 1, but I'm not picking up the blanks and I cannot edit these fields.


    Code
    [/COLOR]Rng.AutoFilter Field:=15, Criteria1:="<1", Operator:=xlOr, Criteria2:=">10"

  • Array AutoFilter VBA

    Re: Array AutoFilter VBA


    I'll be sure to do that. I must have missed the section about cross posting.


    Bryce - I had cross posted and was provided a clean solution very similar to my original code that resolved all of my functionality issues. Code below for reference. Thank you for your help on this because your responses helped me out on this and a few other issues.


  • Array AutoFilter VBA

    Re: Array AutoFilter VBA


    Thanks Bryce! This is 95% where I need to be. The only thing is that it's also copying over the header each time. So if I have three filters, it copies over the header for each one. Is there a line of code that can search for repeats of the header, delete the line, and shift everything up?


    Bill

  • Array AutoFilter VBA

    Re: Array AutoFilter VBA


    Hi bryce,


    I'm looking to have the sheet filter and copy all visible rows to a new sheet. The macro is a form that filters out data in columns 6, 7, and/or 13 based on user inputs. The results then spot out on a newly created sheet that then renames itself based on today's date and adds a 1, 2, 3 etc if the dated sheet already exists.

  • Array AutoFilter VBA

    Re: Array AutoFilter VBA


    Hi Bryce, the above is incorrect. It's not overwriting the last row, it's pasting into A1 - so if I have 8 rows, 4 rows, and 2 rows, it's pasting each into cell A1 and overwriting the first few rows of each result.

  • Array AutoFilter VBA

    Re: Array AutoFilter VBA


    Hi Bryce,


    I played around with the code a bit. This is the closest I've ever been. I'm just having one issue. If I filter on two data points, and let's say the first filter is 8 rows and the second filter is 14 rows, when it pastes the second filter in, it overwrites the last row of the first filter. Any ideas?


  • Array AutoFilter VBA

    Re: Array AutoFilter VBA


    Hi Bryce,


    I'm not sure I follow. The array is contained within the initial IF statement that builds it out:


    Code
    With Range("R1:S99") ' 2 columns, any # of rows
    For x = 1 To .Rows.Count
        If .Cells(x, 1) Then
            y = y + 1
            ReDim Preserve arrResults(1 To y)
            arrResults(y) = .Cells(x, 2)
            End If
    Next x
End With


    Each value returned would be added to arrResults. This is why the next section only filters based on arrResults as it's expected there would be multiple filter criteria:


    Code
    Sheets("All Call Center Detail").Select
If y > 0 Then Rng.AutoFilter Field:=13, Criteria1:=arrResults
If Not str1 = "" Then Rng.AutoFilter Field:=6, Criteria1:=str1
If Not str2 = "" Then Rng.AutoFilter Field:=7, Criteria1:=str2


    You mentioned looping through the array. Are you saying that I would need to loop through the code line multiple times for each filter? I thought the purpose of the array was to be able to filter a column on multiple criteria (as if I manually went in and checked the 3 fields I want to filter by)


    Bill

  • Array AutoFilter VBA

    Hello All,


    This is my first post on the site so please forgive any posting errors. I am working on a macro that creates and adds data to an array. The array then filters based on the results. The issue is that the array runs but only filters based off of the last field in the array. Please note the following:


    * Column R contains True/False indicators. The true/false corresponds with a value in Column S.
    * The IF statement cycles through and for every "True" found in Column R, it adds the value in Column S to the array - titled arrResults()
    * If there are three values marked true, all three are added to arrResults() - as confirmed in the immediate window and the debug statement found in the code
    * When the filter happens on Column 13, it only filters based only on the last field (3rd True).


    Please take a look at the code and let me know any suggestions. I just need to know why AutoFilter only recognizes the last field in arrResults and does not recognize any of the others.


    Thanks,


    Bill