Topic Wiki

For cool Excel spreadsheet template to keep track of all Credit Card stuff, see here
« Last edited by shoobi on November 12, 2015, 11:24:07 PM »

Author Topic: Excel Help and Problems  (Read 101925 times)

Offline cgr

  • Dansdeals Gold Elite
  • ***
  • Join Date: Aug 2017
  • Posts: 180
  • Total likes: 10
  • DansDeals.com Hat Tips 1
    • View Profile
Re: Excel Help and Problems
« Reply #1350 on: January 12, 2020, 12:34:30 PM »
Would it help if you slow down the code? I've seen something similar from John Walkenbach where his code was running faster than the shell object was able to process, so he had a do until loop check every second if it was ready to proceed.

Also just curious why you first used the sheets collection and then the worksheets collection

Thanks but I tried slowing down the code. Didn't help with the memory issue.

I pasted a few parts of code from different VBA's I'd written before that had the same function, in one I used Sheets, and in one Worksheets so this is a collection of both...
Check out photos of my trips here

Online stooges44

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jan 2017
  • Posts: 5187
  • Total likes: 764
  • DansDeals.com Hat Tips 258
    • View Profile
Re: Excel Help and Problems
« Reply #1351 on: February 07, 2020, 10:06:32 AM »
Is there any way to make a "calendar view" by giving it specific dates?

Meaning, I have events on 2/15, 2/17, 3/1, 3/2, 3/9, 4/6...etc... is there a way to have excel (or anything) create a monthly calendar format with my data showing on the appropriate dates? So in this case, I will still see the whole month of Feb but the 15th and 17th will show my events.
If it's not free shipping it's not worth it.

Offline skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 2836
  • Total likes: 169
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Help and Problems
« Reply #1352 on: February 07, 2020, 10:19:20 AM »
Is there any way to make a "calendar view" by giving it specific dates?

Meaning, I have events on 2/15, 2/17, 3/1, 3/2, 3/9, 4/6...etc... is there a way to have excel (or anything) create a monthly calendar format with my data showing on the appropriate dates? So in this case, I will still see the whole month of Feb but the 15th and 17th will show my events.
Easiest thing to do is probably to import the list from Excel into Outlook (something like this - https://www.techwalla.com/articles/create-calendar-excel-data), but I assume you're looking to continually change the list and have it update the calendar, which won't work with this method. I'm sure there are ways to do it with pivot tables as well, but that's probably more hassle than it's worth. I would suggest looking through the calendar templates and see if any work for what you're trying to do. There's one called Student assignment planner that should work for you (ie replace assignment with event).

Online stooges44

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jan 2017
  • Posts: 5187
  • Total likes: 764
  • DansDeals.com Hat Tips 258
    • View Profile
Re: Excel Help and Problems
« Reply #1353 on: February 07, 2020, 10:28:39 AM »
I would suggest looking through the calendar templates and see if any work for what you're trying to do. There's one called Student assignment planner that should work for you (ie replace assignment with event).

Thanks! How do you replace the "Assignment due!" with the event?
If it's not free shipping it's not worth it.

Offline skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 2836
  • Total likes: 169
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Help and Problems
« Reply #1354 on: February 07, 2020, 10:33:51 AM »
Thanks! How do you replace the "Assignment due!" with the event?
The first tab of the template is Assignments. You can change the tab name, and the header (row 2), to Event(s) or whatever you want. Then in each row (from row 4 and on), put the event title/description in column B and the date of the event in column C (clear any rows you don't need). Then when you go to the Month or Week View tabs you'll see the events you put in populated. You can switch the month or year you want to view in row 3 of the those tabs.

ETA: In case it wasn't clear, you don't enter the events on the month tab - it's filled with formulas to read off of the Assignments tab, which is where you enter them.

Online stooges44

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jan 2017
  • Posts: 5187
  • Total likes: 764
  • DansDeals.com Hat Tips 258
    • View Profile
Re: Excel Help and Problems
« Reply #1355 on: February 07, 2020, 10:40:44 AM »
The first tab of the template is Assignments. You can change the tab name, and the header (row 2), to Event(s) or whatever you want. Then in each row (from row 4 and on), put the event title/description in column B and the date of the event in column C (clear any rows you don't need). Then when you go to the Month or Week View tabs you'll see the events you put in populated. You can switch the month or year you want to view in row 3 of the those tabs.

ETA: In case it wasn't clear, you don't enter the events on the month tab - it's filled with formulas to read off of the Assignments tab, which is where you enter them.

Yes I did all that but the formula is this:  =IF(LEN(G8)=0,"",IF(COUNTIF(Assignments[DUE DATE],DATE(MoYear,@MoMonthNum,G8))>0,"Assignment due!",""))  so when it's true it's just showing Assignment due! , how do I get it to show the value of the corresponding date from the fist tab?
If it's not free shipping it's not worth it.

Offline skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 2836
  • Total likes: 169
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Help and Problems
« Reply #1356 on: February 07, 2020, 11:00:59 AM »
Yes I did all that but the formula is this:  =IF(LEN(G8)=0,"",IF(COUNTIF(Assignments[DUE DATE],DATE(MoYear,@MoMonthNum,G8))>0,"Assignment due!",""))  so when it's true it's just showing Assignment due! , how do I get it to show the value of the corresponding date from the fist tab?
Lol, sorry - hadn't tested. In B7, replace the formula with:
=IF(LEN(B6)=0,"",IF(COUNTIF(Assignments[DUE DATE],DATE(MoYear,MoMonthNum,B6))>0,INDEX(Assignments[DESCRIPTION],MATCH(DATE(MoYear,MoMonthNum,B6),Assignments[DUE DATE],0)),""))

Then copy B7 and paste formula to B7:H7, and again for rows 9, 11, 13, 15, and 17.

Online stooges44

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jan 2017
  • Posts: 5187
  • Total likes: 764
  • DansDeals.com Hat Tips 258
    • View Profile
Re: Excel Help and Problems
« Reply #1357 on: February 07, 2020, 11:06:13 AM »
Lol, sorry - hadn't tested. In B7, replace the formula with:
=IF(LEN(B6)=0,"",IF(COUNTIF(Assignments[DUE DATE],DATE(MoYear,MoMonthNum,B6))>0,INDEX(Assignments[DESCRIPTION],MATCH(DATE(MoYear,MoMonthNum,B6),Assignments[DUE DATE],0)),""))

Then copy B7 and paste formula to B7:H7, and again for rows 9, 11, 13, 15, and 17.

Sick!! That's awesome, thanks so so much!!
If it's not free shipping it's not worth it.