How to look up a column by value = 200 (execute command)?

Question

How to look up a column by value = 200 (execute command)?

Navigation

#1 by (0 votes)

1

I want to search the line within the P:P column for a number less than or equal to 200 and run a command. If it is greater than 200 perform another. The values are thus defined 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

ANSWER:

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

vba excel-vba

asked by anonymous 02.06.2017 / 16:40

1 answer

The crash application whenever I click the calculate button [closed] Login page and password

score 0 · Accepted Answer

To do 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

Caution with loop too large, depending on performance. I think it would be better to know just what line has data, so you do not have to run Each across all Excel rows.

Another option would be to use the For Each as follows 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

I emphasize that if there is any empty value between the values the function stops at this point, and I should look for another way to test the cell for not using unnecessary resource.

I hope I have helped!