Macro writing help

[Steve Gort]

I am a macro newbie.

I have some survey data that has been loaded into an Excel
spreadsheet. For one of the survey questions the data is either yes or
no. I want to write a macro to give "yes" a score of "1" and "no" a
score of "0." The data is arranged in columns.

Thanks for any help.
Steve Gort

 

[JE McGimpsey]

I am a macro newbie.

I have some survey data that has been loaded into an Excel
spreadsheet. For one of the survey questions the data is either yes or
no. I want to write a macro to give "yes" a score of "1" and "no" a
score of "0." The data is arranged in columns.

If the data is in column A, you can put this in column B:

B1: =IF(A1="yes",1,0)

and copy down. Equivalently

B1: =--(A1="yes")

If instead you just want to count the Yes's:

B1: =COUNTIF(A:A,"yes")

 

[JE McGimpsey]

And if you really want to use a macro:

Public Sub test()
Dim rCell As Range
For Each rCell In Range("A1:A" & _
Range("A" & Rows.Count).End(xlUp).Row)
If rCell.Value = "yes" Then
rCell.Value = 1
ElseIf rCell.value = "no" Then
rCell.Value = 0
End If
Next rCell
End Sub