VBA - VLOOKUP in a dynamic Range

[Demonsguile]

The workbook I'm working on has several sheets: Totals, Monday, Tuesday, etc set into a SheetArray.

The problem is that I'm trying to perform a VLOOKUP on data in worksheet "Monday" against column A in worksheet "Totals". However, the size of column A will change as the codes iterates through the days. Therefore, I need the range of column A in the Totals worksheet to be dynamic.

    'Find any new projects in the daily data that are not in the Totals.
    DayProjCount = Range(Sheets(SheetArray(A)).Range("AA2"), Sheets(SheetArray(A)).Range("AA2").End(xlDown)).Cells.Count
    ProjCell = 2
    
    With Worksheets("Totals")
        Set rng = .Range("a2", .Range("a2").End(xlDown))
    End With
    
    For c = 1 To DayProjCount
        On Error Resume Next
        With Worksheets(SheetArray(A))
            ValidProject = Application.VLookup(Cells(ProjCell, 27), rng, 1, False)
            'MsgBox (ValidProject)
            If IsError(ValidProject) Then
                MsgBox ("Error Found")
            Else: MsgBox ("No Error")
            End If
        ProjCell = ProjCell + 1
        End With
    Next c

During this process, the code should only find 1 error in ValidProject. But, each are throwing errors. I think it's because the VLOOKUP isn't searching through the correct range, but I don't know how to check it.

 

[Demonsguile]

Any helpful soul willing to tackle this one?

 

[SatishKumar.Dx]

Can you give us an example how you do manually without macro..

I think you are trying to search a data from each sheet (Monday, Tuesday, ... etc) from cell AA2 till last row in column AA in sheet "Totals" from cell A2 till last row in column A. If there is no matching or if no data exist then you need a message popup stating there is an error found.

I think Sheet "Total" is a consolidation of other sheets(Monday, Tuesday ... etc) and what you are trying to do is a quality check of whether all data from sheets(Monday, Tuesday.. etc) are there in sheets "Total" using Vlookup

Please correct me if i'm wrong.