You can get that type mismatch error if the cell contains an error (like #value!
or #n/a!).

Using the .text property is one way around that error.

So I'm guessing that you're converting formulas that evaluate to "Yes" to
values.



cliff18 wrote:

You blokes are good!
Every bit of that info improved it but Dave nailed it with stopping it
recalculating, so it no longer stresses the CPU.
The only problem I have left is when I open the application I get (below)

Run-time error '13'
Type Mismatch

I hit debug and it highlights the line in dave's code (below)

If i.Value = "Yes" Then

I can stop the debugger and close the VBA and it seems to work fine, but I
guess there is still something not quite right. I'm really pleased to get it
to work but am wondering if anyone can guide me through this last small
obsticale.
Again thanks everyone for your assistance!! I doubted we could have got it
this far.

--
Cheers
cliff18

"Dave Peterson" wrote:

I'm not sure what you're doing or why you're doing this, but each time you make
a change to one of those cells, you could be causing a recalculation.

And every recalculation will cause the event to fire again. So your code could
be running hundreds/thousands of times.

You may want to stop that recursion by using something like:

Option Explicit
Private Sub Worksheet_Calculate()

Dim c As Range
Dim i As Range

Set c = Range("M7:M20")

For Each i In c.Cells
If i.Value = "Yes" Then
Application.EnableEvents = False
i.Value = i.Value
Application.EnableEvents = True
End If
Next i

End Sub



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

--

Dave Peterson


--

Dave Peterson