Need macro for replace all function

[mahesh8498]

Hi All,

I am trying to get a macro for Replace all function. I have data in range of C3:W50. There are some codes which i wanted to convert into timing. E.g LA needs to be replaced with 1500 - 0000.

Please suggest me any short and easy macro.

Thanks in advance

 

[jfish1288]

Please clarify your question. If you are simply trying to replace values in your range I would suggest using the built in find and replace excel function. This can be accessed by Ctrl+F.

 

[mahesh8498]

I have data in multiple columns. Start column is C and end column is W.

I wanted to replace few codes into timing format.

for an example i have word LA and i wanted to replace that into timing i.e 15:00 - 23:00

I am trying to get the macro for that as i have 100 codes and Ctrl + F function will take long time to replace all values.

It will be great if i can get the macro for the same. I have one macro but its not replacing values for multiple column.

Macro i m using is as below.

Sub ReplaceAllShifts()
Dim LR&, i&
LR = Range("C" & Rows.Count).End(xlUp).Row


For i = 1 To LR
Range("C" & i).Value = Replace$(Range("C" & i).Value, "LA", "15:00 - 23:00", , , vbBinaryCompare)


Next i
End Sub


This macro is replacing values only for C coulmn and not allowing to edit for multiple column.

 

[jfish1288]

if the macro you posted in working for you i would just loop through the columns as well as the rows

I edited it to do that, see if it helps

Sub ReplaceAllShifts()
Dim LR&, i&
LR = Range("C" & Rows.Count).End(xlUp).Row

For i = 1 To LR
For x = 3 To 23
Cells(i, x).Value = Replace$(Range("C" & i).Value, "LA", "15:00 - 23:00", , , vbBinaryCompare)
Next x
Next i
End Sub

 

[Rick Rothstein]

I would think this one-liner would do what you want (at least for the LA text)...

Sub ReplaceAllShifts()
  Columns("C:W").Replace "LA", "15:00 - 23:00", xlWhole, ,False
End Sub

 

[mahesh8498]

Thanks Rick......

Much appriciated.........macro is too small to remember and its working perfectly.....i got what i want..........amazing.........


Thanks again!!!!!!!!!!!!!!