PDA

View Full Version : Macro to do logic based on other column



arnab0711
01-26-2011, 04:36 AM
hi,
I want do a macro which will do somewhat like this -
1)The macro will lookup for a number in column "N"
2)In result will be in column "AR"
3)The logic will be if the number is >180 it will ">180"
if the # is >90 it will show "90 - 180"
if the # is >60 it will show "60-90"
if the # is >30 it will show "30-60"
if the # is <30 it will show "<30"

I have written the code as
Sub logic()
Dim i As Long
For i = 2 To Range("AR" & Rows.Count).End(xlUp).Row
Select Case Range("AQ" & i).Value
Case Is > 180
Range("AR" & i).Value = "> 180"
Case Is >= 90
Range("AR" & i).Value = "90 - 180"
Case Is >= 60
Range("AR" & i).Value = "60 - 90"
Case Is >= 30
Range("AR" & i).Value = "30-60"
Case Is >= 0
Range("AR" & i).Value = "< 30"
Case Else
' Don't change anything
End Select
Next i
End Sub

p45cal
01-26-2011, 05:18 AM
It seems to be fine except that column AR is blank to start with so either put something in it in the last row you want to be processed or use another column to decide how many rows to process.

For i = 2 To Range("AR" & Rows.Count).End(xlUp).Row

Range("AR" & Rows.Count).End(xlUp).Row becomes 1 so the line will be interpreted as:
For i = 2 To 1
so no iteration.

Oh, I see, you want to process column N. Change the line:
Select Case Range("AQ" & i).Value
to:
Select Case Range("N" & i).Value

and perhaps column A could be used to determine how many rows to process so change:
For i = 2 To Range("AR" & Rows.Count).End(xlUp).Row
to:
For i = 2 To Range("A" & Rows.Count).End(xlUp).Row

leaving:
Sub logic()
Dim i As Long
For i = 2 To Range("A" & Rows.Count).End(xlUp).Row
Select Case Range("N" & i).Value
Case Is > 180
Range("AR" & i).Value = "> 180"
Case Is >= 90
Range("AR" & i).Value = "90 - 180"
Case Is >= 60
Range("AR" & i).Value = "60 - 90"
Case Is >= 30
Range("AR" & i).Value = "30-60"
Case Is >= 0
Range("AR" & i).Value = "< 30"
Case Else
' Don't change anything
End Select
Next i
End Sub

shrivallabha
01-26-2011, 08:47 AM
Looking at the logic, a value like 181 will be true in all conditions. You can ammend the code to add upper binding condition or use formula like:

=IF(AND(IF(AQ3>=0,1,0),IF(AQ3<30,1,0))=TRUE,"<30",IF(AND(IF(AQ3>=30,1,0),IF(AQ3<60,1,0))=TRUE,"30-60",IF(AND(IF(AQ3>=60,1,0),IF(AQ3<90,1,0))=TRUE,"60-90",IF(AND(IF(AQ3>=90,1,0),IF(AQ3<180,1,0))=TRUE,"90-180",IF(AQ3>180,">180",AQ3)))))

shrivallabha
01-26-2011, 10:22 PM
It should be 'N' in the formula and Not 'AQ'. And p45cal's code is also just fine. It skips all other select cases once it finds its match.