Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to find and replace with criteria
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to find and replace with criteria
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 "basic" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to find and replace with criteria
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 -- Regards, Tom Ogilvy "basic" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to find and replace with criteria
Your code worked great--thanks a lot.
"Tom Ogilvy" wrote: 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 -- Regards, Tom Ogilvy "basic" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple Criteria Find & Replace | Excel Worksheet Functions | |||
Customized Find/Replace to Update Numbers Using Specified Criteria | Excel Discussion (Misc queries) | |||
Find & Replace and Find & Insert macro help needed | Excel Programming | |||
Macro to Find & Replace | Excel Worksheet Functions | |||
Using Find and Replace to replace " in a macro | Excel Programming |