String compare doesn't compare?

-I have 2 columns each of which are formatted as TEXT, one on sheet2 and the
other on sheet1

-both have identical data (all numbers put in as strings)

-I loaded up the column on sheet2 with: sheet2.cells(k,1) = Textbox2

-I loaded up the column on sheet1 with: sheet1.cells(k,1) = instring (where
Dim instring as string)

-after this was done, I cycle through the columns comparing them with:

If Sheet1.Cells(row, C_CBib) = Sheet2.Cells(i, 1)

-for every row the above "if" is true as you would expect.

-if I M A N U A L L Y change one of the numbers in one column from say 4 to
4 on sheet2 (take 4 out and put it back in), then that row no longer
compares. With a breakpoint set at the above "if", it shows the following:

Sheet1.Cells(row, C_CBib) = 4 and Sheet2.Cells(i, 1) = "4" N O T E the
parens

-if I do this to the same number on sheet1, it does the same thing only the
parens jump sides.

A L S O :

-if I run through the whole column on sheet2 with the statement:
Sheet2.Cells(i, 1) = Cstr(Sheet2.Cells(i, 1) ), it does something to all the
values such that they A L L show "parens" now. When I do the same to sheet1,
they remain the same(NO parens).

I can fix the problem by changing the above "if" to:

If Cstr(1.Cells(row, C_CBib))= Sheet2.Cells(i, 1)

However, I would have to do things like this in probably a hundred different
places in the code. Doesn't seem to be a good fix to me. It seems to me
there is some other underlying problem that I am not aware of.

Anybody have any ideas??

Thanks!!

I think you have a mixture of numbers and text.

When you "loadup" those cells, I'd make sure the column/cell was formatted as
text first.

sheet1.range("a:a").numberformat="@"
sheet1.cells(k,1).value = instring

(or even cell by cell.)

If you're unlucky and the cell is formatted as general, you could have trouble
with those numbers.

Ken Soenen wrote:
>
> -I have 2 columns each of which are formatted as TEXT, one on sheet2 and the
> other on sheet1
>
> -both have identical data (all numbers put in as strings)
>
> -I loaded up the column on sheet2 with: sheet2.cells(k,1) = Textbox2
>
> -I loaded up the column on sheet1 with: sheet1.cells(k,1) = instring (where
> Dim instring as string)
>
> -after this was done, I cycle through the columns comparing them with:
>
> If Sheet1.Cells(row, C_CBib) = Sheet2.Cells(i, 1)
>
> -for every row the above "if" is true as you would expect.
>
> -if I M A N U A L L Y change one of the numbers in one column from say 4 to
> 4 on sheet2 (take 4 out and put it back in), then that row no longer
> compares. With a breakpoint set at the above "if", it shows the following:
>
> Sheet1.Cells(row, C_CBib) = 4 and Sheet2.Cells(i, 1) = "4" N O T E the
> parens
>
> -if I do this to the same number on sheet1, it does the same thing only the
> parens jump sides.
>
> A L S O :
>
> -if I run through the whole column on sheet2 with the statement:
> Sheet2.Cells(i, 1) = Cstr(Sheet2.Cells(i, 1) ), it does something to all the
> values such that they A L L show "parens" now. When I do the same to sheet1,
> they remain the same(NO parens).
>
> I can fix the problem by changing the above "if" to:
>
> If Cstr(1.Cells(row, C_CBib))= Sheet2.Cells(i, 1)
>
> However, I would have to do things like this in probably a hundred different
> places in the code. Doesn't seem to be a good fix to me. It seems to me
> there is some other underlying problem that I am not aware of.
>
> Anybody have any ideas??
>
> Thanks!!

--

Dave Peterson