multvlookup from previous thread wont work

September 30, 2010 at 13:07:54
Specs: Windows XP
I'm using multvlookup

Option Explicit

Public Function MultVlookup( _
            FindThis As Variant, _
            LookIn As Range, _
            SheetRange As String, _
            OffsetColumn As Integer) _
        As Variant

Dim Sheet As Worksheet
Dim strFirstSheet As String
Dim strLastSheet As String
Dim SheetArray() As String
Dim blnFirstSheet As Boolean
Dim rngFind As Range
Dim blnFound As Boolean
Dim n As Integer

'make search range one column
If LookIn.Columns.Count > 1 Then
    Set LookIn = LookIn.Resize(LookIn.Rows.Count, 1)
End If

'size array to hold all worksheet names
ReDim SheetArray(ActiveWorkbook.Worksheets.Count)

'get the two worksheet names
strFirstSheet = Left(SheetRange, InStr(1, SheetRange, ":") - 1)
strLastSheet = Right(SheetRange, _
                Len(SheetRange) - InStr(1, SheetRange, ":"))
'put worksheet names in the range 'Sheet Range' into an array
blnFirstSheet = False
n = 0
For Each Sheet In ActiveWorkbook.Worksheets()
    If Sheet.Name = strFirstSheet Then
        blnFirstSheet = True
    End If
    If blnFirstSheet = True Then
        SheetArray(n) = Sheet.Name
        n = n + 1
    End If
    If Sheet.Name = strLastSheet Then
        blnFirstSheet = False
    End If
Next Sheet

'search range on each worksheet in array
blnFound = False
For n = 0 To UBound(SheetArray, 1)
    With Worksheets(SheetArray(n)).Range(LookIn.Address)
        Set rngFind = .Find(FindThis, LookIn:=xlValues, _
                        MatchCase:=False, LookAt:=xlWhole)
    End With
    If Not rngFind Is Nothing Then
        'match found
        blnFound = True
    End If
    If blnFound = True Then Exit For
Next n

'return value
If blnFound = True Then
    MultVlookup = rngFind.Offset(0, OffsetColumn - 1)
    MultVlookup = "Not Found"
End If
End Function

And in my example workbook I have Sheet1 Sheet 2 Sheet3

Columns are Server, Ticket, and Past Ticket

Servers are just colors, Red, Orange, Yellow, Green, Blue, Indigo, Violet.

I just gave a few servers per sheet tickets of 001, 002, 003, etc.

Past Ticket is


On sheet 1, anything I put into the Tickets column shows up fine.

On sheet 2, 3, anything I put in sheet 1 shows up as a past ticket.

But anything I put in sheets 2 and 3, does not show up on any of the sheets.

Any insight would be incredibly helpful.

See More: multvlookup from previous thread wont work

Report •

September 30, 2010 at 13:42:39
I don't know if it will make a difference, but you are not using the final version of that function.

Look at the last post in this thread, where it says:

'make function recalculate with all changes in data
'else it won't respond to changes in cells on the other worksheets

Report •

September 30, 2010 at 14:30:42
Hi, sorry I forgot to include that information but no that didn't make a difference, the volume of data and calls on that function became processing prohibitive so I elected to use the version that required manual updating. But thank you for your time regardless.

Report •

Related Solutions

Ask Question