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

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

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

Thanks for your quick reply.
After working with what you asked, I believe I have found the problem to be
in the Macro code that I have set up to reload the formula, (below).

Sub Put_In_M1()
Range("M7:M20").FormulaR1C1 = _

"=IF(OR(AND(RC[5]=""H"",(RC[-9]<=((RC[7]-RC[8])/2+RC[8])))),""Yes"",IF(OR(AND(RC[5]=""I"",(RC[-9]=((RC[8]-RC[7])/2+RC[7])))),""Yes"",IF(OR(AND(RC[5]=""H"",(RC[-9]((RC[7]-RC[8])/2+RC[8])))),""Wait"",IF(OR(AND(RC[5]=""I"",(RC[-9]<((RC[8]-RC[7])/2+RC[7])))),""Wait"",""""))))"
End Sub

I took this back to the original Macro code (below) and then changed the
range from "M7" to "M7:M20" in the other code box, and it all works fine.

Sub Put_In_M1()
Range("M7:M20").FormulaR1C1 = _
"=IF(AND(RC[5]=""H"",(RC[-9]<=((RC[7]-RC[8])/2+RC[8]))),""Yes"","""")"
End Sub

So it appears I caused the problem when I changed the code to work as I
wanted it. The top code did infact work until I tried to extend the range
from "M7" to "M7:M20" as in my original question.
I'll be honest and say I'm out of my depth here, and although your first
suggestion of using alternative variables may have worked, I am not sure how
the coding should be installed.
If you believe it may still be the solution, would it possible for you to
assemble the code for me? Sorry to have to ask.
Thanks for your assistance and patients!

--
Cheers
cliff18


"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

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

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

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

To anyone assisting or following this thread, it may help if I explain I
started it in
'Maintaining a cells value once a target is achieved' in Excell Worksheet
Functions.

--
Cheers
cliff18


"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

Conclusion:
With all the help, and some research, all now seems to be working perfectly.
A small adjustment to the code as below has fixed the error.

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.Text = "Yes" Then
Application.EnableEvents = False
i.Value = i.Value
Application.EnableEvents = True
End If
Next i

End Sub

I hope this assists others.
Thankyou all.

--
Cheers
cliff18


"cliff18" wrote:

To anyone assisting or following this thread, it may help if I explain I
started it in
'Maintaining a cells value once a target is achieved' in Excell Worksheet
Functions.

--
Cheers
cliff18


"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

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