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
Thank you,
Troy
F2-Enter-issue.xls
Microsoft Excel
Last Comment
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?
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
=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
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.
ASKER
teylyn:
Thanks a Ton for the help!! Your trim solution worked with my code below:
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
ASKER
Thanks Again!
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.