asked on # 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.

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.

Microsoft ExcelMicrosoft ApplicationsMicrosoft Office

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)

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)

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

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(xlToLef

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

Maybe like this:

Joop

```
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
```

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

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.

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.

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.

No 500points are deserved. I don't know why 0 showed up. Sorry about that.

JazzyJoop did you get the 500 points I assigned to you?

Yes sir, thank you! :)

A simple way if you just want to check a value exists in the other worksheet, is:

Open in new window

Joop