Glad it worked for you, you're welcome !
Posts by gijsmo
-
-
I would normally use error handling as well however in this specific case it should not be required because the autofiltering was based on an array of unique values derived from the data in the column being filtered
-
As I indicated in my earlier example, the code worked with the original data provided.
No error handling should be necessary because the SpecialCells in this case was based on the unique values in Col G in the original sample.
The new data you provided however uses Col F instead of G and it includes 'unformatted' data such as "1.46505E+14" which needs special handling.
The change from Col G to Col F and the 'unformatted' values now included in Col F should be able to be handled by the attached revised example. This currently works on the sample provided, there are on guarantees if this data is not representative of the actual data or if, for example, the data format changes.
-
The attached example should do what you want, based on the information provided.
There is a 2nd copy of Sheet1 - just to enable copying the data back for testing purposes.
-
One way to process the input file into the output you have suggested is via a stand-alone macro as per attached:
This will process all the sheets in the specified input file (based on the sample provided) and write the results to the specified output file.
The "template" used for output file is stored as the Output sheet in this stand-alone macro.
Based on the sample provided, the output file looks as per attached:
-
What do you expect the "File used" value to look like (on the Output sheet) when there are multiple files making up the formula eg:
or:
In the 1st example, the 2 files referenced happen to be the same but in the 2nd example there are 3 files in the formula and 2 of those are unique.
-
-
I have attached a generic file lister that may be more than you need but it will derive a list of video files and a bunch of related information like duration, aspect ratio, etc etc. It can also be used to list eg image files, audio files or just as a general file lister (depending on what you value select in the "Extra Data to Show" drop list).
It takes as input the folder that contains the files to be listed and the folder where you want to write the output file to. For video files, the output filename is in the format of Video List dd-mm-yy.xlsx
There is one small catch to this. It requires the MediaInfo DLL to extract the metadata from the files. More specifically, it requires the 32bit version of the DLL (the macro is only written for 32 bit version of Excel).
The 32 bit MediaInfo DLL for Windows can be obtained from here: https://mediaarea.net/en/MediaInfo/Download/Windows
Best to just grab the DLL file in zip format as you will need to place this file on the windows path somewhere (eg: C:\Windows\System32). Also, the file needs to be renamed to MediaInfo32.DLL to work with this macro (to avoid confusion in case you have the 64 bit version of the MediaInfo GUI installed).
If you want to place the file in another folder, you will need to modify the MediaInfoLibDLL class module and adjust all the declarations eg:
From:
To:
If you're comfortable doing that, this will provide a decent list of file details.
There are a bunch of other options that you can play around with but hopefully most of them are self-evident.
-
Thanks for letting me know, you're welcome
-
One way to do this is via a user defined function (UDF) using regular expressions (regex) as per attached example
-
I don't think there is a ColorIndex property as such for shapes, maybe take a look at ActiveSheet.Shapes("TA_Forfeit").Fill properties
https://docs.microsoft.com/en-…/vba/api/excel.shape.fill
eg: to set the forecolor:
-
You are welcome, thx for letting me know.
-
-
Thanks for the kind words, glad it worked for you.
With family in Australia, particularly New South Wales, you're probably aware that covid is becoming as unmanageable here as other parts of the world...but still comparatively mild compared to UK numbers.
I don't think we'll be travelling overseas any time soon, let alone within Aus at the moment.
Stay safe in the meantime.
-
You are most welcome & a celebratory beer would be fantastic given our ongoing lockdowns in Australia !...so I'll just have to drink one in your honour
In terms of that change you require for your "live" sheet, you should be able to do this by changing the line of code in the Create_Report sub from:
to:
or if you want formatting as well as values, you could alternatively use:
For completeness, I've updated the last copy with the above changes.
-
The layout on this new report sample is somewhat different to the original sample provided so the code, whilst generic, was not going to work as is. With extra row headers above the main data and report fields and with the data grouping, the approach needs to be somewhat different.
Taking the revised layout into account and with some interpretation of where I think you are heading with this, I've attached an updated version which I think now correctly creates the Platinum report. It should also work for Gold. I have intentionally not created a sheet for Silver or Bronze, just so you can see how the code works in that scenario. This also means that macros are now attached to each of the Report buttons on the Report Generation sheet.
-
You are welcome.
-
As per Roy's comment, not sure of the value of having the difference between the dates popping up as a message.
However, conceptually this is possible to do as per attached example, code is in the Worksheet_Change function.
-
Combining the arrays from vcoolio's code into my original, I've attached an updated version
-
If you want a more traditional approach or have no scope to install the Power Query AddIn try the attached.