[SOLVED] VBA for Vlookup with multiple criteria

VBA for Vlookup with multiple criteria

I have a worksheet where I have a mixture of manual and automated input. I am trying to write some VBA code to first check if a cell in range is blank, and only if it is, pull date from a sheet of dates but only return the date if it is greater than or equal to today. Case numbers are in column A on both sheets but I can't figure out how to reference them.

This is what I have so far. It returns no errors but is not pulling the data through either.

Sub LookUpBlankDatesSA()

    Dim wsOpenCases As Worksheet
    Dim wsSADates As Worksheet
    Dim lastRow As Long
    Dim i As Long
    
    ' Set references to the relevant sheets
    Set wsOpenCases = ThisWorkbook.Sheets("OpenCasesSummary")
    Set wsSADates = ThisWorkbook.Sheets("SA Dates")
    
    ' Find the last row in column J
    lastRow = wsOpenCases.Cells(wsOpenCases.Rows.Count, "J").End(xlUp).Row
    
    ' Loop through the rows
    For i = 4 To lastRow
        If IsEmpty(wsOpenCases.Cells(i, "J").Value) Then
            ' Check if the date in SA Dates is greater than or equal to today
            If wsSADates.Cells(i, "B").Value >= Date Then
                ' Update the value in OpenCasesSummary
                wsOpenCases.Cells(i, "J").Value = wsSADates.Cells(i, "B").Value
            End If
        End If
    Next i

End Sub

Solution

If you need to match rows based on case number:

Sub LookUpBlankDatesSA()

    Dim wsOpenCases As Worksheet, wsSADates As Worksheet
    Dim lastRow As Long, i As Long, m As Variant, c As Range, dt
    
    Set wsOpenCases = ThisWorkbook.Sheets("OpenCasesSummary")
    Set wsSADates = ThisWorkbook.Sheets("SA Dates")
    
    'Find the last row in #Col A#
    lastRow = wsOpenCases.Cells(wsOpenCases.Rows.Count, "A").End(xlUp).Row
    
    For i = 4 To lastRow ' Loop through the rows
        Set c = wsOpenCases.Cells(i, "J")
        If IsEmpty(c.Value) Then 'no date?
            'try to find the matching case number on "SA Dates"
            m = Application.Match(wsOpenCases.Cells(i, "A").Value, wsSADates.Columns("A"), 0)
            If Not IsError(m) Then                   'got a match?
                dt = wsSADates.Cells(i, "B").Value   'read the date
                If dt >= Date Then c.Value = dt       'add if > Today
            End If  'got match
        End If      'empty date
    Next i
End Sub