VBA trying to compare number as text to number, must convert

Having an issue with a very large spreadsheet where some numbers are formatted as text and som values are strings.  Trying to cycle thru 1st sheet one row at a time and comparing these cells to values on the second sheet so I can do some logic in a vba routine.

The issue is that out of the 800,000 rows of data, over 100,000 are not comparing properly because numbers formatted as text in the first sheet are not "equal" to the numbers in the second sheet.

I was going to try to convert everything to a string like:
If  STR(sheet1.cells(r,c) = STR(sheet2.cells(s,d)) then....

but then I get errors because some cells actually contain strings...and trying to make a string... a string ....gave me an error.

so, not sure how to do this.  wanted to just test if it's a string and if not, make it a string.  I think that would work.  any ideas?

here are some of the things I am trying to work with and am having trouble comparing due to formats:

W1011223
4544556
3433454 (as text)


ohmetwAsked:
Who is Participating?
 
VipulKadiaCommented:
Try this one :

If  CStr(sheet1.cells(r,c)) = CStr(sheet2.cells(s,d)) then
  msgbox "Same Value"
End IF
0
 
JeewsCommented:
Try IsNumeric() to check before convertion
0
 
JeewsCommented:
Even TypeName() can be used
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.