Vlookup inconsistencies and remedy

[Tigerexcel]

A 2 part question. Everyone has had the issue of Vlookup returning an error when a field is formatted as text and it requires a number. I run into this issue but it doesn't always return an error, sometimes even on the same files it returns inconsistent results. I notice it particularly when I download an external file. What is the main factor in causing this error, is it that the lookup value is formatted as text, or is the table array the major issue? The lookup formula will work sometimes when one or the other is formatted as General or Number but not always.
Even when you have formatted both the lookup value and the corresponding entry in the table array, a re-set is sometimes required to return the correct value. I usually do this by re-typing some of the lookup formula and that does the trick. I'd like to combine all these steps into a one-step process which will probably a macro so that I speed up the processing, anyone have such a macro?

 

[jasonb75]

Before uploading the file that Mark has asked for, it would be helpful to highlight a couple of cells where the vlookup result is incorrect and type the expected result in the cell next to it.

 

[Tigerexcel]

It should return Melbourne, box link is Box

 

[jasonb75]

Now I follow, the way you phrased your question earlier implied that the formula was returning the incorrect result, not that the formula was visible instead of a result.

After applying your desired formatting, running this will 'fix' any formulas

Sub fixit()
    Cells.Replace What:="=", Replacement:="=", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End Sub

 

[Tigerexcel]

Thanks Jason but let's go back to my original question, why does it happen as it only happens on the odd occasion, and can the macro include changing the formatting so that it fixes the situation all in one go?

 

[MARK858]

What are the cells formatted as before you put in the formulas?

 

[Tigerexcel]

What are the cells formatted as before you put in the formulas?

Text I believe.

 

[MARK858]

That is your answer then, if they are formatted as Text then they remain as text.
To demonstrate format a blank cell as text and then type a number in the cell, you will see it will align to the left as it is text.

 

[Tigerexcel]

That is your answer then, if they are formatted as Text then they remain as text.
To demonstrate format a blank cell as text and then type a number in the cell, you will see it will align to the left as it is text.

If I format the next column to General or Number and then copy the formula across it still returns the formula rather than the value, the only thing that works is if some part of the formula is re-typed.

 

[jasonb75]

When you drag you create an exact copy, including any problems.
This will format the selected cells to general and 'fix' any formulas that appear as text.

Sub fixit()
With Selection
    .NumberFormat = "General"
    .Replace What:="=", Replacement:="=", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End Sub

Does that do what you need, or do you need to expand the format correction beyond that?

Applying 'General' to the whole sheet could remove the correct formatting from other cells if there are any that are formatted as date, currency, percentage, etc. If that is not going to be a problem for you then simply changing Selection to Cells should work.

 

[Tigerexcel]

When you drag you create an exact copy, including any problems.
This will format the selected cells to general and 'fix' any formulas that appear as text.

Sub fixit()
With Selection
    .NumberFormat = "General"
    .Replace What:="=", Replacement:="=", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End Sub

Does that do what you need, or do you need to expand the format correction beyond that?

Applying 'General' to the whole sheet could remove the correct formatting from other cells if there are any that are formatted as date, currency, percentage, etc. If that is not going to be a problem for you then simply changing Selection to Cells should work.

Thanks that does the job.