Option Explicit
Public Sub CheckCIF()
    Dim fileArray
    Dim finalRow As Long
    Dim targetColumn As Long
    Dim lastColumn As Long

    With Worksheets("Loan Data")

        finalRow = .Range("A" & Rows.Count).End(xlUp).Row
        lastColumn = .Cells(1, .Columns.Count).End(xlToLeft).Column

        Dim targetRange As Range
        Dim searchRange As Range

        Set searchRange = .Range(.Cells(1, 1), .Cells(1, lastColumn))
        Set targetRange = searchRange.Find("CIF #")

        If targetRange Is Nothing Then
            Debug.Print "Column not found"
            Exit Sub
        End If

        targetColumn = targetRange.Column
        fileArray = .Range(.Cells(2, "A"), .Cells(finalRow, targetColumn)).Value

        Dim rowCounter As Long
        For rowCounter = 1 To finalRow - 1
            If fileArray(rowCounter, 2) = vbNullString And fileArray(rowCounter, 1) <> vbNullString Then
                .Range("CIF_" & rowCounter) = InputBox("Please enter the CIF Number for " & vbCrLf & fileArray(rowCounter, 1))
            End If
        Next
    End With

End Sub