Unable to correct VBA code

[cliff18]

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?

 

[OssieMac]

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

 

[got.sp4m]

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

Hi Cliff,

I don't think that the actual code is the problem. You are not using c ori
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

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

 

[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!

 

[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

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

 

[cliff18]

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

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.

 

[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.

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.

 

[Max]

Dave, your guess is correct, OP's intent was to monitor a bank of formulas
and freeze it once it evaluates to "Yes".

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

could you kindly illustrate how the above would look like,
when applied to the OP's sub here? Thanks
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik
---

 

[Dave Peterson]

The OP showed how in an earlier post:

If i.Text = "Yes" Then

 

[Max]

Thanks, I missed that.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik