1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Macro coding help

Discussion in 'Business Applications' started by prempkn, Aug 17, 2012.

Thread Status:
Not open for further replies.
Advertisement
  1. prempkn

    prempkn Thread Starter

    Joined:
    Dec 11, 2011
    Messages:
    37
    Dear Techsupportguy,

    In the attached excel file there is "Priority" And "Validation" Sheet's......

    In the "Priority sheet" "M" Column we need to apply priority like 1,2,3,4... up to 9.....
    In the "Validation" sheet there is rules how to apply priority level....

    Eg : If "Amount (USD)" in "L" Column contains Above 50000 USD and "Ageing vs. WF tool" in Column "H" contains 30days and above that has to be priority level 1. likewise we need to apply priority level for all numbers based on the rules given in the Validation sheet........

    Please help me with coding to apply priority level's in one click......

    Thanks
    Prem
     

    Attached Files:

  2. etaf

    etaf Wayne Moderator

    Joined:
    Oct 2, 2003
    Messages:
    55,851
    you should be able to do this with an embedded IF statement

    just want to check the validation table

    anything <= 10days = level 9
    >10 days and any workflow usd = will be level 8
    >15 days and if workflows usd >10,000 = level 5
    >30 days and if workflows usd anything under or = 10,000 = level 7
    >30 days and if workflows usd >10,000 and <= 25,000 = level 3
    >30 days and if workflows usd >25,000 and <= 50,000 = level 2
    >30 days and if workflows usd >50,000 = level 1
    >50 days is level 6 - but I assume you dont want to alter the USD - so thats actually
    >50 days and if workflows usd <=10,000 = level 6
    >70 days and if workflows usd <=10,000 = level 4

    so I would almost write that IF statement

    i'll try out and post back - but maybe tomorrow - so someone else may answer

    would you confirm the above though

    if so then I think this is the order the IF statement needs to be written to ensure the levels are not overwritten
    >30 days and if workflows usd >50,000 = level 1
    >30 days and if workflows usd >25,000 and <= 50,000 = level 2
    >30 days and if workflows usd >10,000 and <= 25,000 = level 3
    >70 days and if workflows usd <=10,000 = level 4
    >50 days and if workflows usd <=10,000 = level 6
    >30 days and if workflows usd <= 10,000 = level 7
    >15 days and if workflows usd >10,000 = level 5
    >10 days and any workflow usd = will be level 8
    anything <= 10days = level 9
    would you check the logic flow - and assume you start at the top and work down and that any changes in levels are correct

    clearly the logic above is not the same as you have used in the spreadsheet
    i used this formula and in the first few cells I got a level 5 - where you had a 1 or n/a
    Code:
    =IF(AND(H2>30,L2>50000),1,IF(AND(H2>30,L2>25000),2,IF(AND(H2>30,L2>10000),3,IF(H2>70,4,IF(H2>50,6,IF(H2>30,7,IF(AND(H2>15,L2>10000),5,IF(H2>10,8,9))))))))
    
    so i have misunderstood the requirement completely :( :(

    in rows 2, 3 and 4

    M2 = level 1
    you have H2 = 18 L2 = 686,647 - so not a level 1 as its less than 30days
    but greater than 15 days and 10,000 = level 5
    M3 = 1
    you have H3= 18 L3 = 561500 - so not level 1 as its less than 30days
    but greater than 15 days and 10,000 = level 5
    M4 = N/A
    you have H4 = 30 and L4 =304,311
    so greater than 15days and greater than 10000 = level 5
     
  3. 20_2_Many

    20_2_Many

    Joined:
    Jun 29, 2012
    Messages:
    518
    I have the same misunderstanding.
    Your 'Validation' rules seem to override other parameters of your validation because you use ALL as part of the requirement. ( ' ALL over 49 days = Level 6 or ' ALL over 69 days = Level 4)
    M26 is Level 4 but under 15 days so ALL amounts and ALL days make it Level 9 and Level 4 is + 70 days

    Here is some code that might help with your better understanding of the requirements

    Option Explicit
    Option Compare Text

    Dim LastRow, I, x, y, PL As Integer

    Sub SetPriority()
    Sheets("Priority WF").Select
    LastRow = Range("A65536").End(xlUp).Row
    For I = 2 To LastRow
    PL = 9
    Range("M" & I).Select
    ActiveCell = 9
    x = ActiveCell.Offset(0, -1)
    y = ActiveCell.Offset(0, -5)
    Select Case y
    Case Is > 69
    ActiveCell = 4
    If x >= 10000 Then ActiveCell = 3
    If x >= 25000 Then ActiveCell = 2
    If x >= 50000 Then ActiveCell = 1
    Case 50 To 69
    ActiveCell = 6
    If x >= 10000 Then ActiveCell = 3
    If x >= 25000 Then ActiveCell = 2
    If x >= 50000 Then ActiveCell = 1
    Case 30 To 49
    ActiveCell = 7
    If x >= 10000 Then ActiveCell = 3
    If x >= 25000 Then ActiveCell = 2
    If x >= 50000 Then ActiveCell = 1
    Case 15 To 29
    ActiveCell = 5
    If x >= 10000 Then ActiveCell = 3
    If x >= 25000 Then ActiveCell = 2
    If x >= 50000 Then ActiveCell = 1
    Case 10 To 14
    ActiveCell = 8
    If x >= 10000 Then ActiveCell = 3
    If x >= 25000 Then ActiveCell = 2
    If x >= 50000 Then ActiveCell = 1
    End Select
    Next I
    USDAging
    End Sub

    Sub USDAging()
    ActiveWorkbook.Worksheets("Priority WF").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Priority WF").Sort.SortFields.Add Key:=Range( _
    "L2:L1675"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
    xlSortNormal
    ActiveWorkbook.Worksheets("Priority WF").Sort.SortFields.Add Key:=Range( _
    "H2:H1675"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
    xlSortNormal
    With ActiveWorkbook.Worksheets("Priority WF").Sort
    .SetRange Range("A1:S1675")
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    End With
    End Sub
     
  4. Garf13LD

    Garf13LD

    Joined:
    Apr 17, 2012
    Messages:
    455
    same as etf's logic except i treat +??days as >=??days
     

    Attached Files:

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1065510