| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
IF Function
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 "Ranjit kurian" wrote: 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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
IF Function
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 365 Monthly 20 Jan-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 365 Monthly 80 Dec-08 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 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 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 365 Annual 121 Jan-08 NB/P 181-365 Annual 130 Dec-07 NB/P 61-90 Annual 181 Jan-09 B/NP 91-180 Annual Nov-08 B/NP 181-365 Annual 360 Oct-08 B/NP 365 Annual 298 Jan-08 NB/NP 61-90 Annual 380 Dec-07 NB/NP 365 Annual 330 Nov-07 NB/NP "Joel" wrote: 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 "Ranjit kurian" wrote: 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
IF Function
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. "Ranjit kurian" wrote: 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 365 Monthly 20 Jan-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 365 Monthly 80 Dec-08 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 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 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 365 Annual 121 Jan-08 NB/P 181-365 Annual 130 Dec-07 NB/P 61-90 Annual 181 Jan-09 B/NP 91-180 Annual Nov-08 B/NP 181-365 Annual 360 Oct-08 B/NP 365 Annual 298 Jan-08 NB/NP 61-90 Annual 380 Dec-07 NB/NP 365 Annual 330 Nov-07 NB/NP "Joel" wrote: 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 "Ranjit kurian" wrote: 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
IF Function
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. "Joel" wrote: 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. "Ranjit kurian" wrote: 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 365 Monthly 20 Jan-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 365 Monthly 80 Dec-08 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 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 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 365 Annual 121 Jan-08 NB/P 181-365 Annual 130 Dec-07 NB/P 61-90 Annual 181 Jan-09 B/NP 91-180 Annual Nov-08 B/NP 181-365 Annual 360 Oct-08 B/NP 365 Annual 298 Jan-08 NB/NP 61-90 Annual 380 Dec-07 NB/NP 365 Annual 330 Nov-07 NB/NP "Joel" wrote: 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 "Ranjit kurian" wrote: 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
IF Function
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 "Ranjit kurian" wrote: 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. "Joel" wrote: 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. "Ranjit kurian" wrote: 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 365 Monthly 20 Jan-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 365 Monthly 80 Dec-08 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 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 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 365 Annual 121 Jan-08 NB/P 181-365 Annual 130 Dec-07 NB/P 61-90 Annual 181 Jan-09 B/NP 91-180 Annual Nov-08 B/NP 181-365 Annual 360 Oct-08 B/NP 365 Annual 298 Jan-08 NB/NP 61-90 Annual 380 Dec-07 NB/NP 365 Annual 330 Nov-07 NB/NP "Joel" wrote: 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 "Ranjit kurian" wrote: 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
IF Function
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 "Ranjit kurian" wrote: 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 365 Monthly 20 Jan-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 365 Monthly 80 Dec-08 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 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 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 365 Annual 121 Jan-08 NB/P 181-365 Annual 130 Dec-07 NB/P 61-90 Annual 181 Jan-09 B/NP 91-180 Annual Nov-08 B/NP 181-365 Annual 360 Oct-08 B/NP 365 Annual 298 Jan-08 NB/NP 61-90 Annual 380 Dec-07 NB/NP 365 Annual 330 Nov-07 NB/NP "Joel" wrote: 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 "Ranjit kurian" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel Data Validation/Lookup function does function correcty | Excel Worksheet Functions | |||
| User Function Question: Collect Condition in Dialog Box - But How toInsert into Function Equation? | Excel Programming | |||
| LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
| Excel - User Defined Function Error: This function takes no argume | Excel Programming | |||
| Adding a custom function to the default excel function list | Excel Programming | |||
Hybrid Mode
