Link to home
Create AccountLog in
Avatar of T1080
T1080Flag 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
Avatar of Ingeborg Hawighorst (Microsoft MVP / EE MVE)
Ingeborg Hawighorst (Microsoft MVP / EE MVE)
Flag of New Zealand image

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.
Avatar of 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?
Avatar of T1080

ASKER

My workbook is set to automatic calculation.
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
Avatar of Ingeborg Hawighorst (Microsoft MVP / EE MVE)
Ingeborg Hawighorst (Microsoft MVP / EE MVE)
Flag of New Zealand image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of 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

Avatar of T1080

ASKER

Thanks Again!