need help in Excel macro programming in VB!

[sujata ghosh dastider]

Hi to all!

i am working on MS-Excel 2000. i am getting problem with
macro written in VB.

i have one program which produce dates like trade date,
spot date. tom date and a series of continues year based
on spot date. if trade date is 030502 then tom date will
be 040502 and spot date should be 050502 and then series
of twelve months will be 050602, 050702...like that
upto 050503.

this spot date and tom date are decided after holiday
checking. i have made a holiday sheet in excel in same
workbook where this macro and calculation sheet are
reside. after checking holidays from holiday sheet. it
will count tom date, spot date and based on Day of spot
date the serious of 12 months get generated. if on the
date is holiday then it goes back, and take working day
before a date that is in serious of months.

now the problem is the Day of spot date is 29 or 30 or
31 then apart form goes back, it get added day and
displayed..suppose u have 280102 in spot and
correspondence to that in series of month u should have
280202 then 280302 then 280402(if these days are working
days only). but if these days are holiday or working day
it only displayed like that...280202...010402(instead of
28th march, 02)...according to that 010502

then if 28 march or april is holiday, even though its
don't go back, it just added days and show.

i need help in that area, i can't figure out what is
problem is in coding.

if any one know please help me out. any suggestion or
help get appreciated.

thanks is advance.

Sujata

<small>[ May 13, 2002, 10:51 PM: Message edited by: sujata ghosh dastider ]</small>

[Lord Skeeve]

is it at all possible to see the vb macro code?
if you dont want to post it here you can send it to the email in my profile, ill be glad to look at it.

<small>[ May 07, 2002, 09:10 PM: Message edited by: Lord Skeeve ]</small>

[sujata ghosh dastider]

thnaks lordskeeve!

yes i am sending it to you by mail. i hope these will helpu to understand. if you need more clarificaton please let me know via mail or at HFT.

sujata.

[Dag-Otto]

lol not the best idea to receive office macro code via email :)

[Lord Skeeve]

ur right dag-otto its not the best idea, but im a wiz at formatting if things go wrong, and honestly i dont store any personal info/passwords on my pc so im not all too worried about things "going wrong".. i dont recommend it for other ppl tho!!

[Dag-Otto]

:)

[Dashwood95]

Why not just post it here by using [.code][./code] buttons?

[sujata ghosh dastider]

sorry mike i can't post such long macro coding here. i feel insecure aslo. i hope that lordskeeve must keep privacy and my faith to him. :)

lordskeeve i hope u got my mail. i am waiting for reply from u about solution of that macro.

:)

[Lord Skeeve]

ive looked over it at least a dozen times sujata and im not seeing where anything could be wrong,
but ill keep pickin it apart until i do!

[sujata ghosh dastider]

ok lord!

keep looking. i hope u have understood that what i want to do and what is the problem its giving me. did u prepared any dammy sheet for that so that u can have a look of the problem area on the spot?

sujata.

<small>[ May 16, 2002, 03:16 AM: Message edited by: sujata ghosh dastider ]</small>

[Dag-Otto]

Any luck sujata ghosh dastider?

Sounds to me that this is a job for SQL. I don't think doing it this is way is terribly efficient.

Did you write the code from scratch? If you didn't try on a whim running your macro code under Excel 97.

[sujata ghosh dastider]

Dag, what r u trying to say, i can't understand at all.

for ur information this macro was made in MS excel 2000, only.

i don't think there is any need on SQL over there. because it was working perfectly. now we need some changes in it. after the the changes are made we find out its now also work correctly but when the spot date is 280502 or 300502 or 310502 then the problem is occured. because accroding to my coding it has to check for holidays and if it is then lt goes back , not next working days. here is the problem start form the month february, 03 to end of the serious. other wise it is ok. i can't understand what is the problem is, the perticular area of it.

Lord also find it, i hope this explanation will help him too, if he has any doubt, until now.

for further expalnation, please let me know.

<small>[ May 17, 2002, 03:00 AM: Message edited by: sujata ghosh dastider ]</small>

[Lord Skeeve]

ive stared at the coding until im totally blank and cant find anything wrong with it
sorry to say im gonna have to write this one off..
good luck with it

[sujata ghosh dastider]

tell me did u get that problem area?

it is giving abroupt error when spot date is 28/02/02 or 28/03/02. try to put this date and see the result.

i hope u can find out the unwanted date-serious in Excle sheet. even though it 28 is holiday or not. its simply add dates with it and displayed, but is suppose to goes back is 28 is holiday, according to programe coding!

if its not clear what i am trying to say, then let me know.

sujata.

[Megan]

OK, I'll pop in a suggestion here - thinking aloud for a moment... (and possibly completely off the wall, in which case you can ignore me)

Excel stores dates as numerics, then formats for display. So, to get around the holiday, end of month issues, etc, is the macro simply adding a day to the date value and rechecking until it finds a valid date? Without seeing the code, I'm wondering if you're working with date as "date" (ie formatted) or the numeric? Trying to work with the formatted date would seem to make the whole thing way more difficult, as you remove the automatic progression Excel handles on its own.

Adding a day to the numeric, the macro no longer needs to determine if the day is after a day of the month - adding 5 days to Feb 24 will automatically produce Feb 29 in a leap year, and Mar 1 if not. Then, the macro only needs to check the table to determine if the date is valid (not a holiday or other excluded date). If false, add another day and go again...

Done thinking aloud - shutting up now - hope it helps...

[sujata ghosh dastider]

hello megan!

sorry for the let respons.

i have that kind of programming which search working days from holiday sheet and display it. it is working perfectly. some programs are searching next working days. its going allright. but when it come to find out previous working day of given date, then the ultimate problem is start.but not all time when trade date become 29/30/31. then for some month it goes next, instead of going back. if can get more explantion about that from my first post.

i allready gave that coding to Lord Skeeve. he was trying to find out the error but still he is finding or not i don't know. :(

if you want sample coding i can send it to you.

[Megan]

NO THANK YOU! I long ago decided I would NOT program macros for public consumption, so I'm not up to reading it. I went the dBase III route instead :) Suggestions are from my own experience only - seemed that would alleviate one issue, but you've already dealt with that. However...

If you can add and find the next date, should the same code not be able to continue subtracting one day until it finds a valid date? Is it actually running a loop of
</font><blockquote><font size="1" face="Verdana,Arial,Helvetica">code:</font><hr /><pre style="font-size:x-small; font-family: monospace;"> add a day
check if valid
repeat if not</pre><hr /></blockquote><font size="2" face="Verdana,Arial,Helvetica">if not, that may solve the problem - uuuggghhh loops in excel - now you know why I went with dBase <img border="0" title="" alt="[Eek!]" src="eek.gif" /> <img border="0" title="" alt="[Eek!]" src="eek.gif" /> <img border="0" title="" alt="[Eek!]" src="eek.gif" /> Good luck!

[sujata ghosh dastider]

hello megan!

ok no problem! i think u have suffered a lot with excel loop. but i can't help it. i have work with excel macro with LOOP. :)

yes i have done same loop code. i told you that everything is working perfectly where it come to forword ing (adding) date to moving back. problem only arries when day of month become 29 or 30 or 31.

i am finding solution of it. but when it will solve God knows.

Thanks an Good luck with your dBase. <img border="0" title="" alt="[Wink]" src="wink.gif" /> :)

[sameer27p]

Hi,

I have an excel workbook with two sheets.Both sheets have 'item number' as a column.Both sheets contain different number of rows.I want to write a macro for displaying the common items in both sheets on a page (comparing both the sheets and getting the common item numbers)

I have to write a macro for generating the waterfall chart (Refer attached file Waterfall.doc).The 'Actual' values come from one sheet and the 'Forecast' value from the other of the same worksheet.(Item 1,Item 2......) are the common items in both the sheets.Variance =Actual-Forecast.........

It would be kind of you if someone could help me generate this waterfall model using Macro programming in Excel (VB Editor)...(puru_66@yahoo.com)