New to this world, so please feel free to patronise!

I am trying to asign a value of either 1 or 2 depending of th
condition of Yes or No, in two colums next to each other.

This is so that I can add up the values and determine whether I hav
all yes's.

This is what I have written so far:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Long

r = 9

Do Until IsEmpty(Cells(r, 12))

If Cells(r, 12) = "Yes" Then
Cells(r, 13) = 1

ElseIf Cells(r, 12) = "No" Then
Cells(r, 13) = 2
End If
r = r + 1
Loop
End Sub


The issue is that its fine for the first row, slow for the second, an
then locks up for the third.

There is something fundemental that I am missing, if you could point m
in the right direction, I would appreciate it.

Thanks

--
pjjclar
-----------------------------------------------------------------------
pjjclark's Profile: http://www.excelforum.com/member.php...fo&userid=2918
View this thread: http://www.excelforum.com/showthread.php?threadid=48908

pjjclark wrote:
New to this world, so please feel free to patronise!

I am trying to asign a value of either 1 or 2 depending of the
condition of Yes or No, in two colums next to each other.

This is so that I can add up the values and determine whether I have
all yes's.

This is what I have written so far:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Long

r = 9

Do Until IsEmpty(Cells(r, 12))

If Cells(r, 12) = "Yes" Then
Cells(r, 13) = 1

ElseIf Cells(r, 12) = "No" Then
Cells(r, 13) = 2
End If
r = r + 1
Loop
End Sub


The issue is that its fine for the first row, slow for the second, and
then locks up for the third.

There is something fundemental that I am missing, if you could point me
in the right direction, I would appreciate it.

Thanks.

--------------------------------------

I don't recognize the problem that you asked about, but I know the solution to
the problem that you *didn't* ask about!

Have you considered dumping the whole VBA approach and just using something of
the sort:

[ ] = countif(A1:A999, "Yes")

Good luck...

Bill

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Long

On Error Goto ws_exit
Application.EnableEvents = False

r = 9

Do Until IsEmpty(Cells(r, 12))

If Cells(r, 12) = "Yes" Then
Cells(r, 13) = 1

ElseIf Cells(r, 12) = "No" Then
Cells(r, 13) = 2
End If
r = r + 1
Loop
ws_exit:
Application.EnableEvents = True
End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"pjjclark" wrote in
message ...

New to this world, so please feel free to patronise!

I am trying to asign a value of either 1 or 2 depending of the
condition of Yes or No, in two colums next to each other.

This is so that I can add up the values and determine whether I have
all yes's.

This is what I have written so far:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Long

r = 9

Do Until IsEmpty(Cells(r, 12))

If Cells(r, 12) = "Yes" Then
Cells(r, 13) = 1

ElseIf Cells(r, 12) = "No" Then
Cells(r, 13) = 2
End If
r = r + 1
Loop
End Sub


The issue is that its fine for the first row, slow for the second, and
then locks up for the third.

There is something fundemental that I am missing, if you could point me
in the right direction, I would appreciate it.

Thanks.


--
pjjclark
------------------------------------------------------------------------
pjjclark's Profile:
http://www.excelforum.com/member.php...o&userid=29180
View this thread: http://www.excelforum.com/showthread...hreadid=489087

pjjclark,

No need to loop:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lYes As Long
Dim lNo As Long

If Target.Cells(1).Column < 12 Then Exit Sub
lYes = Application.CountIf(Range("L:L"), "Yes")
lNo = Application.CountIf(Range("L:L"), "No")
MsgBox "There are " & lYes & " ""Yes"" and " _
& lNo & " ""No"" values"

End Sub

Though I'm not sure that the change event is the best place for this....

HTH,
Bernie
MS Excel MVP


"pjjclark" wrote in message
...

New to this world, so please feel free to patronise!

I am trying to asign a value of either 1 or 2 depending of the
condition of Yes or No, in two colums next to each other.

This is so that I can add up the values and determine whether I have
all yes's.

This is what I have written so far:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Long

r = 9

Do Until IsEmpty(Cells(r, 12))

If Cells(r, 12) = "Yes" Then
Cells(r, 13) = 1

ElseIf Cells(r, 12) = "No" Then
Cells(r, 13) = 2
End If
r = r + 1
Loop
End Sub


The issue is that its fine for the first row, slow for the second, and
then locks up for the third.

There is something fundemental that I am missing, if you could point me
in the right direction, I would appreciate it.

Thanks.


--
pjjclark
------------------------------------------------------------------------
pjjclark's Profile: http://www.excelforum.com/member.php...o&userid=29180
View this thread: http://www.excelforum.com/showthread...hreadid=489087

Another Option


Sub WorksheetC()
Dim r As Long
Dim cellVal As Boolean

r = 1
cellVal = True

Do Until IsEmpty(Cells(r, 1)) Or cellVal = False
Cells(r, 1).Select
cellVal = (Cells(r, 1) = "Yes")
r = r + 1
Loop
If cellVal = False Then MsgBox "Not all Yes!"
End Sub

Brian



"pjjclark" wrote in
message ...

New to this world, so please feel free to patronise!

I am trying to asign a value of either 1 or 2 depending of the
condition of Yes or No, in two colums next to each other.

This is so that I can add up the values and determine whether I have
all yes's.

This is what I have written so far:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Long

r = 9

Do Until IsEmpty(Cells(r, 12))

If Cells(r, 12) = "Yes" Then
Cells(r, 13) = 1

ElseIf Cells(r, 12) = "No" Then
Cells(r, 13) = 2
End If
r = r + 1
Loop
End Sub


The issue is that its fine for the first row, slow for the second, and
then locks up for the third.

There is something fundemental that I am missing, if you could point me
in the right direction, I would appreciate it.

Thanks.


--
pjjclark
------------------------------------------------------------------------
pjjclark's Profile:
http://www.excelforum.com/member.php...o&userid=29180
View this thread: http://www.excelforum.com/showthread...hreadid=489087