examine value, leave it or increment it

[Ivano]

Hi,
I want a macro that will examin cells within range A1 to A10 and if it is
equal to or less then 5 leave it alone else increment it by one.

Thanks
Ivano,

 

[okrob]

Hi,
I want a macro that will examin cells within range A1 to A10 and if it is
equal to or less then 5 leave it alone else increment it by one.

Thanks
Ivano,

This should work.
Rob

Sub test()
Dim myCell As Range, rng As Range
Set rng = Range("A1:A10")
For Each myCell In rng
If Abs(myCell) > 5 Then myCell = myCell + 1
Next
End Sub

 

[Jim Thomlinson]

Try this...

Sub IncrementStuff()
Dim rngToIncrement As Range
Dim rng As Range

Set rngToIncrement = Sheets("Sheet1").Range("a1:a10")
For Each rng In rngToIncrement
If rng.Value > 5 Then rng.Value = rng.Value + 1
Next rng
End Sub

 

[Susan]

increment each cell within that range?
or increment the total?
i believe you mean each cell

something like this would work (not tested)

sub ivano()

dim c as range
dim myrange as range
dim ivalue as integer
dim ws as worksheet

set ws = activesheet
set myrange = ws.range("a1:a10")

for each c in myrange
ivalue = c 'this might need to be c.value
if ivalue <= "5" then
'do nothing
else
ivalue = ivalue+1
end if
next c

end sub

hope it helps!
susan

 

[Susan]

whoops

if ivalue <= "5" then
'do nothing

should be
if ivalue>="5" then

but everybody else's suggestions did the same basic thing, but were
much more concise.
susan

 

[Gord Dibben]

Sub increment()
Dim r As Range
Dim myRange As Range
Set myRange = ActiveSheet.Range("A1:A10")
For Each r In myRange
If r.Value > 5 Then r.Value = r.Value + 1
Next
End Sub


Gord Dibben MS Excel MVP