Thanks Peder,
I didn't see your responce prior to answering OssieMac. I copy that code in
as you gave it and it does seem to work ok with my extended Macro code. The
only problem now is the EXCELL.EXE is working hard and the CPU Usage bounces
everywhere until after a minute or two it hits 100% and freezes the program.
I assume because I still have a problem somewhere.
I hope someone can spot the problem from my previous post.
Thanks again for all your assistance.
--
Cheers
cliff18


" wrote:

Also try to use the .Cells property of the range, like this:

Private Sub Worksheet_Calculate()
Dim rngC As Range
Dim objI As Object.

Set rngC = Me.Range("M7:M20")
For Each objI In rngC.Cells
If objI.Value = "Yes" Then
objI.Value = objI.Value
End If
Next
End Sub

But, what are you actually trying to accomplish here? The line:
i.Value = i.Value
does nothing..

best regards
Peder Schmedling

On Jan 9, 8:33 am, OssieMac
wrote:
Hi Cliff,

I don't think that the actual code is the problem. You are not using c or i
as a constant or something somewhere else in your code are you? Try using
alternative variables and dimension them also.

Dim rngc As Range
Dim obji As Object

--
Regards,

OssieMac

"cliff18" wrote:
I had the following code in a VBA which, when I opened the Workbook showed
Error '13' - Type mismatch, I believe relating to the Set c =
Range("M7:M20") line. I exited the error and it would function ok.

Private Sub Worksheet_Calculate()
Set c = Range("M7:M20")
For Each i In c
If i.Value = "Yes" Then
i.Value = i.Value
End If
Next
End Sub

But now, the line in the code has changed itself back to

Set c = Range("M7")

which was the range we had in an earlier code, and obviously only works on
cell M7. I can change the "M7" to "M8" etc, but as soon as I try to change
the range back to ("M7:M20") as I need it, Excel and the VBA freeze.
Does anyone know how I may overcome this?
--
Cheers
cliff18