Excel change value

[Jason Huang]

Hi,

In the Excel 2000, I have a Worksheet like this

1 1 John
2 0 Mary
3 1 Tom
4 0 Jane
5 0 Helen

How do I do to let the data change to

1 M John
2 F Mary
3 M Tom
4 F Jane
5 F Helen

Thanks for help.

Jason

 

[NateBuckley]

public sub test()

dim sze as Long
sze = Sheets("Data").Cells(rows.count, 1).End(xlUp).row
dim i as long
for i = 1 to sze
if sheets("Data").Cells(i, 1).value = 1 then
sheets("Data").cells(i, 1).Value = "Male"
else
sheets("Data").Cells(i, 1).Value = "Female"
end if
next i
end sub

This should do the trick, copy and paste the code into a module. This checks
a sheet called Data, then firstly assignes the variable sze with the amount
of rows in the sheet.

Then proceeds to go through each row checking Column A (which contains
either 0 or 1) and makes the neccessary change.

Haven't had time to test this, but should work.

Hope that helps!

 

[Rick Rothstein \(MVP - VB\)]

Select the column with the 1's and 0's in it and click on Edit/Replace in
Excel's menu bar. Put 1 in the "Find what" field and M in the "Replace with"
field and click on the Replace All button; then do the same for 0 and F.

Rick