I'm writing a macro in excel to search through a worksheet of data and find similar rows of data in a different worksheet. One of the items I'm comparing in each row is a field called "Visit Number". They are entered slightly differently in each worksheet. For instance in one worksheet the visit number will be entered as 08 and in the other worksheet the visit number will be entered as just plain 8 (though in both worksheets the format of the cell is "general"). If I do a straight compare of the data like so...
... the macro doesn't see 08 as being equal to 8. I guess it stores them as strings by default or something, I don't know.
I do know that if I declare a couple of variables as Longs first, ie:
... and store the values in them before the compare, then I'm good to go because 8 and 08 are the same when stored as a Long.
However, my problem is that every once in a while one of the worksheets will have a letter in the "Visit Number" field (i.e. "8A" as a visit number). When I have the macro store visit numbers as a Long before comparing them the program barfs where its tries to store the string "8A" in a Long variable. I get a "Runtime Error 13, Type Mismatch".
I'm thinking I'll do some error trapping, and if Err is equal to 13 then I'll set a flag and GoTo back into the function. On each run through the 'compare loop' I'll check the flag, and if it is set then I'll compare the values as strings, and for the next run un-set the flag. Is that as unelegant as it seems to me?
Code:
If shipmentListWS.Cells(ShipmentFileRowIndex, 3) = MasterListWS.Cells(MasterListRowIndex, 4).Value Then
I do know that if I declare a couple of variables as Longs first, ie:
Code:
'Dim thisMasterListVisitNo As Long 'holds each visit number in master list as we loop through
'Dim thisShipmentVisitNo As Long 'holds each visit number in shipment file as we loop through
However, my problem is that every once in a while one of the worksheets will have a letter in the "Visit Number" field (i.e. "8A" as a visit number). When I have the macro store visit numbers as a Long before comparing them the program barfs where its tries to store the string "8A" in a Long variable. I get a "Runtime Error 13, Type Mismatch".
I'm thinking I'll do some error trapping, and if Err is equal to 13 then I'll set a flag and GoTo back into the function. On each run through the 'compare loop' I'll check the flag, and if it is set then I'll compare the values as strings, and for the next run un-set the flag. Is that as unelegant as it seems to me?