If Columns C cells have value, then write "OK" in Column C Sheet 2

Question

I want to add text to Column C Sheet 2 with "OK", IF there is a value in Column C Sheet 1.

Here is an example of the data on Sheet 1:

NIP	Name	Graduated
8593	Ariana	04/12/2023
8594	Mike
8595	Harry	08/20/2023

Data on Sheet 2:

NIP	Name	Recommendation
8593	Ariana
8595	Harry
8596	John

There should be an index match function to column A on both sheets, because the sheets don't contain the same lists. And I wish to make it dynamic range.

Here is my current code but it gave runtime type mismatch

Dim lw As Long
lw = sws.Cells(sws.Rows.Count, "A").End(xlUp).Row

Dim p As Long
p = sws.Range("C" & Rows.Count).End(xlDown).Rows
Dim u As Long

For u = 2 To lw
    If p <> 0 Then
        dws.Cells(i, "C").Value = "OK"
    End If
Next u

Answer 1

Try this:

Base sheet is Sheet1 Recom sheet is Sheet2

Sub acar()
 Set sws = Worksheets("Base")
 Set dws = Worksheets("recom")
 
 Dim lw As Long, p As Long, u As Long
    lw = sws.Cells(sws.rows.Count, "A").End(xlUp).Row
    
    p = dws.Range("A" & rows.Count).End(xlUp).Row
    
    For u = 2 To p
        Set v = sws.Range("A2:A" & lw).Find(dws.Range("A" & u), , xlFormulas, xlWhole)
        If Not v Is Nothing Then
            If sws.Range("C" & v.Row) <> "" Then
              dws.Cells(u, "C").Value = "OK"
            Else
              dws.Cells(u, "C").Value = ""
            End If
        End If
    Next u
End Sub

Answer 2

• Loading data into an array and populating the output all at once to improve efficiency

Microsoft documentation:

Dictionary object

Range.CurrentRegion property (Excel)

Application.Union method (Excel)

Option Explicit
Sub Demo()
    Dim objDic As Object, arrData
    Dim i As Long, sKey As String, rngOK As Range
    Dim oSht1 As Worksheet, oSht2 As Worksheet
    Set oSht1 = Sheets("Sheet1") ' modify as needed
    Set oSht2 = Sheets("Sheet2")
    Set objDic = CreateObject("scripting.dictionary")
    arrData = oSht1.Range("A1").CurrentRegion.Value
    For i = LBound(arrData) + 1 To UBound(arrData)
        If Len(arrData(i, 3)) > 0 Then
            sKey = arrData(i, 1) & "|" & arrData(i, 2)
            objDic(sKey) = ""
        End If
    Next i
    arrData = oSht2.Range("A1").CurrentRegion.Value
    For i = LBound(arrData) + 1 To UBound(arrData)
        sKey = arrData(i, 1) & "|" & arrData(i, 2)
        If objDic.exists(sKey) Then
            If rngOK Is Nothing Then
                Set rngOK = oSht2.Cells(i, 3)
            Else
                Set rngOK = Application.Union(rngOK, oSht2.Cells(i, 3))
            End If
        End If
    Next i
    If Not rngOK Is Nothing Then
        rngOK.Value = "OK"
    End If
End Sub