VBA VLookup Help!

Excelworld

New Member
Joined
Nov 10, 2011
Messages
33
Hi All,

I need to do a Vlookup on a persons name in sheet1 to find that persons name in sheet2 and put a "Yes" in the correct cell of column 26 when it finds the name

so far I have:

#Application.WorksheetFunction.VLOOKUP(B3,Sheet2.Range("C3:AB1000"), 26, FALSE)# - 'so the cell that it finds will be empty but I want it to now populate this cell with the word "Yes"

Any help would be great :)

Thanks in advance

EW
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

ParamRay

Well-known Member
Joined
Aug 6, 2014
Messages
1,195
.
.

I don't think VLOOKUP is right function for this. If I've interpretted the question correctly then you'll need the MATCH function instead.


I would suggest a formula like this:

=IF(ISNUMBER(MATCH(B3,Sheet2!C:C,0)),"yes","no")


Or (in VBA):

Cells(3, 26).Formula = "=IF(ISNUMBER(MATCH(B3,Sheet2!C:C,0)),""yes"",""no"")"
 

Excelworld

New Member
Joined
Nov 10, 2011
Messages
33
Hi,

Yes the match works :) but this is putting the Yes or No on the dashboard page - What I need it to do it look at the name on the dashboard and then go and find that name on sheet2 (the tracker) then put a "yes" in the "has this person been contacted" column

In sheet2 the name column is B:B and the "has this person been contacted"column is AA:AA
 

Excelworld

New Member
Joined
Nov 10, 2011
Messages
33
thinking about it, could this code just copy the "Yes" in the target cell instead of the all the code?
 

ParamRay

Well-known Member
Joined
Aug 6, 2014
Messages
1,195

ADVERTISEMENT

.
.

Please confirm:

(1) Column B of Sheet1 contains names;
(2) Column B of Sheet2 also contains names;
(3) Column AA of Sheet2 needs to contain a formula; and
(4) The formula should return whether or not the names in column B (of Sheet2) are found in column B of Sheet1.

Is this correct?
 

Excelworld

New Member
Joined
Nov 10, 2011
Messages
33
Hi,

below answers:

(1) Column B of Sheet1 contains names; YES
(2) Column B of Sheet2 contains names; YES
(3) Column AA of Sheet2 needs to show the result e.g "Yes" of the formula rather than the formula displaying the result - the reason for this is that once that person has a "yes" in column AA their name will disappear from Sheet1 so it will cause a circular reference error

The list of names in Sheet1 are extractions from a pivot table in Sheet1 looking for names of people who have not been contacted in sheet2, so it's kind of like a daily workload. next to each name in Sheet1 there are macro buttons that send a generic email out to that person - where I'm stuck is I want the macro to then go and put the value "yes" in sheet2 so when the pivot table is refreshed in sheet1 that persons name disappears.
 

ParamRay

Well-known Member
Joined
Aug 6, 2014
Messages
1,195
.
.

Perhaps something like this:

Code:
Sub Contacted()

    Dim Rang As Range
    Dim Cell As Range
    
    On Error Resume Next
        With Worksheets("Sheet2").Columns("B")
            Set Rang = Union( _
                .SpecialCells(xlCellTypeConstants, xlTextValues), _
                .SpecialCells(xlCellTypeFormulas, xlTextValues))
        End With
    On Error GoTo 0
    
    If Not Rang Is Nothing Then
        For Each Cell In Rang
            With Range("AA" & Cell.Row)
                .Formula = "=IF(ISNUMBER(MATCH(B" & Cell.Row & ",Sheet1!B:B,0)),""no"",""yes"")"
                .Value = .Value
            End With
        Next Cell
    End If

End Sub
 

Excelworld

New Member
Joined
Nov 10, 2011
Messages
33
I tried the above but it didn't result in a "Yes" being entered into Cell AA16 in Sheet2 - so obviously the pivot table in sheet1 is still showing that persons name.

Is this because the above is comparing whole columns in sheet1 to sheet2 instead of a specific cell?
 

Watch MrExcel Video

Forum statistics

Threads
1,108,960
Messages
5,525,916
Members
409,671
Latest member
nasseralateek

This Week's Hot Topics

Top