Posts by ChrisOK

    I have used this code to move batches of files from one folder to another (somewhat manually bc I have to hunt through a large batch of files to select what I want moved) but now need more specifics built in and not sure how? Hoping it's a good starting point that can be easily updated but if not, happy to try something new that will do the task!

    Current script does this:
    Excel file has a table that holds a list of file names
    Prompts user to choose SOURCE folder, Prompts user to then choose the MOVE TO folder

    Current task need:
    Look specifically at the last 5 characters of each cell in that list (for the Julian dates found in the last 5 chars of the file name) and either just SELECT THEM --- so I can then select the MOVE TO FOLDER and have them moved into their appropriate Fiscal Month Folder for Reconciliation.

    This orig script already allows me to pick the SOURCE folder and already allows me to then pick the MOVE TO folder so no code is needed for that part..
    Need it to look for matching Julian code, IF MATCH FOUND, then auto-select the matches in the file folder that match the Excel list/ for moving..(so I don't have to hunt n' pic through tons within a folder or list)

    End Result: I'll be able to easily move large batches of files from one place to another that all might have a Julian date of "19091" (April 1, 2019).

    Thanks, Chris
    link to sample included below:
    [FONT="Helvetica"]GO TO FILE [/FONT]…lab%2Binvite%20new%20user

    Attached is a sample of what I'm trying to accomplish. The user must submit their time in the format shown on the upper table using a hand-written paper copy. (yes old school for that employment agency)
    The lower table is a more straight-forward TIME IN and OUT table that makes the most sense to them.
    A new module was created and tied to the icon to allow them ease in generating the upper table based on what they've entered into the lower table.
    Not sure how to accomplish this conversion - but am confident there's got to be a way with a vba sub.. hoping someone knows a way..

    Thanks in advance!

    Re: VBA auto-fed Calendar Blocked View of Income, Expense and OMG Debt Status

    Woooo Hooo! So excited to show this to them and show them that their dreams have come true! Love the solution - thanks for the awesome help with the solution! I gave 5 starts on the reputation scale! SOOO very much appreciate your help -- now I've got to go study it to see if I can learn from it!
    Thanks again! :cheers: :flower: :rock: :thanx:

    Re: VBA auto-fed Calendar Blocked View of Income, Expense and OMG Debt Status

    INCREDIBLE! -- I added another "new car" on 8/15/17 and it worked!
    (updated all the balance info, added the new purchase and triggered an alert):wowee::yikes::thumbcoo:
    This is fantastic and I'm sure they'll actually enjoy working with their finances now! (and should now, NOT encounter the OMG moments as much):dance:

    One thing that was bit cumbersome was after the alert triggered, I looked at the calendar to see where things 'went read' - but "Jan" was what was visible...(so I had to scroll and scroll all the way down til' I could find AUG 2017 which is where things hit negative)

    Is there a way to make scrolling occur to whatever mo the "RED RED date's month" is that's already being listed within the alert?
    Or alternatively, at the very least -- to whatever calendar month their desktop shows as =TODAY...

    The first would be ideal, the 2nd alternative is still good..
    In other words, COL J (MO) + COL L (YR) would be Indexed in some way for a MATCH to be the same MO+YR of either (1) the delinquency or (2) to TODAY's mo if that's easier -- that would be fine too.. (at least it would eliminate the majority of scrolling)

    Re: VBA auto-fed Calendar Blocked View of Income, Expense and OMG Debt Status

    ROFL -- I literally had to laugh out loud --- Well, re: "OMG Debt Alarm" --
    A pop-up triggered by a negative bank balance would probably work if easy to trigger and look up the associated date?
    Some possible messages:
    "OMG! You'll be overdrawn by Aug 25 if you don't take actions prior!"
    "OMG! You spent WAY too much this month -- get a grip, buckle down or find 2nd job!"
    "OMG! When it rains it pours they say --- you've poured out too much and will SEE RED on 8/25"
    "TIME FOR A NEW CAR if that many repairs has put you into the RED on 8/25"

    (the list can go on -- with probably many more ---much better alternatives) - I'd like to see other Ozgriders submissions...but has appeared it's just you and me in this string for some crazy reason...!?!

    :party:I think it would be really fun to have that chunk of code built in -- to where if the user wants it turned on, I can go in the backside and toggle TRUE to FALSE or vice-a-versa.. and can edit msg as appropriate to the user's sense of humor level..

    Sadly, I know folks are struggling because they've lost their job due to layoffs, shutdowns, & things totally out of their control such as enormous medical bills are cutting into their once-balanced-budget... Sooo, having something that's a little more fun to manage the horrible topic of **DEBT** is the goal... (with hopes things will lighten up quicker - w/ the ease of at-a-glance visibility and debt be more expeditiously eliminated) :flower:

    Re: VBA auto-fed Calendar Blocked View of Income, Expense and OMG Debt Status

    OH WOW! That works great!!! Just tested it to feed a few INC and EXP items to Jan and Nov Dec and each fed through perfectly!
    I was laying in bed last night thinking I'd just start trying to find a lookup/index/sum combo formula and start plugging it in every calendar cell manually - for worse case scenario -- but YOU MADE MY DAY!! This is MUCH more efficient and works like a charm!

    If you know that the recurrence capability (originally sought) is doable -- this would be great to see when time permits --but this current/attached/ more manual process will at least get them started for a fresh new month of July in 'better overseeing their INC/EXP activity month-to-month'...

    RE: The Rules you mentioned previously for recurrence: I think would be pretty straight forward (based on what column data dictated)..
    Out of the list of rules provided at the UTx link, I think we could scale it back to only a handful to keep it somewhat simple..
    1. OneTime (is what we're essentially doing w/ the current list) it is driven solely by a full date to go place it on that sole date. mm/dd/yy
    2. Weekly (is definitely needed as this is common for incoming payroll checks (every Friday) and outgoing childcare expenses (every Monday))
    3. Every 2 Weeks (is very common for INC payroll)
    4. Monthly By Date (very common for EXP, rent/house pmt, car pmt)
    5. Occurs every N weeks on a set of selected weekdays : (I'm not sure if this is the right one to use for the "Occurs Every Monday of Every Week of Every Month" childcare EXP)?
    6. This one I'm not seeing in the list of rules below: "Bi-Monthly" (which is true in this case and for many - where INC only comes in twice a month and is very diff from Bi-Wkly esp in mos where 5 wks are present. Generally with Bi-Wkly payroll it's something like "15th & 30th" -- (so 2 number designations would be needed to accommodate this) -
    I'll try to update the previous sample file to accommodate only the 6 rules above --- and see if that's something that you see is achievable?
    (I'll post that update today)

    Recurrence Rules

    The occurrence of an repeating event is determined by three variables: repeating rule, occurrence count and an list of exceptions.
    Repeating rule and occurrence count are set at the time when the event is entered into a calendar.
    The exception list is constructed at a later time when occurrence at certain dates were deleted.

    • OneTime: does not repeat
    • Daily: occurs daily
    • Weekly: occurs weekly
    • Every 2 Weeks: occurs bi-weekly
    • Monthly By Date: occurs on the same day every month. If the specified date does not apply to a month (e.g. Apr. 31), no events will be scheduled for that month.
    • Monthly By Weekday: occurs on the specified weekday every month. For example, the second Thursday of the month. The week number is determined by the initial date the event was scheduled.
    • Yearly: occurs yearly on the same date, like New Year's day.
    • MTWThF: occurs on weekdays. The occurrence count is the number of weeks the event is scheduled to run, not the number of occurence of the events. (Depending on user feedbacks, the occurrence count maybe changed to the actual number of occurrence of the event in the future.)
    • MWF: occurs on Monday, Wednesday and Fridays.
    • TTh: occurs on Tuesday and Thursdays.
    • Yearly By Month Weekday: occurs yearly, on the same weekday of the month the event was scheduled. For example, the third Sunday of June.
    • Every Other Day: occurs every two days.
    • Every Third Day: occurs every three days.
    • Every Fourth Day: occurs every four days.
    • Every Fifth Day: occurs every five days.
    • Every Sixth Day: occurs every six days.

    Custom Recurring Rules

    • Occurs every N days: N is a customized variable.
    • Occurs every N months on the same day or weekday: N is a customized variable.
    • Occurs every N months on the same day/weekday counting backwards from the end of the month: N is a customized variable.
    • Occurs every N weeks on a set of selected weekdays : N and weekday-mask are customized variables.
    • Occurs every N weekdays: N is a customized variable.

    Re: VBA auto-fed Calendar Blocked View of Income, Expense and OMG Debt Status

    I'd rather just forget the recurring b/c this is taking too long to get help --
    I'd rather just go with the simple list (shown in the most recent attachment) and have the user be forced to just enter recurrences as they are needed --
    So if the vba can just look at the single table and place a SUMmed dollar amt for each day -- onto the calendar - I would assume - calling it good at this point --
    Is this something you think is doable?

    Re: VBA auto-fed Calendar Blocked View of Income, Expense and OMG Debt Status

    Lol - yes, unusual structure for "future financial health-monitoring" - but not unusual for a calendar structure =-)
    Essentially the left and right sides of every calendar day will hold the INC and EXP..
    The BOTTOM cell of every calendar day holds the running balance...

    If there's a way to program in a "pattern" combined w/ a lookup to locate the proper calendar range -- then it should be "easy" right? ::D
    First part:
    1. INDEX/SCAN through the INC column of WORKSHEET and SUM up like INCOMES (everything with a "1" + "June" + "2017" add together), etc. (indicating all of these income sources occur on the 1st of JUNE, 2017
    2. repeat the same step for EXP column of WORKSHEET (and place expenses where they belong)

    Part 2:
    1. Find JUNE 2017, THEN
    2. Find MATCHing number for the day that the INCome should hit (which would be "1" in this example) and PASTE the SUM into cell Q122
    3. do the same for EXPenses that occur on the 1st of June....................................................and PASTE the SUM into cell R122
    ...repeat until all INC and EXP items on WORKSHEET have been pasted into the CALENDAR

    Easy-Schmeazzzy right? :drunk:
    It's FRIDAY -- we need to be able to celebrate this accomplishment right??

    THAT SAID -- please see the newly attached file and the RECOMMENDATION to make this much easier! (new sheet w/ example added)

    Re: VBA auto-fed Calendar Blocked View of Income, Expense and OMG Debt Status

    Awesome start for sure!
    *I did some testing starting w/ Jan 2017 through EOY (to see if the 3 mos that are visible rolled properly)... YES

    *Next, I changed the first cal month to Dec to see if the 2nd cal rolled to Jan 2018 but 2nd & 3rd stayed at 2017 (and of course the day alignment was off until I updated the yr block to reflect 2018 on BOTH 2nd and 3rd calendars. Once yr was selected, the 2nd and 3rd calendars updated appropriately. (I imagine I'm telling you something you already know -- but in case you've not had the chance to test it -- perhaps it's value added info)

    * Last, I decided to quickly throw in the rest of a full 12 mo period -- and amazingly -- the drop downs are all tying together - forcing Jan thru Dec to appear all the way down in the MO fields - however, the adjustment of proper days stops working at Calendar 4 down through Calendar 12 (which you are probably saying - DUH to - because that hasn't been set up -- but just saying what I'm seeing - hoping it helps)

    Looks like calendar part 1 is almost a "go"! :congrats::thumbup:
    Can't wait to see summing 'humming'!
    (I've attch'd the updated file)

    Re: VBA auto-fed Calendar Blocked View of Income, Expense and OMG Debt Status

    No, I plan to pre-build the full 12 mos in advance (or if there's code that will build that out, of course this would be most desirable and save time each year...)
    If so, maybe range cells & generate button tied to code module in far top left of calendar worksheet like: "CALENDAR YR RANGE:" [ 01/01/2017 ] - [12/31/2017] ["GENERATE"] - but this may be considered more time consuming scope-creep-so I'm fine w/ just getting 1 manually built year functional so inputs will sum and feed to correct places without having to manually build in hundreds of functions to each calendar cell..

    The 3-mo was just a small example of what's needed to accomplish as far as input area vs receiving area of content

    Re: VBA auto-fed Calendar Blocked View of Income, Expense and OMG Debt Status

    Assuming we eliminate the need for the code to auto-generate the calendar (I'll just hand-build that I guess for now)...
    Assuming that CALENDAR tab is clean and ready for population as-is.... (and already has the running balance formulas in place and protected)
    There's probably a thousand approaches, but I'll take a stab and say start at the beginning of the year: (although this attchmt starts w/ Jun thru Aug)

    1. Perform some sort of lookup/find/index that will start w/ "Jan" (1st mo of a yr) = Jan
    2. Anything = to Jan (and) "1" found in the FIXED number column of the WORKSHEET table (and) = to INCOME table
    ....SUM all "1's" together and paste into CALENDAR cell that holds INCOME
    ....SUM all "2's" together and paste into CALENDAR cell that holds INCOME
    ....continue through 28th, 30th or 31st (however many total days are in that month)
    ....(probably need to have the code look to a cross-refc table to identify how many total days are in that month for that specific year)
    3. Perform exact same steps for the EXPENSE table (looking up all = to X, summing them and pasting them into Calendar cell)

    Is this along the lines you're asking for?
    Using formulas could work - but VBA probably much more reliable - just not sure how to accomplish that (esp w/ the 2nd tier variables) :yikes:
    NOTE: I've attached an updated file - cleaned out a bit for those w/ clutter-phobias :duh:

    Re: VBA auto-fed Calendar Blocked View of Income, Expense and OMG Debt Status

    Thx Carim - Worksheet 1 is totally acceptable the way you've arranged it if we can find a way to get it working/feeding the Calendar...? :eureka:

    (I imagine they'll prefer the INC & EXP separated lk WS1) more than having it all in a single table lk WS2 -- but for ease of programming/lookup/indexing etc - I figured a single table would be the easiest to accomplish (so I'm good w/ either solution).
    Do you see a way to make this layout work?

    As far as there only being 2 categories: Fixed Date or Recurring Dates
    Yes, this is true to a point... However, there's essentially sub-sets with that 2-prong tier..
    --Fixed is easy
    ex1: this INC or EXP amt always occurs on X number day ("10th"), every month

    --Recurring is where is gets a bit hairy --
    ex1: Re-Occurs every Monday (doesn't matter what the number ends up being from month to month it will always change)
    ex2: Re-Occurs every 5th day of a Month (the day will always be changing)
    ex3: Re-Occurs every 3rd month on the 10th of that quarterly month....
    Yes they're each recurring, but each uses a diff variable: specific number date (10th), specific day (Monday), specific interval (Quarterly)

    Yes, I suppose it's still boiling down to FIXED and RECURRING -- but does it make it too difficult to accommodate the variable examples shown?
    If so, I'll just tell them to enter those oddballs manually in their ongoing manual list.. (which would follow any pre-set up Fixed & Recurrings)
    It sounds like you don't foresee it being an issue - if that's the case, GREAT!! Just hoping to come up w/ a close solution.

    Thanks greatly for trying to help with figuring out a solution!

    Re: VBA auto-fed Calendar Blocked View of Income, Expense and OMG Debt Status

    If you read my notes, I already advised putting it all on one tab was a bit on the cluttered side - which is why a simple "ledger like" table on one tab and a simple calendar on the other tab was...
    (putting both on one sheet was only an OPTION for possible ease of programming)
    All the note clutter would not be there in real life. (simply notes of what is needed)
    The ledger format they've used in real life and don't like/want that.
    This is what they asked for -- a table to input such as this -- and a calendar layout such as this -- Didn't really ask for impressions or 2 cents -- just some help in accomplishing what was requested. TBNT

    By the way, the graph was already built elsewhere that shows percentage allocations -- it would be added in later (no need for further clutter right) -

    Trying to help some young friends out to better manage finances and (based on their feedback on what would be appealing to them and easier to understand) I'm trying to put together a quick entry tool that allows them to see AT-A-GLANCE CALENDAR view affects of their Income vs Outgo (some is manageable - some are 'unplanned' oh crap--this isn't good scenarios) as we've all experienced that wreaks havoc on the best of budgets..
    This quickly lets them see weeks in advance that on XXX day -- they will be dead broke -- IF they don't take action....

    Attached is a file that holds the CALENDAR view desired (where each day's block shows them INC, OUTGO and running balance)

    * The orig WORKSHEET idea was they wanted to be able to enter "LIKE" things (compartmentalized) made more sense than intimidating "ledger" style..
    * This evolved to WORKSHEET (2) which is more ledger style with all INC and EXP on a single (probably more easily 'extract-able' table) - code wise..
    * Third option was to just throw the ledger table along side the CALENDAR View (on a single worksheet) - which allowed them to see interactivity as they entered things.
    (kept them from having to jump back and forth to another sheet to see the results of their entry)

    The 3rd option is a little more cluttered and of course as months go by, they'll have to do some scrolling to get back to the CALENDAR they want to see but this 3rd option is probably the easiest to code whether using VBA or Functions or combination of both to accomplish auto-fed-summing-status..

    As long as it's functional -- the format option is OPEN for surgery --- whatever will give them the At-A-Glance CALENDAR block VIEW is the ultimate goal!
    I didn't save it as xlsm yet but adding modules and doing so is totally great!
    Thanks in advance!

    NOTE: there's some complication to it -- with "occurrence types" -- like:
    This expense occurs every Monday of every month... and ...
    This income should be applied to the calendar every Quarter (on the 5th of that mo)
    HOWEVER, if it's too complex to mess with auto-feeding those rules in --- They can just enter those manually; feel free to strip out those added columns..

    I've used TRIM, CLEAN, LEFT (etc.) functions to strip things off before but for this scenario, I think VBA would do a much better job w/ less manual effort given that each has a diff number of chars, lengths (and it's not just that single character -- there's page numbers at the end of it as well - so Find/Replace was not an option w/ the various numbers) etc..
    See attached sample file ---
    I just want to retain the LEFT side where title content exists and get rid of all the TOC dots and page #'s starting w/ the first dot.....
    Hoping someone knows a way to quickly vba-it-away!