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

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
.
.

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"")"
 
Upvote 0
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
 
Upvote 0
.
.

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?
 
Upvote 0
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.
 
Upvote 0
.
.

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
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

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
Back
Top