Calculate 2 values for a cell and compare

[rana8689]

Hi,
I need to insert a formula for the minimum amount based on 2 calculations. I have the following codes. The codes have 2 criteria. When I run the macro it does not consider the second criteria, which is value in the cell.

Kindly help me:

Sub TRYMAY29()
Dim i, LastRow
LastRow = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LastRow

If Cells(i, "A") = "Roger" And Cells(i, "G") = "AV1" And Cells(i, "H") = "HDWE" Then
Cells(i, "w").formula = "=R" & i & "*0.05"
ElseIf Cells(i, "A") = "Roger" And Cells(i, "H") <> "HDWE" And ("=RC[-4]*0.20") >= ("=RC[-2]*0.05") Then
Cells(i, "w").formula = "=U" & i & "*0.05"
ElseIf Cells(i, "A") = "Roger" And Cells(i, "H") <> "HDWE" And ("RC[-4]*0.20") <= ("RC[-2]*0.05") Then
Cells(i, "w").formula = "=R" & i & "*0.20"
Else: Cells(i, "W").formula = "Please update record"
End If
Next
End Sub

 

[JackDanIce]

What cells is this part of your code referring to:

 ("=RC[-4]*0.20") >= ("=RC[-2]*0.05") Then

It's not relating to any range which is why I think it's failling.

Also, you should declare your variables with a data type, rather than let Excel default them as type variant, i.e.

Dim i as Long, LastRow as Long

 

[rana8689]

I recorded a macro and I got the codes from:

Activecell.formulaR1C1 = "=RC[-2]*0.05"

I tried replacing that with "=U" & i & "*0.05"

When the Macro runs it inserts formula on Column W.
Column A is the Sales Rep Name
Column G is the SO type
Column H is the item Type
Column R is the Margin
Column S extended Price,
Column W Commission calculation

Thanks for your help.

 

[JackDanIce]

Can you post all of your code? I think you're trying to see if 20% of the extended price is greater than 5% of whatever price is in column U and then you want 5% of whatever is in column U

 

[rana8689]

The code I orginally sent has all the codes.
Once this works I will add similar codes for the codes for all the sales rep as each one has different rate.

Thanks

 

[JackDanIce]

You said you recorded a macro but that does not appear to be part of your original code..

Totally guessing what you want as I'm about to go to bed and too tired to explain the fact I have no idea what your sheet looks like so can only make inferences from what you have given me (e.g. you try to tell me what is wrong with my plumbing in my house if I tell you it is made of bricks.. not exactly clear) but anyway:

Sub TRYMAY29()
Dim i As Long, LastRow As Long
Application.ScreenUpdating = False
LastRow = Range("A" & Rows.Count).End(xlUp).Row

For i = 2 To LastRow
    If Cells(i, 1) = "Roger" Then
        If Cells(i, 7) = "AV1" And Cells(i, "H") = "HDWE" Then
            Cells(i, 23).Formula = "=R" & i & "*0.05"
        ElseIf Cells(i, 8) <> "HDWE" And (Cells(i, 19) * 0.2) >= (Cells(i, 21) * 0.05) Then
            Cells(i, 23).Formula = "=U" & i & "*0.05"
        ElseIf Cells(i, 8) <> "HDWE" And (Cells(i, 19) * 0.2) <= (Cells(i, 21) * 0.05) Then
            Cells(i, 23).Formula = "=R" & i & "*0.20"
        Else
            Cells(i, 23).Formula = "Please update record"
        End If
    End If
Next
Application.ScreenUpdating = True
End Sub

 

[rana8689]

Getting back to work after one day of headache.

Thanks Jack, your solution reduced my headache.
It works well.
However I am getting Procedure too long error.
I tried splitting the procedure and then calling both from a third procedure.
It does not work.
If possible please help.

 

[JackDanIce]

rana8689, that is a very short piece of code. On this board and personally used much bigger codes in a single module. I'm not sure what is causing that message. Perhaps someone else can help otherwise ask your IT support