Worksheet_Change code question

[Smokin Joe]

I have successfully written a bit of code which clears columns T:W if Col T reads "CANCELLED".

The code read as follows:
' Check for "CANCELLED" in Row T
For a = 5 To 150
If ws1.Range("T" & a).Value = "CANCELLED" Then
' Clear bet references
ws1.Range("T" & a & ":W" & a).ClearContents
End If
Next a

I now want to add an extra condition by saying that if the above is true OR if Col AE reads "F" (this looks up to see if the bet has been fully matched) then clear Cols T:W.

The code reads as follows:
' Check for "CANCELLED" in Row T OR "F" in Row AE
For a = 5 To 150
If ws1.Range("T" & a).Value = "CANCELLED" Or ws1.Range("AE" & a).Value = "F" Then
' Clear bet references
ws1.Range("T" & a & ":W" & a).ClearContents
End If
Next a

However, that code gives me a Run-time Error 13: Type Mismatch.

I have ran the code in another spreadsheet unrelated to BA and it works exactly as planned.

Any ideas as to why does it not work within the above code, is it something to do with a Worksheet Change event?

[KevinTHFC]

I am guessing that the "F" in AE is actually a boolean rather than a string and that is why you are getting the type mismatch error.

[Smokin Joe]

I am guessing that the "F" in AE is actually a boolean rather than a string and that is why you are getting the type mismatch error.

The "F" is generated from a lookup.

Kevin, how do I correct my code then?

Cheers

[KevinTHFC]

I assume that the error is coming on the

Code: Select all

If ws1.Range("T" & a).Value = "CANCELLED" Or ws1.Range("AE" & a).Value = "F" Then


If so what are the values of the two elements of the IF statement at the time of the error.

Kev

[Smokin Joe]

I assume that the error is coming on the

Code: Select all

If ws1.Range("T" & a).Value = "CANCELLED" Or ws1.Range("AE" & a).Value = "F" Then


If so what are the values of the two elements of the IF statement at the time of the error.

Kev

Kev, yes that is correct. The error is happening at that code.

How do I know the values at the time of breaking? Can I type something into the immediate pane to determine the values (as I have now way of knowing what row it stopped at)?

Cheers

[KevinTHFC]

If you type a ? followed by the variable it will display the value.

For example if you typed

Code: Select all

?Activecell.Row

The current row number will be displayed

if you typed

Code: Select all

?a

The value of a will be displayed

So you should be able to enter

Code: Select all

?ws1.Range("AE" & a).Value

to get the value that is causing the problem.

Kev

[Smokin Joe]

I had a rough idea how the immediate pane worked, so thanks for the clarifcation.

I ran the code, and it stopped at the same line.

I then typed:
?ws1.Range("AE" & a).Value in the immediate pane

But I got an error message "Run-time error 424". Object required

I am none the wiser.

[Smokin Joe]

On further testing, the problem seems to be that the F is generated by a lookup formula, ie AE6 is not equal to "F" directly, but rather as a result of the lookup formula which is:
=VLOOKUP(T6,MyBets!A$2:E$100,5,FALSE)

So, is it possible to use the result of a lookup formula in the fashion that I am attempting to do it.

Or alternately is there a different way of determing whether my BACK-F5 bet has been matched in full or not, as that is what I am trying to do with this?

I can't use the obvious solution of seeing if my liability = my stake, as the stake is calculated by a Kelly formula and so it changes all the time, so even if my bet was matched in full the liability almost certainly won't equal the newly calculated stake.

[Smokin Joe]

At the risk of talking to myself, I have now (categorically) discovered what the problem is, and I was talking rubbish in the previous post .

Col AE has lookups and some cells have #N/A as the solution (ie the lookup isn't matched). This is then what causes the error in my code.

The code doesn't know what to do if ws1.Range("AE" & a).Value = #N/A

So I'm going to have to thing of an alternative solution, as the lookup doesn't have the desired effect.

[KevinTHFC]

Hi SJ,

Been out all afternoon. Yep the N/A will cause the problem

Kev