Search and Replace columns using Case?

  • Thread starter Thread starter john_t_h
  • Start date Start date
J

john_t_h

I want to run a macro that will search through the rows of a column and
if the value in the cell = a certain value then replace it with
another. I also want to search another column and and if the value in
the cell = a certain value then insert another value in the next column
(same row)

while Column A does not = 0
search column B
Case
Cell = dog
then replace with Cat

Cell = Rat
then replace with Mouse

and so on for many many entries (about 300)

Cell = (empty cell)
then continue to next row

search column C
Case

Cell = spot
then insert into column D - 1

Cell = ralf
then insert into column D - 2

Cell = (empty cell)
then insert into column D - 0

and so on........

I don't know VBA very well so I'm not too sure how to write this, can
anyone help.

Thanks
John
 
try this. Modify to suit.
Sub replaceem()
For Each c In Selection
Select Case UCase(c)
Case "DOG": c.Value = "Cat"
Case "CAT": c.Value = "Mouse"
End Select
Next c
End Sub
 
hmmm I gave this a go but it doesn't seem to work. Here is my code.


Code:
--------------------

Columns("H:H").Select
For Each C In Selection
Select Case UCase(C)
Case "ARMY Transport Corps": C.Value = "RACT"
Case "ARMY Chaplains Department": C.Value = "RAACHd"
Case "ARMY Catering Corps": C.Value = "AACC"
Case "ARMY Electrical&Mech Engineers": C.Value = "RAEME"
Case "ARMY Engineers": C.Value = "RAE"
Case "ARMY Medical Corps": C.Value = "RAAMC"
Case "ARMY Pay Corps": C.Value = "RAAPC"
Case "ARMY Ordnance Corps": C.Value = "RAAOC"
Case "AARMY Nursing Corps": C.Value = "RAANC"
Case "ARMY Signals Corps": C.Value = "RA SIGS"
End Select
Next C

--------------------
 
It WAS tested and it did work. I used ucase in case some is not typed
EXACTLY.
Case "ARMY Transport Corps": C.Value = "RACT
Click to expand...
try
Case "ARMY TRANSPORT CORPS": C.Value = "RACT
 
Don

Thanks for the reply.

I didn't mean to imply the code was faulty. It just did not work fo
me. Sorry if I caused you offence.

I didn't not pick up on the need to have the string that is bein
searched for in uppercase. Once I changed that, the code works fine.

As the data I am working from does not have any variations I typed i
as it is listed. Just for my own info, how would I change this
Select Case UCase(C)* so it would pick up the exact text.

Thanks for your hel
 
I didn't take offense. People around here will tell you that I am hard to
offend. I just pointed out that it did work. I always use the upper case to
try and prevent errors. Just change to

select case(c)
or
select case c

and hope that users type EXACTLY as desired.
 
Back
Top