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

 

[DanteAmor]

I don't know why your 2 values are text, maybe one of the 2 was copied from a web page and when you pasted it it has some special character that cannot be seen. Anyway, you will have to solve it as I explained in post #3.
The data should be clean before running any macro, as macros usually do not fix all problems with the data.

 

[RoryA]

If the cell was populated and then the format set to text afterwards, the content would still be a number.

 

[Jurg55]

I don't know why your 2 values are text, maybe one of the 2 was copied from a web page and when you pasted it it has some special character that cannot be seen. Anyway, you will have to solve it as I explained in post #3.
The data should be clean before running any macro, as macros usually do not fix all problems with the data.

Yes roryA is right!
I copied the cell and set it to text afterwards in the code and then the if-statement comes in. It doesn’t work like this unless I edit with F2 like you said.
Anyways, it is always better to declare each variable, right?
Is it also necessary to declare a variable when I have a code-line that is not equal to an empty cell? Or is it only the case when I match numbers and text?
Like the following example that is also in my code:

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

should I change this to the following?

Dim sd As String
sd = Worksheets("ZANRIC").Cells(e, 1).Value
Do While sd <> ""

Thanks again

 

[DanteAmor]

I will try to answer all your questions.

Anyways, it is always better to declare each variable, right?

That's right, the best practice is to declare all variables appropriately, in fact at the beginning of all your code you should use the Option Explicit statement, it verifies that all variables have a declaration.

As I mentioned, you should try to fix your data, since macros are unlikely to repair all data problems, unless you verify each case, but you will have to consider each case.

In all cases you must declare the variables, if you do not declare the variable, VBA declares it by default as a Variant type; and as we already saw, the Variant type did not help in this case. The appropriate thing is to have the variable as String.

Is it also necessary to declare a variable when I have a code-line that is not equal to an empty cell?

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

should I change this to the following?

Dim sd As String
sd = Worksheets("ZANRIC").Cells(e, 1).Value
Do While sd <> ""

In that case, the declaration of a variable is not necessary.

Or is it only the case when I match numbers and text?

As we already saw in your example in the original post, it is necessary to declare the variable of type String.

 

[Jurg55]

I will try to answer all your questions.

That's right, the best practice is to declare all variables appropriately, in fact at the beginning of all your code you should use the Option Explicit statement, it verifies that all variables have a declaration.

As I mentioned, you should try to fix your data, since macros are unlikely to repair all data problems, unless you verify each case, but you will have to consider each case.

In all cases you must declare the variables, if you do not declare the variable, VBA declares it by default as a Variant type; and as we already saw, the Variant type did not help in this case. The appropriate thing is to have the variable as String.


In that case, the declaration of a variable is not necessary.


As we already saw in your example in the original post, it is necessary to declare the variable of type String.

Thank you!
You helped me alot!