replace a whole cell

[ap2015]

Wanting to be able to run a code that is similar to

Sub MultiFindNReplace()

'Update 20140722

Dim Rng As Range

Dim InputRng As Range, ReplaceRng As Range

xTitleId = "KutoolsforExcel"

Set InputRng = Application.Selection

Set InputRng = Application.InputBox("Original Range ", xTitleId, InputRng.Address, Type:=8)

Set ReplaceRng = Application.InputBox("Replace Range :", xTitleId, Type:=8)

Application.ScreenUpdating = False

For Each Rng In ReplaceRng.Columns(1).Cells

InputRng.Replace what:=Rng.Value, replacement:=Rng.Offset(0, 1).Value

Next

Application.ScreenUpdating = True

End Sub

With something that will replace the whole cell instead of just that word in the cell. So for example, If in the cell I have "ask how" (not including the quotations) and want to replace the whole cell with "How?" (not including the quotations), or a cell that has "but how" and replace the whole cell with How?. I would like the find and replace code to search for the word how and replace it with How?, but sometimes the cell might have extra words. Does that make sense?

 

[Micron]

Just test if the cell contains How (without question mark?). If it does, replace the contents with "How?"
Something like

For Each Rng In ReplaceRng.Columns(1).Cells
  If Instr(Rng,"How") > 0 Then Rng = "How?)
Next

I'm not sure about the first line I posted as much of your posted code doesn't seem to have much to do with what you're asking for.

 

[ap2015]

Thank you for your response.

Basically I have a spreadsheet that I get from someone. I have a list of words I want to replace with original spreadsheet. For example if it says music, I want to replace that word with music01. The code I gave allows me to pick a range (from the original spreadsheet) and then lets me pick another range (From a list that I created that on one column says music, then the next column says music01), and it will replace the words with the words from the 2nd column. But it just replaces the word not the whole text. So in this example, if the cell has -> I love music, it only replaces the music so the cell now says -> I love music01. What I want is for that whole cell to just say music01. I have a large list of things to replace, so running one word at a time isn't possible.

 

[Micron]

The IF logic I posted will replace the entire contents with the word you provide. If you've got a way to iterate over a list rather than providing one search word at a time, then feed that into Instr instead of just one word. Instr returns the position of the search word within a string. If the value is greater than one, the word is there. You then make the cell/range equal to what you want; you don't use the Replace function else only the search word will be replaced, as you've discovered.

Why do you have 2 set statements for the same object? - Set InputRng
The 2nd will over-ride the 1st, no?
You must not be using Option Explicit as xTitleId is an undeclared variable.
When you get the cell contents you want, you should deal with the errors you'll get if the input is canceled?

 

[ap2015]

I should have stated that I know nothing about code. Saw that code on the internet. Could you make the adjustments to the code I provided that would allow me to do this?

 

[Micron]

I have no idea what your sheet looks like but if I have this in B3:C8

TEST1	TEST
TEST2
TEST3
TEST4
TEST5
TEST6

and choose C3 as original range, then B3:B8 as replace range, this code

Sub MultiFindNReplace()
'Update 20140722
Dim Rng As Range, InputRng As Range, ReplaceRng As Range
Dim xTitleId As String

xTitleId = "KutoolsforExcel"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Original Range ", xTitleId, InputRng.Address, Type:=8)
Set ReplaceRng = Application.InputBox("Replace Range :", xTitleId, Type:=8)
Application.ScreenUpdating = False

For Each Rng In ReplaceRng.Columns(1).Cells
   'InputRng.Replace what:=Rng.Value, replacement:=Rng.Offset(0, 1).Value
   If InStr(Rng, InputRng) > 0 Then Rng = InputRng
Next

Application.ScreenUpdating = True

End Sub

gives me this

TEST	TEST
TEST
TEST
TEST
TEST
TEST

If I have the process backwards, you should be able to modify it.

I should have stated that I know nothing about code.

No time like the present for learning by experimenting?

 

[ap2015]

Thank you!

 

[Micron]

You're welcome. I'm much more experienced in Access vba and when playing with your code I was surprised to discover that in Excel, some things seem to be case sensitive by default. I could not find "test" or "Test" in "TEST1", but I could find "TEST" which I'm not used to because in Access, the default is case insensitive. So keep that in mind. If it can be an issue, wrapping the values inside of UCase function should help (then the search word and the word being searched will both be compared in upper case).

Also, if you cancel the input prompt you'll get an error. If you're the only user, you probably don't need to worry about it.
Maybe mark this one as solved when you're done?

 

[ap2015]

I keep getting this

 

[ap2015]

Also, so you or anyone else that can solve this problem and the case sensitive problem, I am willing to pay for their time.