Macro to find and replace with criteria

[Guest]

I am trying to get a macro to check column C which is a list of numbers and
letters. If the number is less then 17 then change it to 1.

Example:

Original C Changed to
05 01
wt wt
63 63
16 01
12 01
33 33

Thank You,

Tom

 

[Guest]

Try this

Sub test()

Range("C1").Activate

Do Until IsEmpty(ActiveCell)

If ActiveCell.Value < 17 Then
ActiveCell.Value = 1
ActiveCell.Offset(1,0).Activate

Else ActiveCell.Offset(1,0).Activate

End If

Loop

End Sub

 

[Guest]

Assume the data starts in C2 and numbers are stored as numbers and not text:

Sub ProcessData()
Dim rng as Range, rng1 as range
Dim cell as Range
set rng =range("C2",cells(rows.count,3).End(xlup))
on error resume next
set rng1 = rng.specialcells(xlconstants,xlNumbers)
on error goto 0
if not rng1 is nothing then
for each cell in rng1
if cell.value < 17 then
cell.value = 1
end if
next
Else
Msgbox "No numbers found"
end if
End Sub

 

[Guest]

Your code worked great--thanks a lot.

Assume the data starts in C2 and numbers are stored as numbers and not text:

Sub ProcessData()
Dim rng as Range, rng1 as range
Dim cell as Range
set rng =range("C2",cells(rows.count,3).End(xlup))
on error resume next
set rng1 = rng.specialcells(xlconstants,xlNumbers)
on error goto 0
if not rng1 is nothing then
for each cell in rng1
if cell.value < 17 then
cell.value = 1
end if
next
Else
Msgbox "No numbers found"
end if
End Sub