Automating Hitting F2 + Enter?

[SemiCatS]

Hi folks.

I've been working on an excel sheet which job is to match an alternative ID (Alphanumeric, numeric or text) with another number via three different columns using the VLOOKUP formula. When I'm testing it, by typing some random number/text into the "source" cell, and typing the same number in one of the "data-sheets", it works very well, but when I collect all the data from a bunch of what we call breakdown sheets and paste the numbers in the source column, nothing happens until I hit F2 and enter... Then, magically, the corresponding value pops up in the column. My question is: Is there a way to make Excel do the "F2 + Enter" for me, since it's quite time consuming having to hit F2 + Enter more than 1600 times, even though it's good excersise for the fingers/arms...

The workbook in question can be found here:

https://www.dropbox.com/sh/bw2ut5kue2yktjv/AADf4i9ChewQ-snJNzh5h7Gfa?dl=0

I've also used a "number conversion sheet" to get the numbers in the correct "format".
This sheet can be found in the same folder as the "main" workbook.
The way it works, is that it checks any number written in the A-column and returns it in the "correct" format, according to company standard.
All nine digit numbers starting with 10, 11, 12, 13 or 14, should be shown in the format 13 1001 135. By typing 131001135 in the A-column,
the formula returns 13 1001 135 and so on. I suspect that this "conversion" may have something to do with the above mentioned behaviour?

Thankful for any help on this issue.

Best regards

Stig M. Thu

 

[CalcSux78]

When data is pasted, Excel copy's the formats from the source book. Sometimes a number is copied over as text.

An easy way around it (not fool proof) is to read in the data into an array and then send that array back to the worksheet. For me, it usually fixes things.

Sub CleanUP()
    Dim rng As Range
    Dim Arr As Variant
    
    Set rng = [A1].CurrentRegion
    Arr = rng
    rng = Arr
End Sub

 

[SemiCatS]

Hi, and thanks for your reply.

It is a "little bit over mye head", so I'm not quite sure how to implement this (where to write the code, i.e. and so on).
Just to clarify: When I do the copying from the number conversion sheet, I do a special paste, that should only paste the
values, so no source formatting should be included, right?

Best regards

Stig M. Thu