Excel 2003 VBA MultiVlookup question

[chamdan]

On a Gantt Chart, I wish to be able to gather from the (PD) Predecessors and (SC) Successors each item that is separated by a comma and pick the (ES) Early Start and (EF) Early Finish, (LS) Late Start and (LF) Late Finish corresponding value to be able to calculate the task (ES) (EF) (LS) (LF) of the next Task. I have found a user defined function that does it but it is referencing to the column A. The function is display below. I have resolved it not using a function but it is very long as it has many (IF) statement and as know, one is limited with the number of (IF). So, I would rather use a user defined function to simplify the calculation.

Public Function MultiVLookup(ReferenceIDs As String, Table As Range,  TargetColumn As Integer, Optional Delimeter As String = ",") As  Variant() 
    'MultiReference = "A1" 
    IDs = Split(ReferenceIDs, Delimeter, -1, vbTextCompare) 
    Length = (UBound(IDs) - LBound(IDs) + 1) 
    If Length = 0 Then 
        MultiVLookup = Null 
        Exit Function 
    End If 
     
    Dim Result() As Variant 
    ReDim Result(Length - 1) 
    For i = 0 To Length - 1 
        Result(i) = Application.WorksheetFunction.VLookup(IDs(i), Table, TargetColumn, False) 
    Next 
    MultiVLookup = Result 
End Function

Now each time I try to insert a column before the Column A All the calculation gets messed up.

Can some one help me change a bit the function so I can insert a column without causing any problem to the calculation.

Thank you in advance!

 

[chamdan]

Got it resolved by clearing the named columns in the Worksheet.

 

[PaddyD]

glad you got it sorted and thanks for posting shape of solution in case someon'es searching for similar issue in the future.

 

[chamdan]

Excel is pretty good about adjusting addresses when you insert cells (or columns or rows). However, you are making extensive use of a function that also adjusts, when you don’t want it to: in Row 1, you have the formula

=Column()

in the first fourteen columns. When you insert a column, all of these change. But you are using them to identify a column relative to the start of a table, so they shouldn’t change! In addition, there is one particular defined name (“Table”) that is defined as $1:$65536 (that’s the entire worksheet!), so its definition doesn’t change, and that means that everything is further displaced.
If you replace the formulas in A1:Q1 with constant numbers (1 to 14), and redefine “Table” (I changed it to

=Gantt!$A$1:$DF$57)

, you can insert columns without errors.
By the way, VLOOKUP requires that the column you search be the leftmost column of the “table.” There are other functions (LOOKUP and MATCH) that can be used to find a value in a specific column. Then you can retrieve a corresponding value from a different column, using the OFFSET or INDIRECT functions. The formulas get more complex, but with care they can be made to adjust to the insertion of columns even if you want the inserted columns to be part of the table (don’t worry about the details of this right now – but do take a bit of time to look at those worksheet functions).


It worked well, I inserted a column prior to column A and did not affect the calculations.