Help with modifying a formula.

[Juan Correa]

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<=LastFriday(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

 

[Juan Correa]

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

 

[dan dungan]

Hi Juan,

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)))

I'm wondering what you expected to see?

Dan

 

[JLatham]

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

 

[Juan Correa]

Thank you very much Sr.
I had figured it out in a different way ... Probably more complicated than
yours.

Here's how I did it in case someone wants to know:
I found a code snippet that will convert the column number to its
corresponding letter
Function ColumnLetter(ColumnNumber As Integer) As String
If ColumnNumber > 26 Then

' 1st character: Subtract 1 to map the characters to 0-25,
' but you don't have to remap back to 1-26
' after the 'Int' operation since columns
' 1-26 have no prefix letter

' 2nd character: Subtract 1 to map the characters to 0-25,
' but then must remap back to 1-26 after
' the 'Mod' operation by adding 1 back in
' (included in the '65')

ColumnLetter = Chr(Int((ColumnNumber - 1) / 26) + 64) & _
Chr(((ColumnNumber - 1) Mod 26) + 65)
Else
' Columns A-Z
ColumnLetter = Chr(ColumnNumber + 64)
End If
End Function


I included that function in my macro and then used it in my formula like this:

' Populate the Month Column with new Monts
Dim myColumn As Integer
myColumn = Cells.Find(What:="Expected Book Date").Column
.Range(.Cells(2, LastCol + 1), .Cells(LastRow, LastCol +
1)).Formula = _
"=IF(MONTH(" & ColumnLetter(myColumn) & "2" _
& ")=12,CurrentPeriod(" & ColumnLetter(myColumn) & "2" _
& "),IF(" & ColumnLetter(myColumn) & "2" _
& "<=LastFriday(" & ColumnLetter(myColumn) & "2" _
& "),CurrentPeriod(" & ColumnLetter(myColumn) & "2" _
& "),NextPeriod(" & ColumnLetter(myColumn) & "2" & ")))"
.Range(.Cells(2, LastCol + 1), .Cells(LastRow, LastCol +
1)).NumberFormat = "MM-YYYY"

That worked perfectly... I would imagine that doing away with the function
and just using the .Address property would simplify things quite a bit.


Thanks again.
JC

 

[Juan Correa]

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.

 

[Juan Correa]

Dan,
I wanted the cell's address rather than its content.

My question has been answered by JLatham already.

Thanks anyway
JC

 

[JLatham]

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