Search column P for a value > or < and calculate this value in cell U?

Asked

Viewed 399 times

3

From now on I want to thank @Leandro Felipe Moreira for the help with the following code: 

Sub teste()

rng = Columns(16).End(xlDown).Row

MsgBox rng

For i = 1 To rng

    If Cells(i, 16).Value > "0" And Cells(i, 16).Value <= "100" Then

        Range("U2").Select
        ActiveCell.FormulaR1C1 = "=RC[-5]*1.69*(1+40%)"
        lastrow = Range("A" & Rows.Count).End(xlUp).Row
        Range("U2").AutoFill Destination:=Range("U2:U" & lastrow)
        Selection.NumberFormat = "0"

    ElseIf Cells(i, 16).Value > "100" And Cells(i, 16).Value <= "150" Then

        Range("U2").Select
        ActiveCell.FormulaR1C1 = "=RC[-5]*1.69*(1+35%)"
        lastrow = Range("A" & Rows.Count).End(xlUp).Row
        Range("U2").AutoFill Destination:=Range("U2:U" & lastrow)
        Selection.NumberFormat = "0"

    End If

Next i

End Sub

The problem is filling up to the last cell with: 

Lastrow = Range("A" & Rows.Count).End(xlUp).Row
Range("U2").AutoFill Destination:=Range("U2:U" & Lastrow)

I want the calculation to be done in the correct cell. That is, look in column P1 and present the calculation in column U1 and successively.

vba excel-vba

  • You can help me with this @Evert?

  • Hello friend! Even if you mark with @NAME either in comment or in a question/answer the user does not receive this notification, unless you have already participated in your question/answer with a comment.

  • Only hj saw your question and it’s good to have found your answer.

1 answer

1

REPLY: Cells(i, 26). Select
Search column P for a value superior or inferior and calculate this value in cell Z?

Sub teste()
Application.ScreenUpdating = False

rng = Columns(16).End(xlDown).Row

For i = 1 To rng

    If Cells(i, 16).Value = 0 Then

        Cells(i, 26).Select
        On Error Resume Next
        ActiveCell.FormulaR1C1 = "0"
        Selection.NumberFormat = "0"

    ElseIf Cells(i, 16).Value > 0 And Cells(i, 16).Value <= 50 Then

        Cells(i, 26).Select
        On Error Resume Next
        ActiveCell.FormulaR1C1 = "=RC[-10]*1.7*(1+25%)"
        Selection.NumberFormat = "0"

    ElseIf Cells(i, 16).Value > 50 And Cells(i, 16).Value <= 100 Then

        Cells(i, 26).Select
        On Error Resume Next
        ActiveCell.FormulaR1C1 = "=RC[-10]*1.7*(1+20%)"
        Selection.NumberFormat = "0"

    ElseIf Cells(i, 16).Value > 100 And Cells(i, 16).Value <= 200 Then

        Cells(i, 26).Select
        On Error Resume Next
        ActiveCell.FormulaR1C1 = "=RC[-10]*1.7*(1+15%)"
        Selection.NumberFormat = "0"

    End If

Next i

End Sub

Browser other questions tagged vba excel-vba

You are not signed in. Login or sign up in order to post.