type mismatch handling 1

[jay9333]

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...

If shipmentListWS.Cells(ShipmentFileRowIndex, 3) = MasterListWS.Cells(MasterListRowIndex, 4).Value Then

... 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:

    '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

... 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?

 

[SkipVought]

Jay,

Check for a Numeric value IsNumeric(). Compare as either numeric or not.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[brucegn]

In addition to skips' suggestion, you could also try adding Val() to your code;

If Val(shipmentListWS.Cells(ShipmentFileRowIndex, 3)) = Val(MasterListWS.Cells(MasterListRowIndex, 4).Value) Then

Which shoud return just the numeric value, which brings us full circle back to skips suggestion.

 

[jay9333]

Thanks Skip,

An IsNumeric() function should do the trick. As a follow up question though.... I looked around google and MS technet for a good while looking for some type of vb "IsANumber()" functionality. I knew there had to be one. I've got a few VBA/Excel books and none of them had it. Where should I look in the future for a function I'm wondering if VB has? Anything you would suggest?

Thanks so much,
Jason

 

[SkipVought]

Hi,

Look in VB Help for Is Functions.

Also check out Microsoft Excel Visual Basic reference in VB Help.



Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884