Locating Column Position Based on Date

[yunesm]

Hello,
I have an issue with a piece of code I'm trying to put together.

I have a End User defined date with have have set as a variable ChangeDt.

I need to find the column position on a worksheet (sheet2) based on this date.

The reason this isn't straight forward for me is that it will be rare that I will have the same date on sheet2 as was entered.

The dates on sheet2 represent payroll periods and an employee may activate, cancel or put their benefits on hold.

If an employee called and changed their benefit on 7/3/11 this will fall between the payroll dates of 6/30/11 (column S) and 7/7/11 (column T). Since we won't change the status of the current pay period the column that should be located is Column T for the next pay period. If the change occurs on the start of the pay period (6/30/11) then column S should be found.

I want to set this column to a variable like Col.

Does this make sense?

Any thoughts or suggestions are welcome!

I've attached my workbook for reference.

[Leith Ross]

Hello yunesm,

This macro will return either the column number the date is in or a zero if the date was not found.

Please Login or Register  to view this content.

[yunesm]

Thanks Leith! Thanks for the help.

I'm doing something wrong. I put the function that you provided in a general module and then added the following code within my sheet module (Sheet1).

Please Login or Register  to view this content.

The code doesn't seem to be calling the macro appropriately becuase I'm not getting anything in the message box (I'm also not getting any errors).

I also tried to change the fuction to a sub and inserted it into the code within my sheet module.

Thoughts on what I'm going wrong?

[Leith Ross]

Hello yunesm,

If you have placed the macro in standard VBA module like "Module1" etc. then that is where the code should be. Once the code is copied to a standard module, it is visible to all other parts of the VBA Project. So, you don't need to use Application.Run to invoke the macro. The macro takes one argument: the date you want to find the column for.

Change your code to what is below and it should work fine.

Please Login or Register  to view this content.

[yunesm]

Hey Leith,
I've attached the workbook so you can see what I've done.

I do have the function in "Module1" but I'm not getting the correct result in the message box.

At the end of this code I'm using the message box to check the column position before I write the code that I'm actually going to use it in. I'm hoping to get the column position or the 0 in the message box; however, when I run this code all I get is the date for MyDate.

Please Login or Register  to view this content.

Sorry for what is likely a silly question. I appreciate all of your help!

Thanks!

[Leith Ross]

Hello yunesm,

I am not sure what you did. Copy the code below and paste it into Module1. Run the macro TestA. This works fine on my computer.

Please Login or Register  to view this content.

[yunesm]

Looks like all I needed was some sleep... I have it working now.

Thanks again for all of your help...!