Automating Hitting F2 + Enter?

SemiCatS

New Member
Joined
Apr 16, 2015
Messages
9
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
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

CalcSux78

Well-known Member
Joined
Oct 15, 2013
Messages
1,120
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.

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

SemiCatS

New Member
Joined
Apr 16, 2015
Messages
9
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,322
Members
414,053
Latest member
Dual Showman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top