CODEWITHSUNDEEP
vbaexcel
Selrac
Selrac

Reputation: 2293

Excel formula will not work until I acess a cell and press enter

I have a funny problem in Excel. I have a cells (A1) with a number but with the cell formatted as text value. I have a cell (A2) with a lookup formula using A1 as a reference. The formula gives an error. To remove the error I find that I can do two things:

1- I enter into A1 cells, I put the cursor at the end of the number and I press enter 2- I add an apostrophe at the beginning of the number

I tried other things, like changing the cell to number and back to text, copy and paste special, but I can not find another way to resolve this issue.

I have hundreds of cells with this problem, therefore I would like to find a solution that prevents me to go into each cell to correct it manually

Any ideas on how to overcome this problem? Is there any macro I could use?

Upvotes: 0

Views: 3291

Answers (2)

Nicolas Vannier
Nicolas Vannier

Reputation: 108

This is because Excel is not handling your values as you want.

In A1 cell, Excel is thinking that your value is a number, and the lookup formula is searching in a range where the key is a text.

When you add an apostrophe, you are forcing the value to be handled as text, and the lookup is functioning well.

You can try to use the "convert" feature in Excel to convert all keys to numbers in the lookup range.

Upvotes: 0

Rory
Rory

Reputation: 34035

You could select the cells and use this:

Sub makeNumbersText()
    With Selection
        .Value2 = Evaluate("INDEX(""'""&" & .Address & ",)")
    End With

End Sub

Upvotes: 1

Related Questions