Avatar of T1080
T1080
Flag for United States of America asked on

Why must I manually hit f2 + enter for my vlookup formula to work?

Attached is a workbook with 2 worksheets.  On the first worksheet I have a simple vlookup formula that will only populate correctly when I select the value in column A (1st worksheet) and hit f2 and enter.  Within the workbook is a macro that uses sendkeys to hit f2 and enter, but that does not even work correctly.  Is there a way to fix this issue?  I do not want to hit f2 and enter manually.  For that matter, I don't even like to use sendkeys in my VBA, but I will as a last resort.

Thank you,

Troy
F2-Enter-issue.xls
Microsoft Excel

Avatar of undefined
Last Comment
T1080

8/22/2022 - Mon
Ingeborg Hawighorst (Microsoft MVP / EE MVE)

Hi,

Is your workbook set to manual calculation?

Opening the attached file, I find the first sheet full of #NA messages, meaning that the values are not found. That's due to the fact that on EFS in column A you have numbers, but on SQL Data you have the same numbers stored as text. The comparison will return FALSE  and Vlookup will continue to return #NA unless you align the data type on the two sheets.
T1080

ASKER
On worksheet EFS the values in column A are formatted as text.  That is when I right click and choose format it shows text as the formatting.  My guess is that the values are not actually formatted as text until I hit f2 and enter for each value.  How can I get around this issue?
T1080

ASKER
My workbook is set to automatic calculation.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Ingeborg Hawighorst (Microsoft MVP / EE MVE)

In D2 enter

=Text(A2,"0")

copy down.

Copy column D, use Paste - Special - Values to paste over column A

Delete column D


regards

teylyn

ASKER CERTIFIED SOLUTION
Ingeborg Hawighorst (Microsoft MVP / EE MVE)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
T1080

ASKER
teylyn:
Thanks a Ton for the help!!  Your trim solution worked with my code below:

Sub FormatNumberToText()
Dim LastRow As Long
LastRow = ActiveSheet.UsedRange.Rows.Count
Range("A2:A" & LastRow).Select
Dim r As Range
For Each r In Selection
r.Select
r.Value = Trim(r.Value & " ")

Next

End Sub

Open in new window

T1080

ASKER
Thanks Again!
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.