Code/Formula to Evaluate

[Dave Y]

Hello,

I have a spreadsheet that is set up as follows:

BranchNum TestNum IP RespondYN Result
01 01 172.XX.XX.XX Responding

What I need to do is to have a value of either 1 or 0 put
in to the Result column according to what is in the
RespondYN column. For example; as above shows; if the
column shows "Responding" then the value of 1 would be
placed into the Result column; if the RespondYN column
shows "Down" then the value would be a 0. I have created
2 named ranges, RespondTF, and ResultTF. I wrote the
following code in the VBA Editor but it does not work
properly:

Sub ResultTF()
For Each Cell In Range("RESPONDTF")
If Cell.Value = "RESPONDING" Then
Cell.Range("ResultTF") = 1
Else
Cell.Range("ResultTF") = 0
End If
Next Cell
End Sub

The code does produce a result of 1 or 0 but it is
showing up in the wrong place on the worksheet. I would
appreciate some help with this code or even a suggestion
of a formula that I could use on the worksheet. I don't
have much experience with VBA or formulas but I am
trying. Any help will be greatly appreciated. Thank you.

Dave

 

[Frank Kabel]

Hi
try
Sub ResultTF()
For Each Cell In Range("RESPONDTF")
If Cell.Value = "RESPONDING" Then
Cell.offset(0,1).value = 1
Else
Cell.offset(0,1).value = 0
End If
Next Cell
End Sub

 

[Guest]

Hi Frank,

Your suggestion worked perfectly. Thank you very much!

Dave

 

[Dana DeLouis]

Just a different version of Frank's excellent idea.

Sub ResultTF()
Dim Cell As Range
For Each Cell In Range("RESPONDTF")
Cell(1, 2) = -(Cell = "RESPONDING")
Next Cell
End Sub