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]

I can niot supply a mini-sheet.

You could upload a copy of your file to a free site such www.dropbox.com or google drive. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

 

[JeffGrant]

Hi Dan,

sorry for the delay, looks like the Xl2Bb addin just hangs in a particulare workbook. Seems to work fine in other workbooks.

 

[smozgur]

Hi Dan,

sorry for the delay, looks like the Xl2Bb addin just hangs in a particulare workbook. Seems to work fine in other workbooks.

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

The possible reason could be a named range. For example, we had a unique case that a whole column was selected and named, and XL2BB found it unusual, so didn’t know what to do. We had a few similar unique cases especially related to named ranges. So, unselecting some options as I explained might help posting the range in that workbook.