Unable to correct VBA code

C

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?
 
O

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
 
G

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

cliff18 said:
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.
Click to expand...
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
Click to expand...
But now, the line in the code has changed itself back to
Click to expand...
Click to expand...
 
C

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

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


[email protected] said:
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
Click to expand...
 
D

Dave Peterson

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
 
C

cliff18

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 said:
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
Click to expand...
 
C

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.

--
Cheers
cliff18


cliff18 said:
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.
Click to expand...
 
C

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


cliff18 said:
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.
Click to expand...
 
D

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.
Click to expand...
 
M

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.
Click to expand...

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

MS Query 1
Code to Concat a row in loop 2
Worksheet_change event assistance 5
Using progress bar with existing loop 1
Cleaning up the syntax 9
How can I make this macro works 2
Change event for multiple rows 1
Conditional formatting puzzle 2

Top