Hello
 

Hi folks,
Im trying to create a heavily modified accounts receivable aging report. I
think an example will help explain my intent.

CUSTOMER DATE €œDESCRIPTION" 0-30 31-60 61-90 90+

Microsoft 10/15/07 Computer Monitor X
Boeing 9/1/07 Radio Equip. X

What I would like this to do is, have the €œX€� automatically move laterally
from column to column as time passes from the date entered in column B.

Thank you very much for your help. Kind regards, John

Hello
 

Opps, I meant I need the X to move horizontally.

"hjburk" wrote:

Hi folks,
Im trying to create a heavily modified accounts receivable aging report. I
think an example will help explain my intent.

CUSTOMER DATE €œDESCRIPTION" 0-30 31-60 61-90 90+

Microsoft 10/15/07 Computer Monitor X
Boeing 9/1/07 Radio Equip. X

What I would like this to do is, have the €œX€� automatically move laterally
from column to column as time passes from the date entered in column B.

Thank you very much for your help. Kind regards, John

Hello
 

On Oct 15, 10:01 pm, hjburk wrote:
Hi folks,
I'm trying to create a heavily modified accounts receivable aging report. I
think an example will help explain my intent.

CUSTOMER DATE "DESCRIPTION" 0-30 31-60 61-90 90+

Microsoft 10/15/07 Computer Monitor X
Boeing 9/1/07 Radio Equip. X

What I would like this to do is, have the "X" automatically move laterally
from column to column as time passes from the date entered in column B.

Thank you very much for your help. Kind regards, John

Assuming that your worksheet is formatted like your example, place
something like the code below in the ThisWorkbook module of the
applicable workbook. This will cycle through the sheet and make the
necessary adjustment each time the workbook is opened. Is this what
you are after?

Private Sub Workbook_Open()
Dim dateCol As String
dateCol = "B"
For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
Range(Cells(i, 4), Cells(i, 7)).ClearContents
With Cells(i, dateCol)
If .Value <= Date And _
.Value = Date - 30 Then
Cells(i, 4).Value = "X"
ElseIf .Value <= Date - 31 And _
.Value = Date - 60 Then
Cells(i, 5).Value = "X"
ElseIf .Value <= Date - 61 And _
.Value = Date - 90 Then
Cells(i, 6).Value = "X"
Else
Cells(i, 7).Value = "X"
End If
End With
Next i
End Sub

Hello
 

On Oct 15, 10:07 pm, hjburk wrote:
Opps, I meant I need the X to move horizontally.

"hjburk" wrote:
Hi folks,
I'm trying to create a heavily modified accounts receivable aging report. I
think an example will help explain my intent.

CUSTOMER DATE "DESCRIPTION" 0-30 31-60 61-90 90+

Microsoft 10/15/07 Computer Monitor X
Boeing 9/1/07 Radio Equip. X

What I would like this to do is, have the "X" automatically move laterally
from column to column as time passes from the date entered in column B.

Thank you very much for your help. Kind regards, John

After re-reading your post, I realized that you probably want this to
be via formulas, not VBA. If so, you can use formulas like the ones
below. Assuming that your dates are in column B and your
0-30,31-60,61-90, and 90+ column are D, E, F, and G respectively.

Formula in D2:
=IF((TODAY()-B2)<=30,"X","")

Formula in E2:
=IF(AND((TODAY()-B2)<=60,(TODAY()-B2)=31),"X","")

Formula in F2:
=IF(AND((TODAY()-B2)<=90,(TODAY()-B2)=61),"X","")

Formula in G2:
=IF((TODAY()-B2)90,"X","")

Hello
 

Wow! That's much more complicated than I expected. Thank you very much for
your help. It will take me awhile to play w/ this and figure it out. I'll let
you know how it goes. Thanks again!
Kind regards,
John

"JW" wrote:

On Oct 15, 10:01 pm, hjburk wrote:
Hi folks,
I'm trying to create a heavily modified accounts receivable aging report. I
think an example will help explain my intent.

CUSTOMER DATE "DESCRIPTION" 0-30 31-60 61-90 90+

Microsoft 10/15/07 Computer Monitor X
Boeing 9/1/07 Radio Equip. X

What I would like this to do is, have the "X" automatically move laterally
from column to column as time passes from the date entered in column B.

Thank you very much for your help. Kind regards, John

Assuming that your worksheet is formatted like your example, place
something like the code below in the ThisWorkbook module of the
applicable workbook. This will cycle through the sheet and make the
necessary adjustment each time the workbook is opened. Is this what
you are after?

Private Sub Workbook_Open()
Dim dateCol As String
dateCol = "B"
For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
Range(Cells(i, 4), Cells(i, 7)).ClearContents
With Cells(i, dateCol)
If .Value <= Date And _
.Value = Date - 30 Then
Cells(i, 4).Value = "X"
ElseIf .Value <= Date - 31 And _
.Value = Date - 60 Then
Cells(i, 5).Value = "X"
ElseIf .Value <= Date - 61 And _
.Value = Date - 90 Then
Cells(i, 6).Value = "X"
Else
Cells(i, 7).Value = "X"
End If
End With
Next i
End Sub

Hello
 

Thank you very much. Your second email did the trick!! I really appreciate
your help!
Kind regards,
John

"JW" wrote:

On Oct 15, 10:07 pm, hjburk wrote:
Opps, I meant I need the X to move horizontally.

"hjburk" wrote:
Hi folks,
I'm trying to create a heavily modified accounts receivable aging report. I
think an example will help explain my intent.

CUSTOMER DATE "DESCRIPTION" 0-30 31-60 61-90 90+

Microsoft 10/15/07 Computer Monitor X
Boeing 9/1/07 Radio Equip. X

What I would like this to do is, have the "X" automatically move laterally
from column to column as time passes from the date entered in column B.

Thank you very much for your help. Kind regards, John

After re-reading your post, I realized that you probably want this to
be via formulas, not VBA. If so, you can use formulas like the ones
below. Assuming that your dates are in column B and your
0-30,31-60,61-90, and 90+ column are D, E, F, and G respectively.

Formula in D2:
=IF((TODAY()-B2)<=30,"X","")

Formula in E2:
=IF(AND((TODAY()-B2)<=60,(TODAY()-B2)=31),"X","")

Formula in F2:
=IF(AND((TODAY()-B2)<=90,(TODAY()-B2)=61),"X","")

Formula in G2:
=IF((TODAY()-B2)90,"X","")