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
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

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?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,017
Messages
5,834,962
Members
430,330
Latest member
drAli77

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top