Array that stores values from two ranges and compares if one cell is blank

Question

This is my first Array in VBA for Excel, but I need some help to optimize the code and try to reduce the number of If statements.

The long and short of the code is that it checks to see if there is a Customer name in Column B and checks that against Column A; if there is no value in Column A then an input box opens to prompt the user to add the CIF number to the specific named range.

Cells B2 through B9 will always have a value, but not every cell B2 through B9 will be used. Cells A2 through A9 will not always have a value.

Below is the Code:

Sub CheckCIF()
    Dim fileArray
    Dim finalRow As Long
    Dim targetCol As Long


    With Sheets("Loan Data")
        finalRow = .Range("B" & Rows.Count).End(xlUp).Row
        targetCol = .Range("A1").EntireRow.Find("CIF #").Column
        fileArray = Array(.Range("B2:B" & finalRow), _
                    .Range(.Cells(2, targetCol), .Cells(finalRow, targetCol)))
    End With

    'fileArray(1)(1) Number in first parenthesis is the column and the number in the second parenthesis is the row

    If fileArray(1)(1) = vbNullString And fileArray(0)(1) <> vbNullString Then
        With Sheets("Loan Data")
            .Range("CIF_1") = InputBox("Please enter the CIF Number for " & vbCrLf & fileArray(0)(1))
        End With
    End If

    If fileArray(1)(2) = vbNullString And fileArray(0)(2) <> vbNullString Then
        With Sheets("Loan Data")
            .Range("CIF_2") = InputBox("Please enter the CIF Number for " & vbCrLf & fileArray(0)(2))
        End With
    End If

    If fileArray(1)(3) = vbNullString And fileArray(0)(3) <> vbNullString Then
        With Sheets("Loan Data")
            .Range("CIF_3") = InputBox("Please enter the CIF Number for " & vbCrLf & fileArray(0)(3))
        End With
    End If

    If fileArray(1)(4) = vbNullString And fileArray(0)(4) <> vbNullString Then
        With Sheets("Loan Data")
            .Range("CIF_4") = InputBox("Please enter the CIF Number for " & vbCrLf & fileArray(0)(4))
        End With
    End If

    If fileArray(1)(5) = vbNullString And fileArray(0)(5) <> vbNullString Then
        With Sheets("Loan Data")
            .Range("CIF_5") = InputBox("Please enter the CIF Number for " & vbCrLf & fileArray(0)(5))
        End With
    End If

    If fileArray(1)(6) = vbNullString And fileArray(0)(6) <> vbNullString Then
        With Sheets("Loan Data")
            .Range("CIF_6") = InputBox("Please enter the CIF Number for " & vbCrLf & fileArray(0)(6))
        End With
    End If

    If fileArray(1)(7) = vbNullString And fileArray(0)(7) <> vbNullString Then
        With Sheets("Loan Data")
            .Range("CIF_7") = InputBox("Please enter the CIF Number for " & vbCrLf & fileArray(0)(7))
        End With
    End If

    If fileArray(1)(8) = vbNullString And fileArray(0)(8) <> vbNullString Then
        With Sheets("Loan Data")
            .Range("CIF_8") = InputBox("Please enter the CIF Number for " & vbCrLf & fileArray(0)(8))
        End With
    End If

End Sub

Answer 1

The fileArray variable can be replaced with 2 variables. When looking at fileArray in the locals window, from the IDE menu at the top View>Locals Window, you can see that it's it a Variant/Variant(0 to 1). Expanding that by clicking on the + icon to the left of the variable name it it contains two sub arrays. fileArray(0) and fileArray(1) are both of type Variant/Object/Range. These are distinct areas that should be named respectively. customerArea for those in column B and cifArea for those in the same column as targetCol let you refer to these ranges directly.

Because these variables house information stored vertically, that is X rows tall by 1 column wide, you can reference the rows by customerArea(checkRow).Value2. Doing this allows you to write self documenting code. Rather than

If fileArray(1)(1) = vbNullString And fileArray(0)(1) <> vbNullString Then

You have

If customerArea(checkRow).Value2 = vbNullString And cifArea(checkRow).Value2 <> vbNullString Then

I'm torn between how to go about checking each of the rows. Mulling it over I decided on extracting the check into a Sub. I added a guard clause, endRow > customerArea.Rows.Count Or endRow > cifArea.Rows.Count, that raises an error if the end row exceeds the number of rows in each area that's checked. You could also add a guard clauses to ensure that startRow >= 1, or ensure that customerArea.Rows.Count = cifArea.Rows.Count or any others you deem appropriate, but that I'll leave for you.

Instead of checking each row explicitly I condensed that with a For...Next statement and used a descriptive variable checkRow to let you know which row you're checking.

Private Sub CheckForEmptyCellsIn(ByVal customerArea As Range, ByVal cifArea As Range, ByVal startRow As Long, ByVal endRow As Long)
    Const InputMessage As String = "Please enter the CIF Number for "

    Const InvalidArgument As Long = 5
    If endRow > customerArea.Rows.Count Or endRow > cifArea.Rows.Count Then
        Err.Raise InvalidArgument, "CheckForEmptyCells Sub", "endRow argument exceeded number of rows in"
    End If

    Dim checkRow As Long
    For checkRow = startRow To endRow
        If customerArea(checkRow).Value2 = vbNullString And cifArea(checkRow).Value2 <> vbNullString Then
            customerArea.Parent.Range("CIF_" & checkRow).Value2 = InputBox(InputMessage & vbCrLf & cifArea(1).Value2)
        End If
    Next
End Sub

This refactoring leaves you with

Public Sub CodeReview()
    Dim loanData As Worksheet
    Set loanData = Worksheets("Loan Data")
    Dim finalRow As Long
    finalRow = loanData.Range("B" & Rows.Count).End(xlUp).Row
    Dim targetCol As Long
    targetCol = loanData.Range("A1").EntireRow.Find("CIF #").Column

    CheckForEmptyCellsIn loanData.Range("B2:B" & finalRow), _
                        loanData.Range(loanData.Cells(2, targetCol), loanData.Cells(finalRow, targetCol)), _
                        1, 8
End Sub