Using VBA to populate a column based on matching values in two columns with a third columns value

Question

The scenario is the following, I have a sheet which has two columns, one I want to match against, the other contains the values I want to copy in case of a match. I have a second sheet which contains the values to search for in the match column and the column to which to copy the value column if we have a match.

This looks like a prime candidate for VLOOKUP but I want to avoid having to hard code the column number as the data sheets contents can vary. So I Find the column based on the header's contents. If there is a way to VLOOKUP the results with this flexibility, then that also works. I can't use a formula, this needs to be in VBA.

Below there are 4 columns defined:

toFindCol: this contains the master list of values I am going to try and find in the toMatch column
toMatchAgainstCol: this contains the list of values i want to match the toFindCol values against
valueCol: this contains the value I want to copy, in case there is a match, the value has to come from the row on which the match occurred
resultsCol: this is where i want to copy the value to, the value needs to be copied to the row of the toFind value

For some reason the code below gives a "Type Mismatch" error.

Eventually I want to wrap this into a function/subroutine so I can pass in the sheets and column headers and get it to work it's magic. Brownie points for who can do that :)

Dim toFindCol As Range
Dim toMatchAgainstCol As Range
Dim valueCol As Range
Dim resultsCol As Range
Dim match As Variant

Set toFindCol = cohortDataSetSht.Columns(1).EntireColumn
Set toMatchAgainstCol = userSht.Cells.Find("id", , xlValues, xlWhole).EntireColumn
Set valueCol = userSht.Cells.Find("cdate", , xlValues, xlWhole).EntireColumn
Set resultsCol = cohortDataSetSht.Columns(4)

For Each findMe In toFindCol
    Set match = toMatchAgainstCol.Find(What:=findMe, LookIn:=xlValues, _
        LookAt:=xlWhole, SearchOrder:=xlByRows)

    If Not match Is Nothing Then
        resultsCol.Cells(findMe.Row, 0).Value = valueCol.Cells(match.Row, 0).Value
    End If
Next findMe

gwhenning gwhenning · Accepted Answer · 2013-12-06T19:46:58

There is a way to do this in a VLOOKUP. The basic format of the vlookup is vlookup(1,2,3,4).

I pass A2 as the top cell with the value I want to lookup. This formula can then be copied down to fill the other cells. Substitute the appropriate cell reference.
Use the Match function to find the columns you want to set your range in. Since we only want the column letters (i.e. -"C:G"), I strip out the number (which will always be 1 character) using the Len function, and then use the indirect function to convert that to a useable range, and lookup against that. INDIRECT(LEFT(ADDRESS(1,MATCH("ToMatch",$1:$1,0),4),LEN(ADDRESS(1,MATCH("ToMatch",$1:$1,0),4))-1)&":"&LEFT(ADDRESS(1,MATCH("ValueCol",$1:$1,0),4),LEN(ADDRESS(1,MATCH("ValueCol",$1:$1,0),4))-1))
I then use the MATCH("ValueCol",$1:$1,0)-MATCH("ToMatch",$1:$1,0)+1 to calculate the column number of that contains the value we want to lookup in that range.
I use 0 to indicate an exact match rather than closest value

The whole thing looks like this: =VLOOKUP(A2,INDIRECT(LEFT(ADDRESS(1,MATCH("ToMatch",$1:$1,0),4),LEN(ADDRESS(1,MATCH("ToMatch",$1:$1,0),4))-1)&":"&LEFT(ADDRESS(1,MATCH("ValueCol",$1:$1,0),4),LEN(ADDRESS(1,MATCH("ValueCol",$1:$1,0),4))-1)),MATCH("ValueCol",$1:$1,0)-MATCH("ToMatch",$1:$1,0)+1,0)

This code assumes the column headers are in row 1. If not, replace the $1:$1 above with the absolute reference to the row your headers are in (i.e. -Row 5 would be $5:$5).

The other caveat is that we have to assume that the value you want to lookup will always be to the right of the lookup column.

Using VBA to populate a column based on matching values in two columns with a third columns value

2 Answers