Why are there NO quotation marks in my variable. This is the reason why my if statement is not equal (FALSE).

[Jurg55]

Hi,

Can someone help me with this issue please. I'm searching for ages.
My code goes to 'else' when e = 15. At row 15, there is a number/text (C15) that needs to be equal to another number/text (L15). (6002559)
When the 'do while' hits e=15 the condition er=et is FALSE, so it goes to 'else' msgbox.
This is really weird because the cells in excel contains both number 6002559. So it should be TRUE
But when cell L15 becomes a variable, the quotation marks wil be removed by VBA. So, that means the conditions is indeed FALSE. VBA does what it needs to do but....
Why are the quotation marks gone? Something causes that number 6002559 wil be save as variable as 6002559 and not as "6002559". All the other data in column L does have quotations marks when these cells become a variable.

So like I said, the other rows will pass the condition er=et. Is this because it is not a number?
Then it is still strange because cell C15 becomes the value of variable 'et' as well and this value does contains the quotations marks and is also a number. see attached.

Let me know if you have any questions.

Here is the code and the images attached.

    Application.ErrorCheckingOptions.NumberAsText = False
'-----------------------------------

    Dim e As Integer
    e = 2
    

    
    Do While Worksheets("ZANRIC").Cells(e, 1).Value <> ""

    Dim er As Variant
    er = Worksheets("ZANRIC").Cells(e, 12).Value
    Dim et As Variant
    et = Worksheets("ZANRIC").Cells(e, 3).Value

    If er = et _
    Then

    Else

    MsgBox "ARTICLENUMBERS DO NOT MATCH ERROR"
    
    GoTo Forward01

    End If
    
    e = e + 1
    
    Loop

 

[Jurg55]

Images are not sharp. Sorry. Maybe this works.

 

[DanteAmor]

The cells will likely have the number as text.
For that you can declare the variables er and et as String or as Double:

Sub test()
    Application.ErrorCheckingOptions.NumberAsText = False
'-----------------------------------
    Dim e As Integer
    Dim er As Double
    Dim et As Double
    
    e = 2
    Do While Worksheets("ZANRIC").Cells(e, 1).Value <> ""
      er = Worksheets("ZANRIC").Cells(e, 12).Value
      et = Worksheets("ZANRIC").Cells(e, 3).Value
      If er = et Then
        MsgBox "Match"
      Else
        MsgBox "ARTICLENUMBERS DO NOT MATCH ERROR"
      End If
      e = e + 1
    Loop
End Sub

Or:

Sub test()
    Application.ErrorCheckingOptions.NumberAsText = False
'-----------------------------------
    Dim e As Integer
    Dim er As String
    Dim et As String
    
    e = 2
    Do While Worksheets("ZANRIC").Cells(e, 1).Value <> ""
      er = Worksheets("ZANRIC").Cells(e, 12).Value
      et = Worksheets("ZANRIC").Cells(e, 3).Value
      If er = et Then
        MsgBox "Match"
      Else
        MsgBox "ARTICLENUMBERS DO NOT MATCH ERROR"
      End If
      e = e + 1
    Loop
End Sub

In either of the two ways you will compare texts or numbers.

 

[Jurg55]

The cells will likely have the number as text.
For that you can declare the variables er and et as String or as Double:

Sub test()
    Application.ErrorCheckingOptions.NumberAsText = False
'-----------------------------------
    Dim e As Integer
    Dim er As Double
    Dim et As Double
   
    e = 2
    Do While Worksheets("ZANRIC").Cells(e, 1).Value <> ""
      er = Worksheets("ZANRIC").Cells(e, 12).Value
      et = Worksheets("ZANRIC").Cells(e, 3).Value
      If er = et Then
        MsgBox "Match"
      Else
        MsgBox "ARTICLENUMBERS DO NOT MATCH ERROR"
      End If
      e = e + 1
    Loop
End Sub

Or:

Sub test()
    Application.ErrorCheckingOptions.NumberAsText = False
'-----------------------------------
    Dim e As Integer
    Dim er As String
    Dim et As String
   
    e = 2
    Do While Worksheets("ZANRIC").Cells(e, 1).Value <> ""
      er = Worksheets("ZANRIC").Cells(e, 12).Value
      et = Worksheets("ZANRIC").Cells(e, 3).Value
      If er = et Then
        MsgBox "Match"
      Else
        MsgBox "ARTICLENUMBERS DO NOT MATCH ERROR"
      End If
      e = e + 1
    Loop
End Sub

In either of the two ways you will compare texts or numbers.

Oke it works, but I still don't get it.
Why is variant not working?
And why did 'et' contain quotation marks and 'er' not? 'et' was a variant variable as well.
So two exact the same cells and both are variant but one has quotation marks and the other does not have quotation marks.

Thanks

 

[RoryA]

So two exact the same cells

They are not the same. One contains a number stored as text, the other contains a true number.

 

[DanteAmor]

The Variant type does not define a value, it is as if you did not make the declaration.

That is why you must properly declare each variable.

With your images I can't see if it is text or number, apparently they look the same but surely the value in cell C15 is stored as text, that is why it appears with quotes.

Edit each cell by pressing F2 and then press enter. Go back to your macro and run the macro with e = 15 to check each value.

 

[Jurg55]

The Variant type does not define a value, it is as if you did not make the declaration.

That is why you must properly declare each variable.

With your images I can't see if it is text or number, apparently they look the same but surely the value in cell C15 is stored as text, that is why it appears with quotes.

Edit each cell by pressing F2 and then press enter. Go back to your macro and run the macro with e = 15 to check each value.

Cell L15 is also a number stored as text. I select both cells in the images. In the ribbon you can see it is a text cell.
Or does this not mean that it is a text cell?

 

[Jurg55]

The Variant type does not define a value, it is as if you did not make the declaration.

That is why you must properly declare each variable.

With your images I can't see if it is text or number, apparently they look the same but surely the value in cell C15 is stored as text, that is why it appears with quotes.

Edit each cell by pressing F2 and then press enter. Go back to your macro and run the macro with e = 15 to check each value.

Oke that is something new to me. If I edit the cell L15 with F2, enter it and run the macro again, the macro finishes that means the cells are equal. (and this is without declaration)
So, something changes when I edit the cell, but I don't know what changes.

 

[DanteAmor]

So, something changes when I edit the cell, but I don't know what changes.

When you edit the cell and press enter, then excel converts that value stored as text into number.

Look at my example, both numbers "look the same". But L2 has Text format and C2 has General format.

So the value in L2 is stored as text, therefore it is not equal to C2 because it is stored as a number.

 

[Jurg55]

When you edit the cell and press enter, then excel converts that value stored as text into number.

Look at my example, both numbers "look the same". But L2 has Text format and C2 has General format.

View attachment 115099

So the value in L2 is stored as text, therefore it is not equal to C2 because it is stored as a number.

Thank you but I knew this already.
As you see in my images both cells are text. These screenshots were made BEFORE the if-statement started. This didnt work, like how I did it in my first post.

But somehow excel only will activate the ‘text’ numberformat when I edit the cell like you said. I edited the cell AFTER the code finishes and run it again. And then it works. I tried this without declaration, I tested this with the original code like in my first post.

So in short:
-Cell L15 is already a text cell
-Code is not working when ran
-Edited cel L15
-Entered
-Cell L15 is still a text cell
-Code is workong when ran

As I already said, the numberformat was text in de first place but it is somehow activated in the background when edited.
Is this what happens here? And why does it work like this?

Thank you for helping!