How to search in a column by value =< 200 (run command)?

Question

I intend to search row by row within the column P:P a number less than or equal to 200 and execute a command. If more than 200 execute another. The values are defined as 0.0.

If .Value =< 200 Then

ActiveCell.FormulaR1C1 = "=RC[-5]*1.69*(1+30%)"
Lastrow = Range("A" & Rows.Count).End(xlUp).Row
Range("U2").AutoFill Destination:=Range("U2:U" & Lastrow)

ElseIf .Value > 200 Then

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

End If

REPLY:

Sub teste()
Dim rng As Range
Set rng = Range("P1:P300") 'Se colocar P:P vai até à última linha

For Each Row In rng.Rows

If Row.Value < "200" Then

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

ElseIf Row.Value >= "200" 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"

End for

End If

Next Row

End Sub

Answer 1

To make a loop in a column can be done as follows:

Dim rng as Range

Set rng = Selection ' ou Range("A1:A30") ou Range("P:P") no seu caso...

For Each Row In rng.Rows

 ' Seu código aqui...
 ' If row.value >= 200 Then 
 ' [...]

Next Row

Care with loop very large, depending on the performance. I think it would be better to know which line has data, so that you do not need to run the For Each by all Excel lines.

Another option would be to use as follows within the For Each, to check if there is any value in the cell:

For Each Row In rng.Rows

 If Row.value = "" then

   Exit Sub ' ou _Function_ se for uma função

 End If

Rebound that if you have any empty value between the values the function stops at this point, you should look for another way to test the cell not to use unnecessary resource.

I hope I’ve helped!