vba – Search column P for a value > or < and calculate that value in cell U?

Question:

I want to thank @Leandro Felipe Moreira for his 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 so on.

Answer:

ANSWER: Cells(i, 26).Select
Search column P for a higher or lower value and calculate that 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
Scroll to Top
AllEscort