Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to check the whole column at the same time?
Hi all,
Could anyone help me how to check the column content by different condition? If the content of the cell is "Father" then replace it to "1" in the same column. If the content of the other cell is "Mother" then replace it to "2" in same column. If the content of another cell is "Sister" then replace it to "3" in the same column. Besides, how can I implement this checking by using Marco? Thanks, Tlee |
#2
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to check the whole column at the same time?
The simplest option for one-off changes is to use search and replace.
Select the entire column and use the search-replace function, three times. -- Regards, Nigel "tlee" wrote in message ... Hi all, Could anyone help me how to check the column content by different condition? If the content of the cell is "Father" then replace it to "1" in the same column. If the content of the other cell is "Mother" then replace it to "2" in same column. If the content of another cell is "Sister" then replace it to "3" in the same column. Besides, how can I implement this checking by using Marco? Thanks, Tlee |
#3
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to check the whole column at the same time?
Many thanks for your help.
How about if the string searching? such as "4~8¢J" Thanks again! Tlee The simplest option for one-off changes is to use search and replace. Select the entire column and use the search-replace function, three times. -- Regards, Nigel "tlee" wrote in message ... Hi all, Could anyone help me how to check the column content by different condition? If the content of the cell is "Father" then replace it to "1" in the same column. If the content of the other cell is "Mother" then replace it to "2" in same column. If the content of another cell is "Sister" then replace it to "3" in the same column. Besides, how can I implement this checking by using Marco? Thanks, Tlee |
#5
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to check the whole column at the same time?
You do have to watch out for wild cards (* and ?) and the escape character.
If you want to replace an asterisk character, use ~* in the from string. If you want to replace a question mark, use ~? in the from string. If you want to replace a tilde (~), use ~~ in the from string. tlee wrote: Many thanks for your help. How about if the string searching? such as "4~8¢J" Thanks again! Tlee The simplest option for one-off changes is to use search and replace. Select the entire column and use the search-replace function, three times. -- Regards, Nigel "tlee" wrote in message ... Hi all, Could anyone help me how to check the column content by different condition? If the content of the cell is "Father" then replace it to "1" in the same column. If the content of the other cell is "Mother" then replace it to "2" in same column. If the content of another cell is "Sister" then replace it to "3" in the same column. Besides, how can I implement this checking by using Marco? Thanks, Tlee -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions,microsoft.public.excel
|
|||
|
|||
How to check the whole column at the same time?
Hi tlee,
try following code:- Sub tlee() Cells(1, 1).Select a = "father" b = "mother" c = "sister" While ActiveCell.Offset(1, 0).Value < "" Or ActiveCell.Value < "" If ActiveCell.Value = a Then ActiveCell.Value = 1 End If If ActiveCell.Value = b Then ActiveCell.Value = 2 End If If ActiveCell.Value = c Then ActiveCell.Value = 3 End If ActiveCell.Offset(1, 0).Select Wend End Sub 'assumption = data is there in column A -- Click on Yes, if it is useful. Thanks & Best Regards, Dilip Kumar Pandey MBA, BCA, B.Com(Hons.) New Delhi, India "Nigel" wrote: The simplest option for one-off changes is to use search and replace. Select the entire column and use the search-replace function, three times. -- Regards, Nigel "tlee" wrote in message ... Hi all, Could anyone help me how to check the column content by different condition? If the content of the cell is "Father" then replace it to "1" in the same column. If the content of the other cell is "Mother" then replace it to "2" in same column. If the content of another cell is "Sister" then replace it to "3" in the same column. Besides, how can I implement this checking by using Marco? Thanks, Tlee |
#7
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to check the whole column at the same time?
If you mean replace all instances of Father with 1; Mother with 2, Sister
with 3, try the following: Option Base 1 Sub ChangeStrToNum() Dim FindArr, ReplArr FindArr = Array("Father", "Mother", "Sister") ReplArr = Array(1, 2, 3) 'To undo 'ReplArr = Array("Father", "Mother", "Sister") 'FindArr = Array(1, 2, 3) Range("yourRange").Activate 'change If UBound(FindArr) = UBound(ReplArr) Then For i = 1 To UBound(FindArr) Selection.Replace What:=FindArr(i), Replacement:=ReplArr(i), LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Next i Else MsgBox "Your arrays contain different numbers of elements" End If End Sub -- Steve "tlee" wrote in message ... Hi all, Could anyone help me how to check the column content by different condition? If the content of the cell is "Father" then replace it to "1" in the same column. If the content of the other cell is "Mother" then replace it to "2" in same column. If the content of another cell is "Sister" then replace it to "3" in the same column. Besides, how can I implement this checking by using Marco? Thanks, Tlee |
#8
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to check the whole column at the same time?
Please limit your question to one newsgroup
-- Steve "tlee" wrote in message ... Hi all, Could anyone help me how to check the column content by different condition? If the content of the cell is "Father" then replace it to "1" in the same column. If the content of the other cell is "Mother" then replace it to "2" in same column. If the content of another cell is "Sister" then replace it to "3" in the same column. Besides, how can I implement this checking by using Marco? Thanks, Tlee |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Check if a time is past a certain time | Excel Discussion (Misc queries) | |||
Time check | Excel Discussion (Misc queries) | |||
How do I set up a column in Excel as a check mark column? | Excel Discussion (Misc queries) | |||
how to check for recurrence of same names in column 1 and column | Excel Discussion (Misc queries) | |||
How do I Make a column in excel a check box column | Excel Discussion (Misc queries) |