Find within a limited range

[s_ojha]

1. I have 5000 rows record in one column . I want that my FIND & REPLACE to work in the specified range only . When I try to find or replace someting it searches all the worksheet . Always selecting the range and then do the FIND & REPLACE is tireless . Please tell me a way where I can fix the range and when I find records it searches in the specified range only .

Next Queston .
2. I have 1000 row records . For eg. suppose in Column C one row contains Diploma, the other row Overmanship and other row "another word" . I want a formula which could recognise the word "Diploma" or "Overmanship" or another word and put marks as 4 for Diploma, 5 for Overmanship and 6 for any other word in next adjoining column .

Can any body please help .


One question which is out way is how can I delete my post, the answer of which has already been received .

 

[headtoadie]

1) by default the search & replace function works on the whole spreadsheet. You have to highlight a specific area if that is the only area you want impacted. However, since you are limiting your search to a single column you can just click the the column label and highlight the entire column, there is no need to only select the used range.

An alternative is to write a macro that will do the highlighting and search & replace automatically. If you have never written one before the macro recorder is a good place to start. It can be found on the menu at Tools->Macro->Record New Macro...Basically after you start the recording you go through the process that you want to macro to emulate.


2) As for the other item you can use IF statements to evaluate the words and return the value desired. This example assumes that the word to be evaluated is in cell C3

=IF(C3="Diploma",4,IF(C3="Overmanship",3,IF(C3="Another word",2,1)))


HT

 

[s_ojha]

Thanks sir, i will try it and reply.

 

[s_ojha]

Sir,
I have tried the macro, but it does not fully work . In the macro while recording when i open the find window, there the macro could not be stopped . Could you please write the macro and send it to me . I want to repeat here that suppose I select the whole column the search will search many names and I want to search in limited range only .

The second formulla has worked wonderfully .

Please also tell me about how to delete a post .