Thanks for the feedback, and I'm glad you're on your way to a working solution.

"Juan Correa" wrote:

Yep...
Just tested your suggestion and it's much simpler than the function I had
found.
One thing I noticed is that you need to specify (RowAbsolute:=False) with
.Address or the macro will populate the range with formulas referencing $I$2
in every row.

Thank you agan.

"JLatham" wrote:

If you want the address of Cells(2, MyColumn) rather than its contents, use
& Cells(2, MyColumn).Address &

"Juan Correa" wrote:

I just tried this modification:
"=IF(MONTH(" & .Cells(2, myColumn) & ")=12,CurrentPeriod(" & .Cells(2,
myColumn) _
& "),IF(" & .Cells(2, myColumn) & "<=LastFriday(" &
.Cells(2, myColumn) _
& "),CurrentPeriod(" & .Cells(2, myColumn) & "),NextPeriod("
& .Cells(2, myColumn) & ")))"

But it produced this when I ran the macro
=IF(MONTH(4/15/2010)=12,CurrentPeriod(4/15/2010),IF(4/15/2010<=LastFriday(4/15/2010),CurrentPeriod(4/15/2010),NextPeriod(4/15/2010)))

So for some reason that escapes me it wrote 4/15/2010 where I was expecting
to see H2.

Any idea what I'm doing wrong here?

thanks
Juan Correa


"Juan Correa" wrote:

Hello,

I have this bit of code that is part of a larger macro:

' Populate the Month Column with new Monts
.Range(.Cells(2, LastCol + 1), .Cells(LastRow, LastCol +
1)).Formula = _

"=IF(MONTH(H2)=12,CurrentPeriod(H2),IF(H2<=LastFri day(H2),CurrentPeriod(H2),NextPeriod(H2)))"
.Range(.Cells(2, LastCol + 1), .Cells(LastRow, LastCol +
1)).NumberFormat = "MM-YYYY"

What this does is populate the column to the right of the last used column
in my data spreadsheet with a formula that does some checks on the dates
stored on column H and outputs another date based on them.
As you can see right now I have it hard-coded to column H.
The issue that I have is that the report may vary in the number of columns
included and the date to be evaluated may not always be on column H. The one
constant that I have is the column label.
So I though about doing something like:
myColumn = Cells.Find(What:="Column Label").Column
and then changing the formula to the cells(R, C) format...
Here is where I'm running into problems with the syntax.
Can someone show me what the correct syntax would be?

thanks
Juan Correa