VBA, Advanced Filter

  • Re: VBA, Advanced Filter



    1) Macro is in a different workbook, the start up workbooks pretty much . So I would open the sample file and then run it from those personal workbook files.


    2) Yes it shouldnt be column C. It should be 'Scenario Name' column. That is completely wrong. Sometimes I've had to change this because the column I filter differs. I am working with a directory of files where this column I am filtering changes. "Scenario Name" is the header that should always be filtered. and be where the formula should be.


    3) and Isnt what I posted the same code as what you had? I didnt know what part you meant to remove.

  • Re: VBA, Advanced Filter


    I'm not sure if this is exactly what you want, as it now only copies the Scenario Name from column E, instead of also copying the 'Node' from column A.


    I have re-ordered the activities so that you only need to loop once through the worksheets, instead of three times.


    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]

  • Re: VBA, Advanced Filter


    Wow thanks works really good, but I also need the node column lol, But this is really really good, thanks so much!
    How would I include that aswell? As in the same format you have it, I like how you formatted the new sheet.


    Quote from Batman;754640

    I'm not sure if this is exactly what you want, as it now only copies the Scenario Name from column E, instead of also copying the 'Node' from column A.


    I have re-ordered the activities so that you only need to loop once through the worksheets, instead of three times.


  • Re: VBA, Advanced Filter


    Hi,


    Try this:


    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]

  • Re: VBA, Advanced Filter


    Thanks so much works.
    I have a question, If the col's I want change from file to file, how could I filter it by actual header name? I like the exact way you did it, but from file to file the header changes columns, so A and E will not always be applicable.

  • Re: VBA, Advanced Filter


    Firstly, you need to find the column header name in row 1 - see the Match function statements I have inserted. You need the error trap around that as Match will error if what you are looking for is not found.


    You then need to refer to the columns to use using the column number, as returned by Match and stored in the two new variables (intColNode and intColScenario). This is easier within the Cells object rather than Range, so I have changed that where necessary.


    I have assumed that the column in which you might be able to place the formula re the Scenario extract might also need to be variable. I have assumed that to be 1 column after the last column heading in row 1. The formula to extract the Scenario data has also been updated to reflect the variable nature of the Scenario column.


    I have also inserted new If statements to test for the Match function not finding the column headers.


    This is the latest code:

    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]

  • Re: VBA, Advanced Filter


    Your the best!!!! thanks for all the explanation you have been more then helpful.



    If I wanted to add or change the character I split the fields with, how would I modify? Like if I also wanted to split by + or - for example.


    Code
    ' Assign formulas to the next free column to identify the scenario name to the left of the first _ character
                            .Cells(1, intColNext).Value = "Test"
                            lr = .Cells(.Rows.Count, intColScenario).End(xlUp).Row
                            Set myrg = .Range(.Cells(2, intColNext), .Cells(lr, intColNext))
                            With myrg
                                .ClearContents
                                .FormulaR1C1 = "=IFERROR(LEFT(RC" & intColScenario & ",FIND(""_"",RC" & intColScenario & ",1)-1),LEFT(RC" & intColScenario & ",2))"
                                .Value = .Value
                            End With
  • Re: VBA, Advanced Filter


    You are building in VBA the same formula that you would build in Excel. VBA is actually going to place that formula into the worksheet and calculate it, just as if you had entered it manually.


    Are you able to build a formula in Excel that will cater for the different characters you might use? If not, I would need to know what possible situations might arise in order to build a formula. Once we have the formula that works in Excel, we can translate that into the code needed to build it in VBA.

    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]

  • Re: VBA, Advanced Filter


    Quote from Batman;754736

    You are building in VBA the same formula that you would build in Excel. VBA is actually going to place that formula into the worksheet and calculate it, just as if you had entered it manually.


    Are you able to build a formula in Excel that will cater for the different characters you might use? If not, I would need to know what possible situations might arise in order to build a formula. Once we have the formula that works in Excel, we can translate that into the code needed to build it in VBA.


    Yes I have a formula, but it needs to be ctrl+shift+enter, something like this


    =LEFT(A2, MIN(ROW(INDIRECT("1:"&LEN(A2)))+(((CODE(MID(UPPER(A2), ROW(INDIRECT("1:"&LEN(A2))),1))>64)*(CODE(MID(UPPER(A2), ROW(INDIRECT("1:"&LEN(A2))),1))<91))+((CODE(MID(A2, ROW(INDIRECT("1:"&LEN(A2))),1))>47)*(CODE(MID(A2, ROW(INDIRECT("1:"&LEN(A2))),1))<58)))*1E+99)-1)



    But the example I quoted in my above post, doesnt that separate values based on '_', cant we just modify that to include other characters to separate by?


    Like the part you included in the previous code you supplied, cant we slightly modify that?


  • Re: VBA, Advanced Filter


    You can modify the VBA code to search for exactly what you want by simply replacing the _ (underscore) character with whatever character you want to find. So for example if the separator was a + character, the code would need to be

    Code
    .FormulaR1C1 = "=IFERROR(LEFT(RC" & intColScenario & ",FIND(""+"",RC" & intColScenario & ",1)-1),LEFT(RC" & intColScenario & ",2))"


    What I don't know is whether you will have a workbook that contains a mixture of different separators, in which case just searching for a single specific character will not work.


    I was also trying to avoid having to keep changing the program to make it work every time you run it.


    I remember your array formula above from a previous thread, as I recall wondering whether it was actually doing what you wanted it to do. Your requirement was to remove any non-alphanumeric characters, whereas what the formula does is to return all the alphanumeric characters to the left of the first non-alphanumeric character. We also had the problem at the time that the array formula was too long to create in VBA.


    Do you have a defined list of characters that might act as a separator, or could it be anything that is not in the range 0-9 / a-z / A-Z?

    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]

  • Re: VBA, Advanced Filter




    I agree, So where you put the +, we cant add more in there ?


    Yes I have a defined list: '_', '-', '+', '-' , '$', '%' , those should be about it.

  • Re: VBA, Advanced Filter


    As far as I can tell, the 2nd and 4th characters in your list are the same, so I have omitted one of them.


    If you just tried to insert all of those characters into the formula as it stands, the formula would be looking for a single string containing all those characters in the order they are written. That of course will never happen.


    The formula that would be written in Excel would be (if looking at cell A1):


    =IFERROR(LEFT(A1,FIND(INDEX({"+","-","_","$","%"},1,MATCH(1,--(ISNUMBER(FIND({"+","-","_","$","%"},A1))),0)),A1)-1),A1)


    By rights, it ought to be an array formula, but for some reason it seems that the inclusion of ISNUMBER allows you to enter it as a standard formula. That would convert in VBA in your scenario to:

    Code
    .FormulaR1C1 = "=IFERROR(LEFT(RC" & intColScenario & ",FIND(INDEX({""+"",""-"",""_"",""$"",""%""},1,MATCH(1,--(ISNUMBER(FIND({""+"",""-"",""_"",""$"",""%""},RC" & intColScenario & "))),0)), RC" & intColScenario & ")-1), RC" & intColScenario & ")"

    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]

  • Re: VBA, Advanced Filter


    Thanks alot,
    Couldnt I alter this code from your previous post?


    Code
    .Cells(1, intColNext).Value = "Test"                        lr = .Cells(.Rows.Count, intColScenario).End(xlUp).Row
                            Set myrg = .Range(.Cells(2, intColNext), .Cells(lr, intColNext))
                            With myrg
                                .ClearContents
                                .FormulaR1C1 = "=IFERROR(LEFT(RC" & intColScenario & ",FIND(""_"",RC" & intColScenario & ",1)-1),LEFT(RC" & intColScenario & ",2))"
                                .Value = .Value


    As I want to apply it to a header name not A1 for example. Also could I add it in where ""_"", and ""+"" , or is that not gonna work?

  • Re: VBA, Advanced Filter


    Yes, you would be applying it to that section of code. You would replace the last but one line of code, starting .FormulaR1C1, with the new line of code I gave you in post number 32.


    The use of A1 in the Excel example of the formula was just based on the Excel cell I tested the formula against (in my own dummy workbook, not yours) before I posted it. The actual VBA code will refer not to cell A1, but to a cell in the current row, the column being defined by the variable 'intColScenario'. The column number that will be held in intColScenario will have been defined earlier in the program by finding the header 'scenarioName' in row 1.


    Basically, you just need to replace as follows:

    Code
    ' Replace this line
    .FormulaR1C1 = "=IFERROR(LEFT(RC" & intColScenario & ",FIND(""_"",RC" & intColScenario & ",1)-1),LEFT(RC" & intColScenario & ",2))"
    '
    ' with this line
    .FormulaR1C1 = "=IFERROR(LEFT(RC" & intColScenario & ",FIND(INDEX({""+"",""-"",""_"",""$"",""%""},1,MATCH(1,--(ISNUMBER(FIND({""+"",""-"",""_"",""$"",""%""},RC" & intColScenario & "))),0)), RC" & intColScenario & ")-1), RC" & intColScenario & ")"


    You can copy and paste from this thread - you don't need to re-type it.

    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]

  • Re: VBA, Advanced Filter


    Thanks so much!!! Do you got any generic vba codes for looping this code through a directory?


  • Re: VBA, Advanced Filter


    That's a completely different question entirely, and would require a separate thread. Before you do that, I would suggest you search the forum, as I'm sure that will have been raised before.

    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]

  • Re: VBA, Advanced Filter


    back to post #34, if i have multiple - and + in my field, this code takes the splits based on the last one of those listed. So if its Batman-35252_3242, it will make it Batman-35252. Is there anyone to change it to split from the first one?

  • Re: VBA, Advanced Filter


    Even if there are multiple characters from your list in a single cell, the formula will find the first of those, not the last - that is the function of the MATCH part of the formula.


    If I go back to post #32, I pointed out that two of the characters in the list that you gave me appeared to be the same, a hyphen (-) character. Is it possible that they were in fact two slightly different characters, one of which is not in the formula's list to find?

    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]

  • Re: VBA, Advanced Filter


    Quote from Batman;754852

    Even if there are multiple characters from your list in a single cell, the formula will find the first of those, not the last - that is the function of the MATCH part of the formula.


    If I go back to post #32, I pointed out that two of the characters in the list that you gave me appeared to be the same, a hyphen (-) character. Is it possible that they were in fact two slightly different characters, one of which is not in the formula's list to find?


    Sorry those are the same characters. Thanks so much anyway.

  • Re: VBA, Advanced Filter


    Batman, When I get the results in my excel file in 'unique data' how would I fill the blank cells? For example for sheet name it will list it, but then there will be a few blanks before the next sheet name, how can I get these values to fill down till the next unique value?

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!