IF Function

R

Ranjit kurian

I have four columns, i need a macro to fill my fifth column using IF conditions

The Fist column contains Aging details like 1-30,31-60,61-90,91-180,181-360
and >360, this is always fixed.

The Second column contains Frequency details like Monthly,Quarterly, Semi
Annual, Annual this is always fixed.

The Third column contains Ages of Pymt details, here we have a numbers
begins from 1 till no end, but we have three categories here like >=61 or =
blank etc....

The fourth column contains Month details in this formate MMM/YY(Jan/09) (the
months and years are not fixed it can be any month any year)

The Fifth is my result column (Category), i need a macro as explained below

For Aging 1-30 and 31-60 there are no conditions, for these two aging the
fifth cloumn
(Category) result should be B/P

For Aging:61-90, Frequency:Quarterly,Annual,Semi Annual, for these the fifth
cloumn
(Category) result should be B/P

IF the Frequency: Monthly, Age of Pymt : <61,Month: Current month/Previous
month (Jan/09, Dec/08) then the fifth cloumn (Category) result should be B/P

IF the Frequency: Monthly, Age of Pymt : <61,Month: Prior to Current
month/Previous month (Nov/08, Feb/08) then the fifth cloumn (Category)
result should be NB/P

IF the Frequency: Monthly, Age of Pymt : >=61 or =blank ,Month: Current
month/Previous month (Jan/09, Dec/08) then the fifth cloumn (Category)
result should be B/NP

IF the Frequency: Monthly, Age of Pymt : >=61 or =blank ,Month: Prior to
Current month/Previous month (Nov/08, Feb/08) then the fifth cloumn
(Category) result should be NB/NP

IF the Frequency: Quarterly, Age of Pymt : <121,Month: Current
month/Previous month (Jan/09, Dec/08,Nov/08 take three months) then the fifth
cloumn (Category) result should be B/P

IF the Frequency: Quarterly, Age of Pymt : <121,Month: Prior to Current
month/Previous month (Oct/08, sept/08, Aug/08 take three months) then the
fifth cloumn (Category) result should be NB/P


IF the Frequency: Quarterly, Age of Pymt : >=121 or =blank,Month: Current
month/Previous month (Jan/09, Dec/08,Nov/08 take three months) then the fifth
cloumn (Category) result should be B/NP

IF the Frequency: Quarterly, Age of Pymt : >=121 or =blank,Month: Prior
Current month/Previous month (Oct/08, sept/08, Aug/08 take three months) then
the fifth cloumn (Category) result should be NB/NP

IF the Frequency: Annual, Age of Pymt : <181,Month: Current month/Previous
month (from current month calculate 12 months Jan/09 till Feb/08) then the
fifth cloumn (Category) result should be B/P

IF the Frequency: Annual, Age of Pymt : <181,Month: Prior to Current
month/Previous month (Jan/08, Dec/07) then the fifth cloumn (Category) result
should be NB/P


IF the Frequency: Annual, Age of Pymt : >=181 or =blank,Month: Current
month/Previous month (from current month calculate 12 months Jan/09 till
Feb/08) result should be B/NP

IF the Frequency: Annual, Age of Pymt : >=181 or =blank,Month: Prior Current
month/Previous month (Jan/08, Dec/07) then the fifth cloumn (Category) result
should be NB/NP
 
J

Joel

Below is a start to your results. I don't know enough information about the
current and previous month to finish the macro. How do you determine present
and previous month when there is only one date?


Sub GetResults()

RowCount = 1
Do While Range("A" & RowCount) <> ""
Aging = Range("A" & RowCount)
Frequency = Range("B" & RowCount)
AgeofPymt = Range("C" & RowCount)
MyDate = Range("D" & RowCount)
Select Case Aging

Case Is < 60:
Results = "B/P"
Case Is <= 90:
Select Case Frequency
Case "Quarterly", "Annual", "Semi Annual":
Results = "B/P"
Case "Monthly":
Select Case AgeofPymt
Case Is < 61:

End Select

End Select
End Select
Range("E" & RowCount) = Results

RowCount = RowCount + 1
Loop

End Sub
 
R

Ranjit kurian

Please find the below examples..

Aging Frequency Age Months Category
1-30 Monthly 27 Jan-09 B/P
1-30 Annual 360 Dec-08 B/P
1-30 Quarterly 55 Oct-08 B/P
31-60 Monthly 27 Jan-09 B/P
31-60 Annual Dec-08 B/P
31-60 Quarterly 60 Oct-08 B/P
91-180 Monthly 60 Jan-09 B/P
181-365 Monthly 55 Jan-09 B/P
365 Monthly 20 Dec-08 B/P 61-90 Monthly 10 Dec-08 B/P
91-180 Monthly 60 Oct-08 NB/P
181-365 Monthly 55 Feb-08 NB/P
61-90 Monthly 10 Aug-08 NB/P
91-180 Monthly 61 Jan-09 B/NP
181-365 Monthly Jan-09 B/NP
61-90 Monthly 360 Dec-08 B/NP
91-180 Monthly 71 Oct-08 NB/NP
181-365 Monthly 89 Feb-08 NB/NP
365 Monthly 90 Jan-08 NB/NP
Click to expand...
61-90 Monthly Aug-08 NB/NP
91-180 Quarterly 120 Jan-09 B/P
181-365 Quarterly 119 Dec-08 B/P
365 Quarterly 89 Nov-08 B/P
Click to expand...
61-90 Quarterly 60 Oct-08 NB/P
61-90 Quarterly 111 Jan-08 NB/P
91-180 Quarterly 121 Jan-09 B/NP
181-365 Quarterly 122 Dec-08 B/NP
61-90 Quarterly Nov-08 B/NP
91-180 Quarterly 130 Oct-08 NB/NP
181-365 Annual 180 Jan-09 B/P
91-180 Annual 111 Nov-08 B/P
181-365 Annual 121 Oct-08 B/P
 
J

Joel

I still don't know how to figure the following:

Month: Current month/Previous month (Jan/09, Dec/08)

Give an example in your data. The is only one moth in your column, not two.
 
J

Joel

I made some changes and getting conflicts with your expected results. Check
your original input and verify your description for the Aging requirements
are right. You will see if you run this code I get different results.
doesn't pay for me to continue until your explaination is consistent.

Sub GetResults()

RowCount = 2
CurrentMonth = Month(Date)
CurrentYear = Year(Date)
If CurrentMonth = 1 Then
PreviousMonth = 12
CurrentYear = CurrentYear - 1
Else
PreviousMonth = CurrentMonth - 1
End If

Do While Range("A" & RowCount) <> ""
Results = ""
Aging = Range("A" & RowCount)
Frequency = Range("B" & RowCount)
AgeofPymt = Range("C" & RowCount)
MyDate = Range("D" & RowCount)
MyMonth = Month(MyDate)
MyYear = Year(MyDate)

Select Case Aging

Case "1-30", "31-60":
Results = "B/P"
Case "61-90":
Select Case Frequency
Case "Quarterly", "Annual", "Semi Annual":
Results = "B/P"
Case "Monthly":
Select Case AgeofPymt
Case Is < 61:

End Select

End Select
End Select
Range("E" & RowCount) = Results

RowCount = RowCount + 1
Loop
 
R

Ranjit kurian

I think i confussed you, regarding month column the explantion goes like this

you will have only one month in each row, the month which ever is mentioned
in a column if its current month or (currentmonth-1) that is previous month
example:now we are in jan-2009(this is current month) if you minus 1 to
jan-2009 till be Dec-2008 (this is previous month) if you minus 1 again to
the previous month(Dec-2008) it will be a prior month.
 
J

Joel

I fugured out what you wanted. I a little bit futher along with the code but
still having problems understanding all the requirements. I down to this
requirement and it congflicts with a previous requirement. I think they have
different aging requirements

IF the Frequency: Quarterly, Age of Pymt : <121,Month: Current
month/Previous month (Jan/09, Dec/08,Nov/08 take three months) then the fifth
cloumn (Category) result should be B/P

conflicts with

For Aging:61-90, Frequency:Quarterly,Annual,Semi Annual, for these the fifth
cloumn
(Category) result should be B/P



See if you can solve the problem by yourself using the code below as a
reference. The rest of the code is minor changes to what I already have.




Sub GetResults()

RowCount = 2
CurrentMonth = Month(Date)
CurrentYear = Year(Date)
If CurrentMonth = 1 Then
PreviousMonth = 12
CurrentYear = CurrentYear - 1
Else
PreviousMonth = CurrentMonth - 1
End If

Do While Range("A" & RowCount) <> ""
Results = ""
Aging = Range("A" & RowCount)
Frequency = Range("B" & RowCount)
AgeofPymt = Range("C" & RowCount)
MyDate = Range("D" & RowCount)
MyMonth = Month(MyDate)
MyYear = Year(MyDate)

Select Case Aging

Case "1-30", "31-60":
Results = "B/P"
Case "61-90":
Select Case Frequency
Case "Quarterly", "Annual", "Semi Annual":
Results = "B/P"
Case "Monthly":
Select Case AgeofPymt
'A blank will be zero
Case 1 To 61:
If (MyMonth = CurrentMonth And _
MyYear = CurrentYear) Or _
(MyMonth = PreviousMonth And _
MyYear = PreviousYear) Then

Results = "B/P"
Else
Results = "NB/P"
End If
Case Else
If (MyMonth = CurrentMonth And _
MyYear = CurrentYear) Or _
(MyMonth = PreviousMonth And _
MyYear = PreviousYear) Then

Results = "B/NP"
Else
Results = "NB/NP"
End If


End Select

End Select
End Select
Range("E" & RowCount) = Results

RowCount = RowCount + 1
Loop

End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

If function for Current month 2
variable for Date 10
How to Show 12 months dates on the basis of from today's date in 9
Wanted to show 12 month Date in Pivot table 2
Nested If function 3
Date Array Problem 2
Hide the current month columns 9
IF function question 1

Top