VBA Test Fails, but Excel formulas are correct

[JeffGrant]

Hi All,

May I firstly appologise for the tables, xl2bb keeps hanging under windows 11.

In this table, the formula in B7 is =isnumber(A7), and the result is TRUE.

1. Hattusa (3)	FALSE
W: 59.0kgF: x31145	FALSE
J: Brad Pengelly	FALSE
T: H Page	FALSE
1.952.602.45	FALSE
2.5​	TRUE
	FALSE
2. Cool 'N' Ready (4)	FALSE
W: 57.5kgF: 51	FALSE
J: Noel Callow	FALSE
T: A J & T Edmonds	FALSE

In this table, the formula is D28 is =COUNTIF(A1:A200,"EW"), which returns 0, which is also correct.

	See if EW exists in A:A
	0

The sheet functions return the correct values.

However, on a regular basis this VBA code simply fails and the code moves to the next line to complete the SUB:

  ElseIf IsNumeric(.Range("A7").Value) = True And .Range("D28").Value = 0 Then

The sub in its entirty is:

Sub NewFormulas()
Application.ScreenUpdating = False

               With Sheet1

                    'Has Win Price, EW Price and EW offer
                     If IsNumeric(.Range("A7").Value) = True And IsNumeric(.Range("A8").Value) = True And .Range("A9").Text = "EW" Then
                     ColNum = 9
                      'Determine which formula is being used
                      .Range("e34").Value = 9
                      ColVar = "N"
                      Call PasteFormulasSheet1
                     'Has Win Price and Place Price but no EW offer
                     
                     ElseIf IsNumeric(.Range("A7").Value) = True And IsNumeric(.Range("A8").Value) = True And .Range("D28").Value = 0 Then
                     ColNum = 8
                      'Determine which formula is being used
                      .Range("e34").Value = 8
                      ColVar = "N"
                      Call PasteFormulasSheet1
 
                      'Has Win Price & MD Only, No EW offer.
                     ElseIf IsNumeric(.Range("A6").Value) = True And .Range("A7").Text = "MD" And .Range("D28").Value = 0 Then
                     ColNum = 7
                      'Determine which formula is being used
                      .Range("e34").Value = 7
                      ColVar = "Y"
                      Call PasteFormulasSheet1
 
                     'Has Win Price Only, No Place Price, No EW offer
                     ElseIf IsNumeric(.Range("A7").Value) = True And .Range("D28").Value = 0 Then
                     ColNum = 7
                      'Determine which formula is being used
                      .Range("e34").Value = 6
                      ColVar = "N"
                      Call PasteFormulasSheet1

                     'MD Only, No EW offer. Sometimes MD can be on row 6 or row 7
                     ElseIf (.Range("A6").Text = "MD" Or .Range("A7").Text = "MD") And .Range("D28").Value = 0 Then
                     ColNum = 7
                      'Determine which formula is being used
                      .Range("e34").Value = 5
                      ColVar = "N"
                      Call PasteFormulasSheet1

                     Else
                       MsgBox "Check formula for data paisted from SportBet" & vbNewLine & "Try pasting the data again."
                      'Determine which formula is being used
                      .Range("e34").Value = 0
                      Sheet1.Select
                      Range("A1").Select
                    End If
                    
                End With
Application.ScreenUpdating = True
End Sub

I have tried using Application.Wait (Now + TimeValue("0:00:02")), to slow the code down so that the sheet formulas can evaluate properly, but that does not seem to have made any difference.

I have also got:

'wait until all calulcation are complete before starting the testing

Do
    DoEvents
Loop While Not Application.CalculationState = xlDone

in the Sub that calls this sub. I have tried to slow the code. But at this point I dont know if it is is code speed issue or not.

I have aslo tried substituting a space for CHAR(160) in case CHAR(160) being copied from the website was causing the problem, but that didnt work either.

Because the VBA code is not being evaluated properly, the wrong ColNum is being passed to an other sub. Generaly speaking when this happens, which is far too regularly, the code fails all of the Elseif statements and executes the Else statement.

The data that gets pasted into Column A is copied from a website. The silly thing is, that if I paste the data again, without copying it again, all of the above tests work fine and the code does what it supposed to do.

To copy the data, all I do is highlight the code usinga mouse, right click and copy.

The data is pasted using
'Paste Data

    Range("$A$1").Select
    ActiveSheet.PasteSpecial Format:="HTML", Link:=False, DisplayAsIcon:=False
    Application.CutCopyMode = False ' clear the exit cut/copy mode

Any suggestions would be greatly appreciated.

 

[DanteAmor]

ElseIf IsNumeric(.Range("A7").Value) = True And .Range("D28").Value = 0 Then

That condition is ok
But your code is getting into this condition:
ElseIf IsNumeric(.Range("A7").Value) = True And IsNumeric(.Range("A8").Value) = True And .Range("D28").Value = 0 Then

Change this line:

ElseIf IsNumeric(.Range("A7").Value) = True And IsNumeric(.Range("A8").Value) = True And .Range("D28").Value = 0 Then

For this line:

  ElseIf IsNumeric(.Range("A7").Value) = True And IsNumeric(.Range("A8").Value) = True And _
    .Range("A8").Value <> "" And .Range("D28").Value = 0 Then

I have tried using Application.Wait (Now + TimeValue("0:00:02"))

That is not necessary.

 

[JeffGrant]

Hi Dante,

thanks for that.

what do you mean by "your code is getting into this condition".

I guess I should also make the same change the other Elseif conditions?

Do you think that excel is pasting a blank space into the null cells?

cheers

 

[DanteAmor]

what do you mean by "your code is getting into this condition".

Your code has several conditions, if a validation is met, then "enter that condition", that's what I mean.

Do you think that excel is pasting a blank space into the null cells?

VBA is considering a blank cell as numeric, that's why it "enters" this condition:

ElseIf IsNumeric(.Range("A7").Value) = True And IsNumeric(.Range("A8").Value) = True And .Range("D28").Value = 0 Then

To avoid the above you must change the condition to this:

 ElseIf IsNumeric(.Range("A7").Value) = True And IsNumeric(.Range("A8").Value) = True And _
    .Range("A8").Value <> "" And .Range("D28").Value = 0 Then

By preventing it from "entering" the above, then the code will enter this:

ElseIf IsNumeric(.Range("A7").Value) = True And .Range("D28").Value = 0 Then

I guess I should also make the same change the other Elseif conditions?

You must review what you need and if necessary, then do it with each ElseIf.

 

[JeffGrant]

Thank you Dante for the explanation. I will apply the code as you have indicated and see how we go.

 

[JeffGrant]

Hi Dante, your solution most definitly fixed the issue that I raised. Thank you very very much.

As you saw though, there are several Elseif conditions and another elseif raised a failed condition where it appears like all of the criteria is met, but the VBA code treats the elseif as if the criteria has not been met. Would like to have a go at it, or woud you prefer me to raise another thread?

This is the elseif causing the issue

ElseIf IsNumeric(.Range("A6").Value) = True And .Range("A7").Text = "MD" And .Range("D28").Value = 0 Then
where
A6 = 17.5 - is a numeric
A7 =MD i s a text value
D28 = 0, the formula is =COUNTIF(A1:A200,"EW"), even though it looks like a text 0, it is in fact a numeric 0.

But VBA treat this as though one of the creiteria has failed and so the code does not enter the elseif.

			Horse
1. Al Ranchero (9)			1. Al Ranchero (9)
W: 57.5kgF: 08x			J: Todd Pannell
J: L Overall			4​
T: Aaron Bain
17.5​			W: 55.5kgF: 5544x0
MD			T: D R Jolly
2. Big Man At Night (3)
W: 57.5kgF: -
J: Todd Pannell
T: Michael Hickmott
5.9​
MD
4. Glacier Grey (4)
W: 57.5kgF: 4075x
J: Barend Vorster
T: Aaron Bain
4​
MD
5. Moon Boot (5)
W: 57.5kgF: -
J: Callan Murray
T: Cody Reardon
67​			See if EW exists in A:A
MD			0

 

[DanteAmor]

Would like to have a go at it, or woud you prefer me to raise another thread?

We continue on this thread.
Put all your code back.
But this time put your example data with XL2BB tool, so that I can copy and test with your same data.

NOTE XL2BB:
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in
Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[JeffGrant]

Hi Dante, as I said at the very beginning of this thread, the Xl2Bb fails under Win 11 on my machine. I have tried reinstalling several times and the result is the same.

I can niot supply a mini-sheet. I will contact the administrator and see if there is anything that can be done.

cheers & thanks

 

[Dan_W]

Hi Dante, as I said at the very beginning of this thread, the Xl2Bb fails under Win 11 on my machine. I have tried reinstalling several times and the result is the same.

I can niot supply a mini-sheet. I will contact the administrator and see if there is anything that can be done.

cheers & thanks

Sorry, can I just ask what you mean when you say that XL2BB fails under Windows 11 on your machine. Does it present an error message or does it not just not do anything, etc?

 

[smozgur]

Sorry, can I just ask what you mean when you say that XL2BB fails under Windows 11 on your machine. Does it present an error message or does it not just not do anything, etc?

Thanks for the heads-up and the question, @Dan_W.

@JeffGrant - As an additional question - Is the add-in failing in one particular workbook OR any workbook?

Could you please unselect all options except the Cell formulas in the Additional Info section on the XL2BB ribbon tab and retry?