matching dates with VBA

[awsumchillicrab]

I will be using a today() formula in a cell (say A100), and pasting values only.
I then have row A showing years, and row B showing months. A1 to L1 is 2011, B1 to L1 is Jan - Dec. M1 to X1 is 2012, M2 to X2 is Jan - Dec...etc.

I want my code to know which column I want to work on based on the today() date. And as there are so many options, I would prefer something better than IF-THEN-ELSEIF, or the IF-CASE system where I have to explicitly give 50 parameters if I have 50 columns.

Was hoping to do something like a month(A100) and year(A100), find the correct column, and then declare that as x in VBA, so that my code can use:

range(x & "3")

in order to select, say B2 for Feb 2011.

Any help?

 

[texasalynn]

in order for exel to compare today for cells with months only, that cell must be a "real" date. So if you want only the month in a cell, then format the date as "MMMM"