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

Asked

Viewed 831 times

3

If you define the entire column P:P the leaf will freeze. How do I define the rng, any idea?

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

1 answer

1


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

  • Yes, up to the last line @Leandro Felipe Moreira!

  • How do I define that in Set rng = Range("P2:P300")?

  • So, apparently your code has some errors, what is the purpose of the code? You want the cell of the respective line to receive the formula?

  • The code fills up to the last line. But what I’m asking is how do I set it to find the last filled line. I can’t set up Range("P2:P300"), because it can be less or more lines.

  • Define the rng stays Set rng = Columns(16).End(xlDown).Row and the For stays For Each Row In rng

  • That gives tent!

  • I tested his code and apparently he responded correctly

  • It’s an expression! I mean the code breaks into Set rng = Columns(16).End(xlDown).Row, will not be by definition in Dim rng As Range? You’re selecting a column, right?

  • I changed your range, see now. You can take the Msgbox that I put

  • Excellent @ Leandro Felipe Moreira!

  • You are calculating 35% on all cells with different values! First value of cell P2 is 170.00.

Show 6 more comments

Browser other questions tagged vba excel-vba

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