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

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

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

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

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

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

It makes no difference what string you use with search and replace.

If you cannot key the value then copy one cells content, open the search and
replace dialog and paste the value into the search for box, then enter the
replacement string into the with box.


--

Regards,
Nigel




"tlee" wrote in message
...
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

Hi Dilip,

Many thanks for your help. Let me test it.

tlee


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

Hi Steve,

Thanks for your help, let me try.

thanks
Tlee


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

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