Macro to update the date column for certain rows in a table depending on a column value

**ajolin** · 10-12-2012, 08:45 AM

Hi, I have an existing excel table that requires the following added functionality.

If the value in column called movement is equal to SCHOOL AM or SCHOOL PM then update the Date column for that row by 1 day. I'm just unsure how to handle if it is Friday to add 3 to the current date instead of just 1 to the current date. Also, would the function be able to handle the end of the month and roll the date over appropriately. I figure I would run the macro at a certain time during the current day to roll over the date for the found rows (which would run around 80 - 120 rows daily). If someone could provide me the formula or function to use to roll the date over properly that would be great. I have some coding that uses IF statements to find the correct row in the table but I wonder if CASE statements may work better in this situtation. I also jsut thought about the issue of the Macro set to run every day that may screw up the rolling of the dates during the weekend. Is their a time function that I can use to ensure that the marco only runs from Monday to Friday?

Thanks,

A

**HaHoBe** · 10-13-2012, 04:22 AM

Hi, ajolin,

maybe you should attach a sample workbook with a small amount of data and a descption of the situation as is and the result/output wanted.

Ciao,
Holger

**ajolin** · 10-13-2012, 06:29 PM

Hi Holger the information in confidential but I can provide a quick layout for explaination

Worksheet name dailymovement
excel table name is movement

Columns A b c d
Person movement type time and location date of movement

rows Smith Doctor am

**ajolin** · 10-13-2012, 06:36 PM

Hi , Holger the information is confidential. here is the layout

worksheet tab is daily movement
existing excel table is called movement

columns person movement type location or time date of movement
rows SMITH BARBER EVE 2012-10-16
JONES SCHOOL AM 2012-10-16
BAKER SCHOOL PM 2012-10-16
WALKER DOCTOR AM 2012-10-16
MARTINS SCHOOL PM 2012-10-16
GARBE DOCTOR AM 2012-10-16

So in the above data I would create a marco that runs around 3pm daily that will filter on movement type to equal SCHOOL AM OR SCHOOL PM. Then maybe use the =workday function to roll the date over to the next workday and ensure it takes care of month end and skip saturday and sunday as dates to roll to. I SCHOOL PM AND SCHOOL AM movement happens every workday but the persons that attend may vary overtime as they may stop going to school finish school or be suspended from school.

I just don't want the teacher to have to modify the date at the end of each day for each row that equals school pm or school am. I just want a marco that will take care of this modification on a daily workday basis. As the number of rows that contain SCHOOL AM OR SCHOOL pm can contain 120 entries each workday.

Thanks,

A

**HaHoBe** · 10-14-2012, 10:18 AM

Hi, ajolin,

have a look at the workbook attached and see if I understood what you are after.

Code used:

Please Login or Register  to view this content.

This is just basic for the weekends - no free days are included yet.

Ciao,
Hoilger

**ajolin** · 10-18-2012, 12:13 AM

Hi, this looks very close to what I'm looking for. I will test it out in a test copy of the excel file. Would the coding be any different if I refer to the excel table name instead of the worksheet name?

I will update this thread, once I test tomorrow.

Thanks,

Anne

**ajolin** · 10-18-2012, 12:21 AM

Hi Hoilger, it works exactly the way I needed it to work. Thank you so much.

Cheers,

Anne

Macro to update the date column for certain rows in a table depending on a column value

LinkBack

Thread Tools

Rate This Thread

Display

Macro to update the date column for certain rows in a table depending on a column value

Re: Macro to update the date column for certain rows in a table depending on a column valu

Re: Macro to update the date column for certain rows in a table depending on a column valu

Re: Macro to update the date column for certain rows in a table depending on a column valu

Re: Macro to update the date column for certain rows in a table depending on a column valu

Re: Macro to update the date column for certain rows in a table depending on a column valu

Re: Macro to update the date column for certain rows in a table depending on a column valu

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions