Avatar of tesla764
tesla764 asked on

excel vba Vlookup function not working as expected

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
Microsoft ApplicationsMicrosoft OfficeMicrosoft Excel

Avatar of undefined
Last Comment
byundt

8/22/2022 - Mon
byundt

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

Open in new window

byundt

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.
byundt

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
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ASKER
tesla764

What is the 5 doing?
byundt

Return a result from column E, the fifth column in the range A:E on worksheet Data
ASKER
tesla764

Alright it's getting the data from the "DRA Summary" sheet.
How wouldI get a value from the "Data" sheet?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
byundt

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

ASKER
tesla764

That works.
In the Range specification I don't want A:E I just want E.
How would I specify that?
byundt

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.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ASKER
tesla764

So you have to specify a range and also specify the specific column number. Is that correct?
ASKER
tesla764

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?
byundt

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

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
tesla764

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?
ASKER CERTIFIED SOLUTION
byundt

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question