How to make a "for each" loop in column P up to the last line filled in VBA?

Question

How to make a "for each" loop in column P up to the last line filled in VBA?

Navigation

#1 by (1 votes)

3

If you set the entire column P:P the sheet will freeze. How do I set rng , any ideas?

CODE:

Sub teste()
Dim rng As Range
Set rng = Range("P2:P300") 'Estou a definir até à linha 300 para não quebrar

For Each Row In rng.Rows

If Row.Value > "0" And Row.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 Row.Value > "100" And Row.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 Row
End Sub

vba excel-vba

asked by anonymous 03.06.2017 / 19:30

1 answer

what is the purpose of these functions prefixed by "msg_" in PHP? Javascript - Validating numeric field (popup)

score 1 · Accepted Answer

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