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

Question

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

Navigation

#1 by (0 votes)

2

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 in 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.

vba excel-vba

asked by anonymous 05.06.2017 / 20:57

1 answer

Infix function for postfix How to package electron application for windows

score 0 · Answer 1

ANSWER: Cells (i, 26) .Select
Look in column P for a top or bottom value 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