My worksheet has two tabs. I'm using Vlookup to take the number in Column U
on tab#1 and find that same number in Column B of tab#2, and return data from
several columns over back to tab#1. Frist, I need to modify the code so that
it finds the number even with the following variations:

Tab#1 Possibilities
1207-1035
R-1207-1035

Tab#2 Possibilities
1207-1035
R-1207-1035
NR-1207-1035
0913-2033,R-1207-1035,NR-9999-1234
0913-2033,NR-9999-1234,1207-1035
(Note that there could be 2 or more numbers in the same cell, and the #
needing to be found could be the 1st # in the cell, the 2nd, the 3rd, etc.);
I need to search based on the numeric values only (i.e., ignoring any alpha
characters).

Secondly, there might be more than one occurrence of a number on tab#2. Is
there a way to make Excel also find the 2nd/3rd/4th/etc. occurrences so that
I can transfer that data back to tab#1?

The code I'm using now is as follows; it's the basic vlookup code. Any help
would be appreciated!

Dim lookupRange As Range
Dim srceRange As Range
Dim cell As Range
Dim lookupValue As Variant
Set lookupRange = Worksheets("tab2").Range("b4:s70")
Set srceRange = Worksheets("tab1").Range("u5:u70")
For Each cell In srceRange
With cell
lookupValue = Application.VLookup(.Value, lookupRange, 2, False)
If Not IsError(lookupValue) Then
.Offset(0, 11).Value = lookupValue
Else
.Offset(0, 11).Value = ""
End If
End With
Next cell
End Sub

You will have problems using a vlookup. Find and findnext would be more
appropriate... Something like this (I assume since you are using range
objexts taht you are familiar with them...)

Sub test()
Dim rngFoundItems As Range

Set rngFoundItems = Findstuff("1207-1035")

End Sub

Public Function Findstuff(ByVal WhatToFind As String) As Range
Dim rngToSearch As Range
Dim rngFound As Range
Dim rngFoundAll As Range
Dim rngFirst As Range
Dim wks As Worksheet

Set rngFoundAll = Nothing
Set wks = Sheets("Sheet1")
Set rngToSearch = wks.Columns("B")
Set rngFound = rngToSearch.Find(What:=WhatToFind, LookAt:=xlPart)
If Not rngFound Is Nothing Then
Set rngFirst = rngFound
Set rngFoundAll = rngFound
Do
Set rngFoundAll = Union(rngFound, rngFoundAll)
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = rngFirst.Address
End If
Set Findstuff = rngFoundAll
End Function
--
HTH...

Jim Thomlinson


"Paige" wrote:

My worksheet has two tabs. I'm using Vlookup to take the number in Column U
on tab#1 and find that same number in Column B of tab#2, and return data from
several columns over back to tab#1. Frist, I need to modify the code so that
it finds the number even with the following variations:

Tab#1 Possibilities
1207-1035
R-1207-1035

Tab#2 Possibilities
1207-1035
R-1207-1035
NR-1207-1035
0913-2033,R-1207-1035,NR-9999-1234
0913-2033,NR-9999-1234,1207-1035
(Note that there could be 2 or more numbers in the same cell, and the #
needing to be found could be the 1st # in the cell, the 2nd, the 3rd, etc.);
I need to search based on the numeric values only (i.e., ignoring any alpha
characters).

Secondly, there might be more than one occurrence of a number on tab#2. Is
there a way to make Excel also find the 2nd/3rd/4th/etc. occurrences so that
I can transfer that data back to tab#1?

The code I'm using now is as follows; it's the basic vlookup code. Any help
would be appreciated!

Dim lookupRange As Range
Dim srceRange As Range
Dim cell As Range
Dim lookupValue As Variant
Set lookupRange = Worksheets("tab2").Range("b4:s70")
Set srceRange = Worksheets("tab1").Range("u5:u70")
For Each cell In srceRange
With cell
lookupValue = Application.VLookup(.Value, lookupRange, 2, False)
If Not IsError(lookupValue) Then
.Offset(0, 11).Value = lookupValue
Else
.Offset(0, 11).Value = ""
End If
End With
Next cell
End Sub

Thanks, Jim. I've never used functions before, but am getting out my
reference books to learn them now - better late than never! Appreciate your
assistance - have a great Christmas....

"Jim Thomlinson" wrote:

You will have problems using a vlookup. Find and findnext would be more
appropriate... Something like this (I assume since you are using range
objexts taht you are familiar with them...)

Sub test()
Dim rngFoundItems As Range

Set rngFoundItems = Findstuff("1207-1035")

End Sub

Public Function Findstuff(ByVal WhatToFind As String) As Range
Dim rngToSearch As Range
Dim rngFound As Range
Dim rngFoundAll As Range
Dim rngFirst As Range
Dim wks As Worksheet

Set rngFoundAll = Nothing
Set wks = Sheets("Sheet1")
Set rngToSearch = wks.Columns("B")
Set rngFound = rngToSearch.Find(What:=WhatToFind, LookAt:=xlPart)
If Not rngFound Is Nothing Then
Set rngFirst = rngFound
Set rngFoundAll = rngFound
Do
Set rngFoundAll = Union(rngFound, rngFoundAll)
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = rngFirst.Address
End If
Set Findstuff = rngFoundAll
End Function
--
HTH...

Jim Thomlinson


"Paige" wrote:

My worksheet has two tabs. I'm using Vlookup to take the number in Column U
on tab#1 and find that same number in Column B of tab#2, and return data from
several columns over back to tab#1. Frist, I need to modify the code so that
it finds the number even with the following variations:

Tab#1 Possibilities
1207-1035
R-1207-1035

Tab#2 Possibilities
1207-1035
R-1207-1035
NR-1207-1035
0913-2033,R-1207-1035,NR-9999-1234
0913-2033,NR-9999-1234,1207-1035
(Note that there could be 2 or more numbers in the same cell, and the #
needing to be found could be the 1st # in the cell, the 2nd, the 3rd, etc.);
I need to search based on the numeric values only (i.e., ignoring any alpha
characters).

Secondly, there might be more than one occurrence of a number on tab#2. Is
there a way to make Excel also find the 2nd/3rd/4th/etc. occurrences so that
I can transfer that data back to tab#1?

The code I'm using now is as follows; it's the basic vlookup code. Any help
would be appreciated!

Dim lookupRange As Range
Dim srceRange As Range
Dim cell As Range
Dim lookupValue As Variant
Set lookupRange = Worksheets("tab2").Range("b4:s70")
Set srceRange = Worksheets("tab1").Range("u5:u70")
For Each cell In srceRange
With cell
lookupValue = Application.VLookup(.Value, lookupRange, 2, False)
If Not IsError(lookupValue) Then
.Offset(0, 11).Value = lookupValue
Else
.Offset(0, 11).Value = ""
End If
End With
Next cell
End Sub