Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 577
  • Last Modified:

Excel VBA Vlookup function

I am trying to find a value in a spreadsheet table. I am wondering if Vlookup may be the correct function to use. I get a value such as "GQ-000" from a worksheet called "DRA Summary" and would like to find that value in another worksheet called "Data" within the same workbook.
Do you think this may be the best way to do this?
Any suggestions and examples of how Vlookup works would be greatly appreciated.
Thanks in advance.
0
tesla764
Asked:
tesla764
  • 4
  • 4
1 Solution
 
MichaelBusiness AnalystCommented:
If it is 'the best way' to do it, depends on what it is exactly you are trying to accomplish. Vlookup looks for a value in the leftmost column in the specified range and returns the value in the same row from the column you specify. It returns a #N/A if the value is not found.

A simple way if you just want to check a value exists in the other worksheet, is:
=CountIf("Data" range,lookup value)

Open in new window


Joop
0
 
Saqib Husain, SyedEngineerCommented:
If
your value is in A1 of DRA Summary and
your data list is in column A of data

then
This function will give you the row number in the data sheet

=match('DRA Summary'!A1,data!A:A,0)
0
 
tesla764Author Commented:
Here is what I am trying to accomplish...

Look at Module "Get_SQnumber2"
The variable "s" contains the value to be looked up in the sheet "Data".
Once that value is found I want to move the value in column "E" (Data) sheet to Row 2 column "F" (DRA Summary) sheet.

The code module is below and I also attached the work book "Data.xlsm"

Sub Get_SQnumber2()
    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 "SQ-*" Then
            'MsgBox cell.Value
            'The variable s has the value to be looked up.
            s = Left(cell.Value, InStr(1, cell.Value & " ", " ") - 1)
            MsgBox "s: " & s
        End If
    Next
Data.xlsm
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
MichaelBusiness AnalystCommented:
Maybe like this:
Sub Get_SQnumber2()
    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))
    
    'Set lookup range
    Set LkupRng = Sheets("Data").Range("A1").CurrentRegion
    
    For Each cell In rng
        If cell.Value Like "SQ-*" 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)
            Sheets("DRA Summary").Range("F2") = v
            End
        End If
    Next
End Sub

Open in new window

If it finds the SQ-*** value in column A of the 'Data' worksheet it returns the value in column E of the same row to cell F2 of the 'DRA Summary' worksheet, and then ends the procedure.

Joop
0
 
tesla764Author Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for tesla764's comment #a39656539

for the following reason:

Thanks, this gives me something I can work with. I still need to make a few more mods, but you have been very helpful. This is the first time I have used Vlookup.
Thanks again.
0
 
MichaelBusiness AnalystCommented:
0
 
tesla764Author Commented:
No 500points are deserved. I don't know why 0 showed up. Sorry about that.
0
 
tesla764Author Commented:
JazzyJoop did you get the 500 points I assigned to you?
0
 
MichaelBusiness AnalystCommented:
Yes sir, thank you! :)
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!

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now