Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Change Values to Something Else
I need a macro that looks for certain values on a sheet and changes them, for
example: Change 1 to 1 Change 2 to 2 Change 11/2 to 1-1/2 Change 15/16 to 1-5/16 Change 113/16 to 1-13/16 Each of those values are in their own cell. There are some cells that have phrases such as: 2 Pieces I dont want that to get changed to 2 Pieces Thank you, Jeff Jensen |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Change Values to Something Else
Try this small macro:
Sub Jensen() Dim oldd(10) As String Dim neww(10) As String dq = Chr(34) oldd(0) = "1" oldd(1) = "2" oldd(2) = "11/2" oldd(3) = "15/16" oldd(4) = "113/16" neww(0) = "1" & dq neww(1) = "2" & dq neww(2) = "1-1/2" neww(3) = "1-5/16" neww(4) = "1-13/16" For Each r In ActiveSheet.UsedRange v = r.Value For i = 0 To 4 If v = oldd(i) Then r.Value = neww(i) End If Next Next End Sub -- Gary''s Student - gsnu200841 "Jeff Jensen" wrote: I need a macro that looks for certain values on a sheet and changes them, for example: Change 1 to 1 Change 2 to 2 Change 11/2 to 1-1/2 Change 15/16 to 1-5/16 Change 113/16 to 1-13/16 Each of those values are in their own cell. There are some cells that have phrases such as: 2 Pieces I dont want that to get changed to 2 Pieces Thank you, Jeff Jensen |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Change Values to Something Else
Thank you Gary''s Student!
It works great and will save me so much time on a lot of projects! I may even be able to do some fishing this summer! Thanks again, Jeff Jensen "Gary''s Student" wrote: Try this small macro: Sub Jensen() Dim oldd(10) As String Dim neww(10) As String dq = Chr(34) oldd(0) = "1" oldd(1) = "2" oldd(2) = "11/2" oldd(3) = "15/16" oldd(4) = "113/16" neww(0) = "1" & dq neww(1) = "2" & dq neww(2) = "1-1/2" neww(3) = "1-5/16" neww(4) = "1-13/16" For Each r In ActiveSheet.UsedRange v = r.Value For i = 0 To 4 If v = oldd(i) Then r.Value = neww(i) End If Next Next End Sub -- Gary''s Student - gsnu200841 "Jeff Jensen" wrote: I need a macro that looks for certain values on a sheet and changes them, for example: Change 1 to 1 Change 2 to 2 Change 11/2 to 1-1/2 Change 15/16 to 1-5/16 Change 113/16 to 1-13/16 Each of those values are in their own cell. There are some cells that have phrases such as: 2 Pieces I dont want that to get changed to 2 Pieces Thank you, Jeff Jensen |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Change Values to Something Else
Thank you for the feedback!
-- Gary''s Student - gsnu200841 "Jeff Jensen" wrote: Thank you Gary''s Student! It works great and will save me so much time on a lot of projects! I may even be able to do some fishing this summer! Thanks again, Jeff Jensen "Gary''s Student" wrote: Try this small macro: Sub Jensen() Dim oldd(10) As String Dim neww(10) As String dq = Chr(34) oldd(0) = "1" oldd(1) = "2" oldd(2) = "11/2" oldd(3) = "15/16" oldd(4) = "113/16" neww(0) = "1" & dq neww(1) = "2" & dq neww(2) = "1-1/2" neww(3) = "1-5/16" neww(4) = "1-13/16" For Each r In ActiveSheet.UsedRange v = r.Value For i = 0 To 4 If v = oldd(i) Then r.Value = neww(i) End If Next Next End Sub -- Gary''s Student - gsnu200841 "Jeff Jensen" wrote: I need a macro that looks for certain values on a sheet and changes them, for example: Change 1 to 1 Change 2 to 2 Change 11/2 to 1-1/2 Change 15/16 to 1-5/16 Change 113/16 to 1-13/16 Each of those values are in their own cell. There are some cells that have phrases such as: 2 Pieces I dont want that to get changed to 2 Pieces Thank you, Jeff Jensen |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How does one auto-change values? | Excel Worksheet Functions | |||
Lookup and Change Values | Excel Worksheet Functions | |||
Conditional formatting does not change when values change. ?? | Excel Worksheet Functions | |||
How do I change a range of values from + to -? | Excel Discussion (Misc queries) | |||
change values globally | Excel Worksheet Functions |