Unable To Match Data

    Hi,


    I need to compare 2 sets of data, but I have run into a problem- one set of data is prefixed with ' s. This symbol does not appear in the cell, only in the formula box.


    For example, S1234 in an excel cell is 'S1234 in the formula box.
    Whenever, I compare this cell against the value S1234 and check if they are equal, it returns false.


    IS there any way to get around this?

    :angrypc:

    Re: Un-able To Match Data


    Hi aadarsh,


    The apostrophe (‘) prefix is an xl shortcut method of identifying the data as text. It should not effect any comparison formulas. Can you attach a sample file with your comparison formula?


    Bill

    [FONT="Arial Black"][COLOR="blue"][SIZE="4"]Bill[/SIZE][/COLOR][/FONT]
    Tip: To avoid chasing code always use Option Explicit.

    Re: Un-able To Match Data


    in the VBA Code, I am simply writing


    if Cells (i,"A").Value= SourceValue Then


    The SourceValue (of type String) is obtained from a text file.
    While Cells(i,"A") refers to the cells which have the apostrophe.

    :angrypc:

    Re: Un-able To Match Data


    Code
    if Cells (i,"A").Value= SourceValue Then



    Are you sure the “SourceValue” does not have any leading or trailing spaces or hidden characters. Display the SourceValue and its length in a msgbox and see if the number of characters displayed = the length count.
    Bill

    [FONT="Arial Black"][COLOR="blue"][SIZE="4"]Bill[/SIZE][/COLOR][/FONT]
    Tip: To avoid chasing code always use Option Explicit.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login