CODEWITHSUNDEEP
excelvbavlookup
H.Birdi
H.Birdi

Reputation: 45

Is there way to loop through 3 sheets in the spreadsheet and compare the values and if found say true and if not false

I'm very new to Macro. If you can help me get started, that would be great. I have 3 sheets in the workbook. I have 4th sheet which contains email address. I would like to take each email address from 4th sheet and compare to other sheets. If found in sheet 1, create an column in the sheet 4 with name of sheet 1 say true else False.

Columns in Sht4:

  EmailAddress(B), Enabled, Name, Domain

I'm trying to build a Macro that will do that. 

Sub vLookUpEmails()

    Dim sht1 As Worksheet
    Dim sht2 As Worksheet
    Dim sht3 As Worksheet
    Dim sht4 As Worksheet

    Set sht1 = Sheets("h1")
    Set sht2 = Sheets("h2")
    Set sht3 = Sheets("h3")
    Set sht4 = Sheets("h4")

    'Creating columns in the sheet 4

    sht4.Columns("H:H").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("H1").Value = "h1"

    sht4.Columns("I:I").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("I1").Value = "h2"

    sht4.Columns("J:J").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("J1").Value = "h3"

    'Getting last row
    combLastRow = sht1.Cells(Rows.Count, "A").End(xlUp).Row

End Sub

I expect in sht 4. Creates Columns H1, H2, H3. In each of the column, it says true or false if email address found in the sht1 then in h1 column, it will say true else false.

Upvotes: 0

Views: 57

Answers (1)

Scott Craner
Scott Craner

Reputation: 152585

just try to use the match formula and see if a number is returned or not:

With sht4
    Dim lstrw As Long
    lstrw = .Cells(.Rows.Count, 2).End(xlUp).Row
    With .Range(.Cells(2, 8), .Cells(lstrw, 10))
        .Formula = "=ISNUMBER(MATCH($B2,INDIRECT(""'"" & H$1 & ""'!$A:$A""),0))"
        .Value = .Value
    End With
End With

enter image description here

  • Sheet1 has 2004 and 2005 in column A
  • Sheet2 has 2006 in Column A
  • Sheet3 has 2007 in Column A

Upvotes: 2

Related Questions