+ Reply to Thread
Results 1 to 38 of 38

Macro assistance looking up variable data with autofilter

  1. #1
    Forum Contributor
    Join Date
    10-13-2011
    Location
    Australia
    MS-Off Ver
    Excel 2020
    Posts
    308

    Macro assistance looking up variable data with autofilter

    I have problems with the code: When the filter is on, it is looking for actual criteria, i.e. date "1/4/2014" and "AHCLSK505A" and "S1" and "Site 1"
    These are part of the data set I need to filter but need it to filter the first option in the filter, so it can be repeated each time with different data.
    What do I need to change it to? thanks
    Code removed, no longer relevant
    Last edited by Christopherdj; 12-04-2014 at 08:05 PM.

  2. #2
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: Macro assistance looking up variable data with autofilter

    Something like this?

    Please Login or Register  to view this content.
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  3. #3
    Forum Contributor
    Join Date
    10-13-2011
    Location
    Australia
    MS-Off Ver
    Excel 2020
    Posts
    308

    Re: Macro assistance looking up variable data with autofilter

    Thanks for your reply. Sorry I've not explained myself with the initial query.
    I don't want the criteria defined, I want the filter to select the first date available, then in subsequent columns, the first set of data available based on using the filter.
    Each time the macro runs, it will find a different date and then the subsequent filters may find different data sets, based on the sequence of filter being applied.
    Last edited by Christopherdj; 12-02-2014 at 09:13 PM.

  4. #4
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: Macro assistance looking up variable data with autofilter

    So where is it getting the date?? Your solution still resides in making the criteria variable?? Is it the Array(1,"")??

  5. #5
    Forum Contributor
    Join Date
    10-13-2011
    Location
    Australia
    MS-Off Ver
    Excel 2020
    Posts
    308

    Re: Macro assistance looking up variable data with autofilter

    This has been removed as no longer relevant.
    Attached Files Attached Files
    Last edited by Christopherdj; 12-02-2014 at 09:03 PM. Reason: Updated Macro

  6. #6
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: Macro assistance looking up variable data with autofilter

    Hi Chris,

    I'll look at this tomorrow if no one solves the issue in the interim

  7. #7
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: Macro assistance looking up variable data with autofilter

    Hi Chris,

    I've trimmed the first part of your code, but I need guidance on the rest


    Please Login or Register  to view this content.

  8. #8
    Forum Contributor
    Join Date
    10-13-2011
    Location
    Australia
    MS-Off Ver
    Excel 2020
    Posts
    308

    Re: Macro assistance looking up variable data with autofilter

    Thanks for what you've done so far.
    From below your query line, it may be best to revist the macro function. It was my feeble attempt to clean unwanted data from the file - prior to saving as a "*.csv commoa delimited" file.
    Is there an easier/cleaner way to do this?
    Would it be better to simply copy the data from the Excel file, then paste into a new sheet, then save as *.csv ? The .csv file name needs to be the data in U3 of the required data.

    When the .csv is complete, I'm looking for the Saved Excel file (as saved above) to reopen, and restart the process all over again.
    This would continue to create different *.csv files until there were no filtered results left in the Excel file.
    Does this make sense?
    I really appreciate your assistance. Thanks

  9. #9
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: Macro assistance looking up variable data with autofilter

    Hi Chris,

    Instead of reopening the file over and over, couldn't you just reuse it?

  10. #10
    Forum Contributor
    Join Date
    10-13-2011
    Location
    Australia
    MS-Off Ver
    Excel 2020
    Posts
    308

    Re: Macro assistance looking up variable data with autofilter

    Yes, I agree with your question about reusing the file, if that is the way it needs/can be done. I made an assumption that once the *.csv was created, and saved then closed, I'd have to reopen the excel file to resume the process again. Not being familiar with the programming language makes it difficult. The concept is to take required data and place/save it as a csv file, then continue with the data in the Excel file until "YES" is in all of column S and no results are returned. I used an "X" in column V to be able to filter either required data or non required data. At all times, I need the original data kept in the excel file, the only change/addition are the "YES" entry in column S and obviously the "X" in Column V.
    Last edited by Christopherdj; 12-02-2014 at 09:04 PM.

  11. #11
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: Macro assistance looking up variable data with autofilter

    Yeah - you should be able to just peel them off using the Autofilter and the Filter reset! we'd need to know the criteria for each "leaf" however.

  12. #12
    Forum Contributor
    Join Date
    10-13-2011
    Location
    Australia
    MS-Off Ver
    Excel 2020
    Posts
    308

    Re: Macro assistance looking up variable data with autofilter

    I've just updated the Macro as per your sheet, in my reply at 1.40 yesterday (the one with the last file upload from me).

    1. There is an issue with the saving as .csv, I want it to be the same name as the sheet name, which is a paste from data in U3.
    What I've done is created a macro that has copied the filtered data then opened another sheet in the same workbook, pasted into that sheet, renamed it as per data in "U3", saved as .csv and deleted the first sheet then saved again. Even though I created the macro, the file name section of the macro needs fixing. Now I've worked out the SaveAS .csv issue (except for naming the file - hope you can help), that may resolve this section of the puzzle.
    2. I don't understand "the peel off using auto filter" comment, can the date/s be gathered from column R to create the list (or leaves) to peel from (also see **** below). Or,
    3. The date recognition as you suggest in Array1 is still a bug in the macro, the date is variable, as it will change each time the macro runs, so it needs to select just the first instance in the filter each time. ** I have been surfing this site for clues and may have stumbled on a solution for the date problem for "Array1" (Date). If the date to filter by was = to a cell reference, i.e. Column R, then as the filter selects "NOT Yes" or (= Blanks) in column "S", then the cell reference adjacent to the first result in column R would be the date to go by. This would allow for a change each time the Macro runs. (Does this assit in any way?)
    4. The date in colum R is typed as 25/05/2014 (AUS) and has been formatted to reflect dd-mmm-yy, but when copied as part of the Macro at column U, it populates the date as numbers which I don't want. The macro extracts data from "CODERemvoed" that forms part of the file name, the date of which is rather critical in recognising the file details.
    5. The other filtering options - are also variable, and must only select the first option available each time, which changes each time the macro is run. They shouldn't specify specific text like "AHCLSK505A" nor a campuse "Site 1" nor Study Period Description "S1", just what appears first as it applies each of the filters.
    **** Could these also be gathered from each of the relative columns to then create the leaves to select from as per your peeling option suggested?
    6. When the function works correctly, it needs to loop back to repeat the process until such time as no data is remaining via the filters.
    That is why the "Yes" is placed in column S to exclude that line in the next sequence of running the macro.

    Surprisingly, I actually feel like it is coming together, it's just not quite there yet - but progessing well.
    I feel so out of my depth with this - thanks for your patience and assistance thus far.
    Last edited by Christopherdj; 12-02-2014 at 09:41 PM.

  13. #13
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: Macro assistance looking up variable data with autofilter

    Hi Chris,

    I put the "leaves" in Sheets - see if you like the routine
    Attached Files Attached Files

  14. #14
    Forum Contributor
    Join Date
    10-13-2011
    Location
    Australia
    MS-Off Ver
    Excel 2020
    Posts
    308

    Re: Macro assistance looking up variable data with autofilter

    Ok, the sequence works, thanks.
    Looks like you've solved 2, 3, 4, 5 above - great job, thanks!!


    The only issues I see are - 1 & 6 above and,

    1. It only works once, if I try to repeat the sequence, (with YES against the April data), it doesn't find any more data.
    2. The sequence didn't create January first - I thought it would generate the file/s by month, but is it by Alpa? (if Alpha, that is ok)
    3. The file created April data, with 2 differing dates - it should create only one file with (in this instance) only one record.
    4. Can the date applicable appear as 05-04-2014 (which is the Australian version) we use Day-Month-Year format.

    Looking very close to an outcome though.
    Last edited by Christopherdj; 12-02-2014 at 08:33 PM.

  15. #15
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: Macro assistance looking up variable data with autofilter

    Hi Chris,

    This should fix your date - as to the April records, there are two separate dates the 5th and the 30th?? If you want the "leaves" in chronological order, we could sort the records before we process them:

    Please Login or Register  to view this content.

  16. #16
    Forum Contributor
    Join Date
    10-13-2011
    Location
    Australia
    MS-Off Ver
    Excel 2020
    Posts
    308

    Re: Macro assistance looking up variable data with autofilter

    I get an error, which when I run the debug is at:
    Please Login or Register  to view this content.
    Not sure what's changed in the formula?

    If it could sort by date order, and only one sheet Per Date, not one sheet per Month.

  17. #17
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: Macro assistance looking up variable data with autofilter

    Hi Chris,

    This happens with the file I sent you even after all the YES's are erased?? I'll have to look at it again tomorrow

  18. #18
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: Macro assistance looking up variable data with autofilter

    Please Login or Register  to view this content.
    Hi Chris try this - with your csv code in place of the red

  19. #19
    Forum Contributor
    Join Date
    10-13-2011
    Location
    Australia
    MS-Off Ver
    Excel 2020
    Posts
    308

    Re: Macro assistance looking up variable data with autofilter

    The file creates another csv workbook correctly, but does not saveAs, this may be due to the Code not being replicated into Cell U3. The Filename created in column U is only appearing in cell U3 whereas, the code I had placed it into all cells in column U. I don't need the data in the original excel file, it only produces the data required for naming of the new sheet/csv file if that helps.

    This is the code I inserted.
    Please Login or Register  to view this content.
    and:
    I just realised the sheet name date may be Australian, the code is picking up 4th May, I thought it was 5th April. So when I changed the 4th April dates to 13 April, on running the code again, it came out as 4-05-2014, which is the first date in R4. and:
    When I re-run the Macro, it doesn't work. It finds no results? Debug finds the error at:
    Please Login or Register  to view this content.
    So basically, it is running the process for the first instance, and when I run it a second time it doesn't work.
    Last edited by Christopherdj; 12-03-2014 at 07:37 PM.

  20. #20
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: Macro assistance looking up variable data with autofilter

    OOps - not working deleterious
    Last edited by xladept; 12-03-2014 at 08:53 PM.

  21. #21
    Forum Contributor
    Join Date
    10-13-2011
    Location
    Australia
    MS-Off Ver
    Excel 2020
    Posts
    308

    Re: Macro assistance looking up variable data with autofilter

    OMG, Almost......
    the sheet name date may be Australian, the code is picking up 4th May, I thought it was 5th April. So when I changed the 5th April dates to 13 April, on running the code again, it came out as 4-05-2014, which is the first date in R4, I think the file name needs to populate all active cells in column U so when the filter is applied, it will give the correct filename applicable to the data.
    and:
    When I re-run the Macro, with the updated "YES" data it doesn't work. It finds no results? It should be finding the next date for a new .csv file. Debug finds the error at:
    Please Login or Register  to view this content.
    So basically, it is running the process for the first instance, and when I run it a second time it doesn't work.
    Is it the filtering option is still applied? I am checking to see if that my be the issue but if you can see the problem, let me know. thanks again
    Last edited by Christopherdj; 12-03-2014 at 11:21 PM.

  22. #22
    Forum Contributor
    Join Date
    10-13-2011
    Location
    Australia
    MS-Off Ver
    Excel 2020
    Posts
    308

    Re: Macro assistance looking up variable data with autofilter

    The code loops really well without the "Yes" component, but I see it is what places the "YES" into the cells of the excel sheet.
    Is the AutoFilterMode = False in the right place? or Should it be Show all data?
    I'm struggling for answers, much beyond my capabilities I'm afraid. No solutions here yet
    I've changed the code slightly from the Makebooks section, now reads
    Please Login or Register  to view this content.
    Sorry - I was updating this post as you replied.
    Last edited by Christopherdj; 12-04-2014 at 01:17 AM.

  23. #23
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: Macro assistance looking up variable data with autofilter

    It's almost there, but I don't understand the date thing

  24. #24
    Forum Contributor
    Join Date
    10-13-2011
    Location
    Australia
    MS-Off Ver
    Excel 2020
    Posts
    308

    Re: Macro assistance looking up variable data with autofilter

    The date to be used for the file name should be the date applicable to the row entry after the filter is applied. Maybe if we filter out blanks from the "Effective Date" as the first step (a new step in the code, column R) then filter by "Completed (Column S)" and so on... When the data is filtered, the filename information should come from the respective row 3 (the first line of filtered data), so it will change each time the code runs, creating a new file name to be saved. Also, see my previous comment/post re updated code.

  25. #25
    Forum Contributor
    Join Date
    10-13-2011
    Location
    Australia
    MS-Off Ver
    Excel 2020
    Posts
    308

    Re: Macro assistance looking up variable data with autofilter

    Hey Xladept - hope you had a good sleep last night. I ran the file from home on my personal PC and apart from the date format it worked by looping and creating the csv files. It seems the problem may be the version of excel I'm running from work. My version at home is 2010. I'll have to check when I get to work as to the version there, however I thought it was the same?? Unless it is newer than 2010? Please keep in mind the timezone differences. It is starting to make some sense now - especially if the problem is my end.
    Last edited by Christopherdj; 12-04-2014 at 04:49 AM.

  26. #26
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: Macro assistance looking up variable data with autofilter

    Hi Chris,

    This looks like a go to me - but are you going to run it twice?

    Please Login or Register  to view this content.

  27. #27
    Forum Contributor
    Join Date
    10-13-2011
    Location
    Australia
    MS-Off Ver
    Excel 2020
    Posts
    308

    Re: Macro assistance looking up variable data with autofilter

    Hi xladept - I'm running Excel 2010 both at work and home. But when I run this file at work, it creates only one file with the wrong date against it. When I ran it from home last night it continued to create all the files I wanted.
    Now I understand what you've been questioning, the script you've created does loop and create the files as needed. My software/PC/version (I'm confused) at work seems to be the issue. That is why I've been saying when I run it a second time it doesn't work. The error I receive at work is Run-time error '1001': No cells were found and when I run the Debug, it stops at
    Please Login or Register  to view this content.
    and it only creates the one csv file. Then stops with the error. Do you use Lync? I'd like to actually show you what it's doing if that's possible.
    Can you add your code to the file I've attached here and confirm it works your end - it should create a large number of .csv files with different dates. I'm having trouble at work as the filters return a "nil" result after the first run (creating one file only), that's why it stalls with the error.
    Attached Files Attached Files
    Last edited by Christopherdj; 12-04-2014 at 07:39 PM.

  28. #28
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: Macro assistance looking up variable data with autofilter

    Maybe:

    Please Login or Register  to view this content.

  29. #29
    Forum Contributor
    Join Date
    10-13-2011
    Location
    Australia
    MS-Off Ver
    Excel 2020
    Posts
    308

    Re: Macro assistance looking up variable data with autofilter

    xladept, I think you have the right vba code, I've transferred to a work laptop and the files seem to be creating ok. I'm going to solve this query - and I thank you so much for what you've done. If I run into futher issues, I'll message you direct.
    I really appreciate the time and effort you've placed into this, my apologies for the confusion - but is seems there are some software issues my end.

  30. #30
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: Macro assistance looking up variable data with autofilter

    You're welcome and thanks for the rep!

  31. #31
    Forum Contributor
    Join Date
    10-13-2011
    Location
    Australia
    MS-Off Ver
    Excel 2020
    Posts
    308

    Re: Macro assistance looking up variable data with autofilter

    Wondering if you could assist just one more thing with the formula please.

    I'd like the date swithced so it appears at the end of the file names if possible, and not inbetween other information.

  32. #32
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: Macro assistance looking up variable data with autofilter

    Hi Chris,

    Try this:

    Please Login or Register  to view this content.

  33. #33
    Forum Contributor
    Join Date
    10-13-2011
    Location
    Australia
    MS-Off Ver
    Excel 2020
    Posts
    308

    Re: Macro assistance looking up variable data with autofilter

    Stalls at this function, can you help?

    Please Login or Register  to view this content.
    Is it that criteria2 is not defined?
    Last edited by Christopherdj; 12-07-2014 at 10:07 PM.

  34. #34
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: Macro assistance looking up variable data with autofilter

    I'll have to look at it on my other machine tomorrow - it ran slick for the first book?

  35. #35
    Forum Contributor
    Join Date
    10-13-2011
    Location
    Australia
    MS-Off Ver
    Excel 2020
    Posts
    308

    Re: Macro assistance looking up variable data with autofilter

    OK, - issue found.
    I have changed the date format on my laptop to show dates as per Australian dates - this is the issue? I'd prefer dates to appear as dd-mmm-yy. When I reverted back to mm-ddd-yy it worked.

    Can you change the settings so it reflects AUS english and reset the VBA code to match? I'm using it as you've produced, but the output is as confusing to us as it is to you being in reverse.
    Last edited by Christopherdj; 12-07-2014 at 10:51 PM.

  36. #36
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: Macro assistance looking up variable data with autofilter

    Try this:

    Please Login or Register  to view this content.

  37. #37
    Forum Contributor
    Join Date
    10-13-2011
    Location
    Australia
    MS-Off Ver
    Excel 2020
    Posts
    308

    Re: Macro assistance looking up variable data with autofilter

    Thanks XLADEPT, at least I got to the bottom of the issue. Seems it was software, who'd have thought USA/AUS English default would be the issue.
    thanks again.

  38. #38
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: Macro assistance looking up variable data with autofilter

    You're welcome!

+ 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. Autofilter Macro Failing because object variable not set
    By dcgrove in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-30-2016, 02:54 AM
  2. Looping through a variable with macro and autofilter
    By CLoos in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-20-2013, 06:06 PM
  3. [SOLVED] I have macro for autofilter i need suprate sheet for ever variable
    By harishb63 in forum Excel Programming / VBA / Macros
    Replies: 19
    Last Post: 08-28-2013, 03:23 AM
  4. How can I add a 3rd AutoFilter Variable in a Macro?
    By jlax34 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-23-2012, 09:09 AM
  5. [SOLVED] autofilter macro using variable
    By 86gts in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 07-31-2012, 09:51 AM

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