[RESOLVED] When is a String not a string?

[MartinLiss]

I'm using Excel 2019 and I have a workbook where In Sheet1 column BF is formatted as Text and in another sheet column M is also formatted as Text.

I'm trying to compare what's in BF with what's in M by way of this line.

If ws1.Cells(lngRow, "BF") = .Cells(lngRowD, "M") Then

Putting a breakpoint on that line in most of the cases shows that (for example) BF is "123" and M is "123" and so I get a match. In a few cases however BF will be "159" but M will be 159 and and since they are not the same there's no match.

It's a fairly long process already because there are 7000 rows in BF and 123 rows in M and my process is to step through BF one by one and for each value step through M one by one. Note that I tried speeding up the process by doing a Find in column M but by actual timing, that way is slower.

I tried changing the line to

If ws1.Cells(lngRow, "BF") = Cstr(.Cells(lngRowD, "M")) Then

but that made it too slow.

So I have two questions:

1. How is it possible that a cell that is formatted as Text is read (incorrectly) as if it is formatted as General?
2. Given that problem is corrected or avoided somehow, is there a faster way to do the comparison?

[MartinLiss]

Well I found a simple fix that allows it to run correctly

If ws1.Cells(lngRow, "BF") = .Cells(lngRowD, "M").Text Then

but it takes eight minutes!

[jmcilhinney]

I would think that you'd be better off pulling all the values out of each column into an array or collection, then doing your comparisons on those. Going back to the cells over and over and converting the numbers to text over and over would be adding considerable overhead unnecessarily.

[Zvoni]

In a few cases however BF will be "159" but M will be 159 and and since they are not the same there's no match.

I've encountered that, too, especially if the data comes from somewhere else (imported CSV, Database-Report exported to Excel, whatever) and could trace it back to the following:
1) Cell-Content is formatted as Text. Your check in your VBA-code returns "It's a String". Usually you also see this green Flag at the cell indicating an "error" (The one you click on and it offers to convert it to numeric, ignore the erorr, etc.)
2) User clicks into the cell (as if to edit it), but doesn't change anything. He hits return, and (sometimes) you can watch the content jumping from left-aligned to right-aligned, reformatting itself from Text to Numeric

EDIT: btw: what are you trying to achieve with your VBA-code?
Looks like you have nested loops or some such

[MartinLiss]

I would think that you'd be better off pulling all the values out of each column into an array or collection, then doing your comparisons on those. Going back to the cells over and over and converting the numbers to text over and over would be adding considerable overhead unnecessarily.

Thank you. While not exactly what I wound up doing it did remind me of a technique where the worksheet columns values are copied to Variable arrays and the processing is done within those arrays. After doing that the whole process took 2 seconds! Note that I still had to use cStr().

Here's an example from my code library.

  Dim vC, vI
    Dim lLastRow              As Long
    Dim n                     As Long
    Application.ScreenUpdating = False

    lLastRow = Cells(Rows.Count, "I").End(xlUp).Row
    vI = Range("I2:I" & lLastRow).Value2
    vC = Range("C2:C" & lLastRow).Value2
    For n = LBound(vI, 1) To UBound(vI, 1)
        If vI(n, 1) = "US" Then
            ' Do something
        Else
            ' do something else
        End If
    Next
    Range("I2:I" & lLastRow).Value2 = vI
    Range("C2:C" & lLastRow).Value2 = vC

    Application.ScreenUpdating = True