Solved

excel vba Vlookup function not working as expected

Posted on 2013-11-19
14
627 Views
Last Modified: 2013-11-19
The Vlookup function is not working as I thought it would.
Refer to this code (mod_GQ_Get_GQnumber1)...

Sub Get_GQnumber2()
    Dim rng As Range, cell As Range
    Dim lc As Integer
    Dim s As String, sInput As String
   
    lc = Cells(3, Columns.Count).End(xlToLeft).Column
    Set rng = Range(Cells(3, 1), Cells(3, lc))
   
    For Each cell In rng
        If cell.Value Like "GQ-*" Then
            MsgBox cell.Value
            s = Left(cell.Value, InStr(1, cell.Value & " ", " ") - 1)
            MsgBox "s: " & s

            On Error Resume Next
'            v = WorksheetFunction.VLookup(s, LkupRng, 5, 0)
            v = WorksheetFunction.VLookup(s, "Data", 5, 2)
            Sheets("DRA Summary").Range("F2") = v
            End

        End If
    Next
End Sub

This is what happens...
Refering to the line...
v = WorksheetFunction.VLookup(s, "Data", 5, 2)
the variable "s" contains the value "GQ-000" as I would expect but the variable "v" winds up being "Empty" when excuted

What I am trying to do...
I want to take the value that is in the "Data" sheet, Row 2, Column E, which is the word "Yes" and move that value to the "DRA Summary" sheet Column "F" Row "2".

Can anyone tellme what I am doing worng here?

Thanks in Advance.

I am attaching the Workbook Data.xlsm which contains everything I am refering to.
Data.xlsm
0
Comment
Question by:tesla764
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 6
14 Comments
 
LVL 81

Expert Comment

by:byundt
ID: 39660141
Change the statement with the error to:
v = WorksheetFunction.VLookup(s, Range("A:E"), 5, 2)

Open in new window

0
 
LVL 81

Expert Comment

by:byundt
ID: 39660148
You will also find that Application.VLookup is more reliable than WorksheetFunction.VLookup. The former can tolerate an error value being returned; the latter will cause a run-time error.
0
 
LVL 81

Expert Comment

by:byundt
ID: 39660170
Do you want an exact match for s since your lookup column is not sorted in ascending order? If so, you need FALSE as the fourth parameter in VLOOKUP.
v = Application.VLookup(s, Range("A:E"), 5, 0)                 '0 is equivalent to FALSE
0
Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 

Author Comment

by:tesla764
ID: 39660184
What is the 5 doing?
0
 
LVL 81

Expert Comment

by:byundt
ID: 39660279
Return a result from column E, the fifth column in the range A:E on worksheet Data
0
 

Author Comment

by:tesla764
ID: 39660347
Alright it's getting the data from the "DRA Summary" sheet.
How wouldI get a value from the "Data" sheet?
0
 
LVL 81

Expert Comment

by:byundt
ID: 39660367
It returns data from the active worksheet. If you need from a non-active worksheet, you must specify it:
v = Application.VLookup(s, Worksheets("Data").Range("A:E"), 5, 0)                 '0 is equivalent to FALSE 

Open in new window

0
 

Author Comment

by:tesla764
ID: 39660395
That works.
In the Range specification I don't want A:E I just want E.
How would I specify that?
0
 
LVL 81

Expert Comment

by:byundt
ID: 39660403
Isn't the lookup column Data worksheet column A? If you want a result from Data worksheet column E, then you need A:E with returning column 5.
0
 

Author Comment

by:tesla764
ID: 39660444
So you have to specify a range and also specify the specific column number. Is that correct?
0
 

Author Comment

by:tesla764
ID: 39660458
I want to check all of the columns, not just A:E. I have coded...
lc = Cells(3, Columns.Count).End(xlToLeft).Column
But this gives me the numeric total of columns. How would I specify the required Alpha range column A: to last column?
0
 
LVL 81

Expert Comment

by:byundt
ID: 39660482
To get all columns with data, you might use:
Dim lc As Long,ResultCol As Long
lc = Worksheets("Data").Cells(3, Worksheets("Data").Columns.Count).End(xlToLeft).Column
ResultCol = 5
v = Application.VLookup(s, Worksheets("Data").Range("A:A").Resize(,lc), ResultCol, 0)

Open in new window

0
 

Author Comment

by:tesla764
ID: 39660526
I will give that a try.
I don't know if there is a better way to accomplish what I am trying to do.
But to summarize...
1) I find a code like GQ-000(row) in the DRA Summary table.
2) I want to find the same code in the Data table.
3) When that code is found in the Data table I get the value in Column E and
4) move that data to the cell above where GQ-000 was found.

This check must be done for all cells that are like GQ-*

May be I am getting too complicated with what I have coded so far.
Any Suggestions?
0
 
LVL 81

Accepted Solution

by:
byundt earned 500 total points
ID: 39660660
I would AutoFilter the DRA Summary table for text beginning "GQ-"   You could then use the someRange.SpecialCells(xlCellTypeVisible) to capture the filtered values. Loop through that range and populate the data as required.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
This article helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- C…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question