find and replace in EXCEL Closed

Question

Hello, all

i have been suffering to  find a solution for a problem  which is :
I have three categories which I call region (Central, Northern and Southern) and actually there are eight governorates that comes under each region total of 24 districts.  What I am looking for is when someone enters for example C1 or C2 or C3 or C4 or C5 or C6 or C7 or C8 in cell A1it automatically enters Central in cell B1 and if enters N1 or N2 or N3 or N4 or N5 or N6 or N7 or N8 it reflects Northern and the same for the Northern.
Please if anyone knows how to solve this I would be really grateful.
Thanks in advance.




Configuration: Windows XP / Internet Explorer 8.0

venkat1926 · Answer

suppose sheet 1 is your sheet.
open vb editor (alt+F11)
click control+R
on the left "project" window opens
go to your file therein and righclick sheet1 and click view code
in the resulting window copy this event code

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range, mystring, j As Integer
If Target.Address <> "$A$1" Then Exit Sub
mystring = Array("N", "C", "S")
For j = 0 To 2
If Target = mystring(j) Then
Set r = Target.Offset(0, 1)
r = mystring(j) & 1
r.AutoFill Destination:=Range(r, r.Offset(7, 0)), Type:=xlFillDefault
End If
Next j
End Sub


now go to A1 type N or C or S see what happens in column B

talahjer · Answer

Dear Venkat 1926

I really appreciate you effort; however, what you gave me was not quite what I was looking for. Anyway if will write my question in another way.
My regions and Districts are as follow:

Northern:	          Sadah, Amran, Al-Jawf, Marib, Hajjah and Al-Hudaydah	
Central:             Sanaa, Dhamar, Ibb, Raymah, Al-Bayda, Al-Mahwit and Taiz
Southern:	          Aden, Abyan, Dhale, Lahij, Shabwah, Hadramaut and Al-Mahrah

In the H:H i write the details of some incidents which come like this:
On 19 Jan 11, Sadah Governorate, Al-Hada District, etc.............
In C:C comes the Region, according to the above-mentioned incident, C1 should be Northern, because if you see that Sadah Governorate comes under the Northern region.

Let's imagine that I wrote this incident in H2:
On 19 Jan 11, Aden Governorate, Kreator District, etc............. 
Then under C2, Northern should be written automatically.

i hope you understand me now.

thank you very much in advance.

venkat1926 · Answer

it is ok if you write  
"On 19 Jan 11, Aden Governorate, Kreator District, etc............. " 
in H2. 

the name of the governorate is Aden.   
how can the computer find out the name of the governarate in the text..  Like a human being it cannot search the text and guess the name of the governoarate. If the name of the governorate comes always the second word after comma in the text it is possible to write a macro (by the by Aden is in southern region and not northern region. 

suppose in another text it is written 
"governaorate of Abyan ............" 
the name of the goverenorate is not the second word after comma. and the computer will be confused.   

It is therefore better if the user use his discretion to choose the name of hte governaorate and enter it in H2.

clarify these points.  Remember computer is only a high speed idiot. better post a small extract of your data sheet with two or three texts.

venkat1926 · Answer

there is problem in nested ifs in excel 2003 and earlier. there is a limit of 7 nests.

talahjer · Answer

only 7. ok is there any other way to solve what i am looking for.

venkat1926 · Answer

some other tactics have to be used like vlookup, choose etc. depending upon the data base.  

on the other hand excel 2007 do not have these restrictions, I suppose

Find and replace in EXCEL

6 responses

Similar discussions

Subscribe To Our Newsletter!