macro correction

[jack999]

I would like to correct this code as details given below

Scheme amount = 800 then amount need to divide to SalesRep presented and Half day presented.

Suppose 3 SalesRep worked full day and 1 SalesRep worked half day, then full day worked 3 rep will get 225 each and 1 Rep will get 125. So how can I change this code.

[pre]

If wks1.Cells(i, j).Value = "P" Then
Incentive = SchemeAmount / SaleRepInc
wks2.Cells(i, j).Value = Incentive
ElseIf wks1.Cells(i, j).Value = "H" Then
Incentive = SchemeAmount / SaleRepInc
wks2.Cells(i, j).Value = Incentive / 2

[/pre]

 

[Luke M]

I'm not exactly sure what you're question is. Could you try rewording, or provide more examples?

 

[jack999]

Suppose Scheme amount = 800. I would like to give SalesRep who are present fullday and Half Day. Here 3 SalesRep Present (Worked) full day and One SalesRep present (Worked) Half Day. So from the amount of 800 need to divide each 3 SalesRep worked full day with 225 and Half Day worked SalesRep should get 125.

That is 225*3 = 675 + 125 = 800.

Here in the code only calculation need to correct

[pre]

If wks1.Cells(i, j).Value = "P" Then
Incentive = SchemeAmount / SaleRepInc
wks2.Cells(i, j).Value = Incentive
ElseIf wks1.Cells(i, j).Value = "H" Then
Incentive = SchemeAmount / SaleRepInc
wks2.Cells(i, j).Value = Incentive / 2

[/pre]

As per this calculation 800 divide to 3 "P" SalesRep = 200*3 = 600

1 "H" SalesRep = 100 = 100


So it will come 700 only. I want to divide 800 for all who "P" and "H"

 

[Hui]

Jack999


I'd suggest supplying more code as we don't know what values wks1(), wks2(), SchemeAmount Incentive or SalesRepInc hold


Better still is to post the workbook so we can work through it for you

Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook

 

[NARAYANK991]

Hi ,


The problem is you are giving an example where you have considered specific values for the SchemeAmount and Incentive ; can you explain why those who were present the whole day should get 225 , and those who were present for only half the day should get 125 ?


Please understand that for the example you have posted , there are an infinite number of values for which the equation 3x + y = 800 can be satisfied ; the values of 225 and 125 are just one such combination ; you can have 230 and 110 , 220 and 140 or any other combination.


How have you decided that 225 and 125 is the right combination ? What if , instead of 800 as the SchemeAmount , it had been 1800 ?


Narayan

 

[jack999]

http://www20.zippyshare.com/v/88180771/file.html


Here with I attached excel sheets (2sheets) also macro.

 

[Debraj]

Hi Jack999..


I have not tried to optimize.. or to shorten the code.. Just sharping your logic..

[pre]

Sub MonthlyIncentiveMacro() 'Created By Lance
Dim i As Integer, j As Integer, Incentive As Integer
Dim SaleRep As Integer, SaleAmount As Integer, SchemeAmount As Integer, SaleRepInc As Integer
Dim wks1 As Worksheet
Dim wks2 As Worksheet
Set wks1 = Worksheets("Sheet1")
Set wks2 = Worksheets("Sheet2")
SaleRep = 0
For i = 2 To 16 'Column 2 to 16 (
For j = 4 To 7
If wks1.Cells(i, j).Value = "P" Or wks1.Cells(i, j).Value = "H" Then SaleRep = SaleRep + 1
Next j
SaleAmount = wks1.Cells(i, 2)
If SaleAmount < 4999 Then
wks1.Cells(i, 3) = 0
ElseIf SaleAmount < 5999 Then
wks1.Cells(i, 3) = SaleRep * 100
ElseIf SaleAmount > 6000 Then
wks1.Cells(i, 3) = SaleRep * 200
End If
SaleRep = 0
Next i
wks1.Range("A1:G16").Copy Destination:=wks2.Range("A1")
wks2.Range("D2:G16").ClearContents
For i = 2 To 16
SaleRepInc = 0
SchemeAmount = wks2.Cells(i, 3)
For j = 4 To 7
'If wks1.Cells(i, j).Value = "P" Or wks1.Cells(i, j).Value = "H" Then SaleRepInc = SaleRepInc + 1
'----
Select Case wks1.Cells(i, j)
Case "P"
SaleRepInc = SaleRepInc + 2
Case "H"
SaleRepInc = SaleRepInc + 1
End Select
'----
Next j
For j = 4 To 7
If wks1.Cells(i, j).Value = "P" Then
Incentive = SchemeAmount / SaleRepInc
wks2.Cells(i, j).Value = Incentive * 2 '----
ElseIf wks1.Cells(i, j).Value = "H" Then
Incentive = SchemeAmount / SaleRepInc
wks2.Cells(i, j).Value = Incentive '----
Else
wks2.Cells(i, j).Value = 0
End If
Next j
SaleRepInc = 0
Next i
End Sub

[/pre]

You can check the modified LOGIC in ('----) area

Just used "P" as 2 and "H" as 1.. where you have used both as "1"

Then.. I divided the Total Scheme Amount among them..


Regards,

Deb