Quicker way to enter in multiple vba if statements

Originally Posted by mudraker

jubangy

Please take a couple of minutes and read the Forum Rules then edit your thread title by following the instructions in the rules (Rule 1)

Sorry about that, I hope the title is now more appropriate as I really was not/am not to sure how to accurately describe what it is I am asking without a long description.

?
Could you elaborate a little more?
I need it so if something is entered into one of the days, that will be the info used, but when the next month comes, when i redo the schedule i need the formula back without having to retype it.

With the 2 sheets, the request off and the final this isnt a problem but I am trying to eliminate the request off sheet, so when I do the schedule for the following month, the formulas return when i clear the cells.
for example say in c5 i put in vac, next month when i do the schedule in c5 i need to be able to clear cell c5 and have it use the data from t5.
Liek i said with the 2 sheets this isnt a problem, but i would liek to get it down to just one.
Thanks.

Originally Posted by davesexcel

Very strange, I thought I attached a code to this thread..
anyway...

Try this

Please Login or Register  to view this content.

Thankyou Davesexcel, but that is not doing anything.
Maybe this will help describe what I am trying to do alittle better,

Please Login or Register  to view this content.

This so far works, except I need it to go down to row35.
Thanks again.

Originally Posted by davesexcel

What do you mean it doesn't do anything??

Try placing the code in the worksheet change then

BTW
Your new example is nothing like your original example,

In the new example I just changed the cell ranges to the ones I am using.
Also, I did try your formula in worksheet change, and it keeps saying expecting end sub.

Thanks again for your help.

Thankyou a ton jindon, that seems to have done the trick, I am not even going to try and pretend like I understand the code, but if you wouldn't mind maybe you could explain it so I can try and get a grasp of it.
Usually I can work my way through them and figure it out but this one makes no sense to me, however like I said it seems to be workign well. I appreciate it muchly, and thank you davesexcel for taking the time to try and help me.

Originally Posted by jubangy

Thankyou a ton jindon, that seems to have done the trick, I am not even going to try and pretend like I understand the code, but if you wouldn't mind maybe you could explain it so I can try and get a grasp of it.
Usually I can work my way through them and figure it out but this one makes no sense to me, however like I said it seems to be workign well. I appreciate it muchly, and thank you davesexcel for taking the time to try and help me.

Have you tried both ?
Did both work as you expected?

I couldn't get the code that davesexcel posted to work, but I still very much appreciate his efforts and helping me out.
The code you just posted, I modified the with range from c5:f6 to c5:f35
and that seems to have solved the issue. Now I don't have to clutter the whole book up entering in multiple if statements for all the cells. Thanks again.

So now looks like

Please Login or Register  to view this content.

?
It doesn't require LOOP, since you required "Quicker" way.
It generates 2D array from the result of calculation of the formula within a memory.
Then replace C5:F35 with the array.
Formula is
If(C5:F35<=0,T5:W35,C5:F35)

Thank you, it kind of makes sense.

One thing though,
The way I originally had the sheet set up,
You changed the date in b5, to the first day of the month, ex..1/1/08
and the rest of the dates filled in automatically, and the cells from c5:f35 would change to either NDO or 11-7 according to the day of the week in column a, unless something else was entered in any of the columns from c5:f35.
What this code is for is so I did not need the second schedule to change c5:f35 from what was auto entered. Now that works, except when I change the date none of the cells automatically update unless I clear them first. Is there someway to make this code work without me clearign the cells every month?

Originally Posted by jubangy

Thank you, it kind of makes sense.

One thing though,
The way I originally had the sheet set up,
You changed the date in b5, to the first day of the month, ex..1/1/08
and the rest of the dates filled in automatically, and the cells from c5:f35 would change to either NDO or 11-7 according to the day of the week in column a, unless something else was entered in any of the columns from c5:f35.
What this code is for is so I did not need the second schedule to change c5:f35 from what was auto entered. Now that works, except when I change the date none of the cells automatically update unless I clear them first. Is there someway to make this code work without me clearign the cells every month?

No, I didn't change b5.

What are you trying to do ?

No no I know you didn't change b5, I did it is where the first date of the month is entered and all the rest of the data in c5:f35 feeds off of.
When a date is changed in b5, ex 1/1/08, then the rest of the dates for the month are filled down in column b from b6 to b35. In column a, starting form a5, going down to a35 the actual days in text format are filled in. Then from that, columns t:w figure out what to enter into columns c through f (thanks to your handy code )
Except now, unless I manually clear the schedule from from the data that is entered in from the code, the code won't update when I change the month.
I could actually use a macro to accomplish this I just was wondering if there was a somewhat simple peice of code to add or modify to avoid using macros.
Thnaks..

OK
Can you just attach your file with an example with clear explanation without ZIPPED/Macro, otherwise I can not look at your sheet due to the security here.

Heres a trimmed down version so don't mind the div errors I had to delete a couple sheets to get it into the size requirements to attach, but the parts I am asking about are still in tact. Although, I changed the code from selection change to worksheet activate trying to see if maybe i could make it do what I am asking but so far no luck. Anyways here is the workbook.

Copy2 of Blank Schedule.xls

Sorry, but as I mentioned, I can not run a macro nor even get into VBE due to the security here.

All I can is just "LOOK" at your worksheet and formulas.

If you could attach the sheet before/after will help me to understand your porblem.

OK

Is that

When you change B5, you want to clear C5:F35 ?, or not all of them ?

Exactly.

The only time I change b5 is when I am preparign the schedule for the next month, so c5:f35 would need to revert back to using the code.

How it works is, i have the schedule set so when I change the date in b5, a5:a35 display the day of the week starting with b5. Then from that the schedule determines if it is each persons normal day off (ndo) or if not it shows 11-7 which is our shift, unless like i said i manually change it if someone is taking a paid day off.

Right now the code works, until i change to the next month, then c5:c35 still show the previous month until I clear them out manually.

Thanks

I am trying to avoid using a macro because of the security here at work. It will let normal code run but for some reason it has a problem with macros.

I can get them to run but if someone else uses it they get freaked out when the warning message pops up and act like some terrorist is invading the pc.

That code produces the error Run-time error '424'
Object Required.

I have no clue.

Perhaps

Please Login or Register  to view this content.

After this, save and close the book and re-open it.

Originally Posted by jindon

Perhaps

Please Login or Register  to view this content.

After this, save and close the book and re-open it.

That still caused the error.
I have this now and is working well except that everytime you select a different cell the whole sheet kind of flickers.

Please Login or Register  to view this content.

Originally Posted by davesexcel

Do you not require a target range for your select event?
or the code will kick in every time you make a selection

Could you maybe explain what you mena?
I tried the application.enableevents
but it didn't make a difference, as soon as I put in the selection change code
The screen starts flickering.

Thankyou davesexcel for that example,
this is what I put together using your example, and now it all works like I need.

Please Login or Register  to view this content.

Originally Posted by davesexcel

If you only need to target one cell then this would be enough

Please Login or Register  to view this content.

Thanks again that worked also.

And thanks to jindon for the time spent helping me as well I appreciate it.

jubangy
Just change to this

Please Login or Register  to view this content.

Originally Posted by jindon

jubangy
Just change to this

Please Login or Register  to view this content.

Thanks Jindon, that code is shorter and to the point, and works well.