Sorry for posting guys but I'm really stuck, I have this code that I borrowed and I had it working at some point, however I've not looked at it for a while and made some changes to the original spreadsheet it referenced, I've updated it to take into account those changes but now it comes up with an run type error '13' - Type Mismatch. When I click debug it highlights
This is the code in full:HTML Code:
Any help much appreciated!!!HTML Code:
Last edited by Charlottewildsmith; 09-30-2010 at 02:47 PM.
Hi,
Do you still have dates in column J of your worksheet that are stored as text values?
Dom
"May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."
Use code tags when posting your VBA code: [code] Your code here [/code]
Remember, saying thanks only takes a second or two. Click the little star to give some Rep if you think an answer deserves it.
First off Thanks for the Title change, explains it better!!
Secondly I have dates in column J, formatted to show as dates, however just changed to show as text for a test run and still no joy.
Oh and it does add the appointments to Outlook anyway. So it appears to run but with this error??
I'm not sure what format Outlook expects the dates to be in when you create the appointment but DateValue is usually used for converting dates stored as strings to serial date values. If your dates are already serial dates in your worksheet you probably don't need to use it and may get errors.
Dom
If your cells actually contain dates, then you shouldn't need the DateValue statement at all.
Remember what the dormouse said
Feed your head
Yeah I'm not sure what outlook uses either, will do some more research on that, I've tried taking DateValue out before and whilst I had no errors it took the date of 30/10/2010 and put in in the calendar as Sat 30/12/1899, slight difference there!
What's annoying is I'm sure I've had it running before, will do some more digging and once I have it sussed will post the answer (fingers crossed!)
I think .start must be a timeValue.
e.g. if J3 is "12-03-2010 12:15:00"
Please Login or Register to view this content.
Last edited by snb; 09-30-2010 at 12:43 PM.
Ok I have checked it again without DateValue, now I am confused, it appears that the dates are getting entered as I wanted, however each time I run it 5 extra appointments are added with the date of 30/12/1899 and not sourcing any other information?? Ideas?? This is what I have entered in J, first line is blank followed by:
DATE OF DEPARTURE
30/10/2010
31/10/2010
01/11/2010
02/11/2010
03/11/2010
04/11/2010
05/11/2010
06/11/2010
07/11/2010
08/11/2010
09/11/2010
10/11/2010
11/11/2010
12/11/2010
I love code when it works!!
I suspect you are including 5 rows with blank cells in the start column.
ahhhhh, now I thought like you did blank data at the start, so checked for hidden rows etc etc
But I have the cells formatted so they are all pretty with 5 blank rows after my last row of data!! Seeing you write that made me recheck and "oh look 5 blank rows" just at the bottom and not the top. Hence the random appointments being added. Just need some code to stop it when it hits an empty cell.
I can't thank you guys enough for helping me through this, I'm happy I was on the right track just with your input it made me take one step at a time.
Instead of using UsedRange to work out the last row try:
Please Login or Register to view this content.
Dom
THANK YOU, THANK YOU, THANK YOU, THANK YOU, THANK YOU, THANK YOU, THANK YOU, THANK YOU!!!!!!!!
I was just trying to work out what to do and if it was worth changing another code I have to format the cells as it added (not sure I'm up to that job at all yet!!) but this worked perfectly.
Finished code is:
HTML Code:
Glad Dom got you sorted.
Now for the boilerplate bit:
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save
Yes Sir I have done it now hehehe
Cheers guys
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks