Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

Macro coding help


(!)

prempkn's Avatar
prempkn prempkn is offline
Member with 37 posts.
THREAD STARTER
 
Join Date: Dec 2011
Experience: Beginner
17-Aug-2012, 10:18 PM #1
Macro coding help
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
File Type: zip WF sheet.zip (246.3 KB, 10 views)
etaf's Avatar
etaf   (Wayne) etaf is offline
Computer Specs
Moderator with 51,605 posts.
 
Join Date: Oct 2003
Location: Surrey, UK
18-Aug-2012, 03:07 AM #2
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
__________________
Wayne
Please let us know what the final solution was to any problem posted

Last edited by etaf; 18-Aug-2012 at 04:02 AM..
20_2_Many's Avatar
20_2_Many 20_2_Many is offline
Member with 497 posts.
 
Join Date: Jun 2012
18-Aug-2012, 06:57 AM #3
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
Garf13LD's Avatar
Garf13LD Garf13LD is offline
Member with 455 posts.
 
Join Date: Apr 2012
Experience: Intermediate
18-Aug-2012, 10:59 AM #4
same as etf's logic except i treat +??days as >=??days
Attached Files
File Type: zip WF sheet.zip (316.9 KB, 18 views)
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
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.


(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑