Excel VBA, array range issue

I have the below code that based on user input in cell B5 in "Main" worksheet will search the input part number in another two worksheets, "SHORTAGE" and "PPN", and return the corresponding columns and rows of data, the code works but I need to change the data range for shortageData array from columns B to F, to A to F, plus L, and N. I have researched and tried a few methods, like union range Union(promptSheet.Range("A1:F" & promptLastRow), promptSheet.Range("L1:L" & promptLastRow), promptSheet.Range("N1:N" & promptLastRow)).Value, and promptData = promptSheet.Range("A1:F" & promptLastRow & ", L1:L" & promptLastRow & ", N1:N" & promptLastRow).Value, but none works, it returns the correct values for A:F, but for the next two columns L, and N, it returns all #REF! values. Tried using chatGPT and too wasn't able to get a fix. Is anyone able to assist in this? Any help is much appreciated! Does it have to do with non-contiguous range?

Sub Button2_Click()
Dim partNum As String
Dim mainSheet As Worksheet
Dim shortageSheet As Worksheet
Dim ppnSheet As Worksheet
Dim mainLastRow As Long
Dim shortageLastRow As Long
Dim ppnLastRow As Long
Dim shortageData As Variant
Dim ppnData As Variant
Dim i As Long
Dim recordFound As Boolean
Application.ScreenUpdating = False
Set mainSheet = ThisWorkbook.Sheets("Main")
Set shortageSheet = ThisWorkbook.Sheets("SHORTAGE")
Set ppnSheet = ThisWorkbook.Sheets("PPN")
partNum = mainSheet.Range("B5").Value
If partNum = "" Then
    MsgBox "Please enter a part number.", vbExclamation
    Exit Sub
End If
mainSheet.Range("B11:F" & mainSheet.Cells(mainSheet.Rows.Count, "B").End(xlDown).Row).ClearContents
mainSheet.Range("I11:O" & mainSheet.Cells(mainSheet.Rows.Count, "I").End(xlDown).Row).ClearContents
shortageLastRow = shortageSheet.Cells(shortageSheet.Rows.Count, "B").End(xlUp).Row
shortageData = shortageSheet.Range("B1:F" & shortageLastRow).Value
For i = 1 To shortageLastRow
    If shortageData(i, 1) = partNum Then
        mainLastRow = mainSheet.Cells(mainSheet.Rows.Count, "B").End(xlUp).Row + 1
        mainSheet.Range("B" & mainLastRow & ":I" & mainLastRow).Value = _
            Application.Index(shortageData, i, Array(1, 2, 3, 4, 5))
        recordFound = True
    End If
Next i
If Not recordFound Then
    MsgBox "No records found in SHORTAGE"
End If
recordFound = False
ppnLastRow = ppnSheet.Cells(ppnSheet.Rows.Count, "G").End(xlUp).Row
ppnData = ppnSheet.Range("G1:N" & ppnLastRow).Value
For i = 1 To ppnLastRow
    If ppnData(i, 1) = partNum Then
        mainLastRow = mainSheet.Cells(mainSheet.Rows.Count, "I").End(xlUp).Row + 1
        mainSheet.Range("I" & mainLastRow & ":O" & mainLastRow).Value = _
            Application.Index(ppnData, i, Array(1, 2, 3, 4, 5, 6, 7, 8))
        recordFound = True
    End If
Next i
If Not recordFound Then
    MsgBox "No records found in PPN"
End If
Application.CutCopyMode = False
Application.ScreenUpdating = True
MsgBox "Search complete!", vbInformation
End Sub

This function is quite long and complicated, so I would start with refactoring it. First of all, you should extract two functions like this:

Option Explicit

Public Sub Button2_Click()
    Application.ScreenUpdating = False
    
    Dim mainSheet As Worksheet: Set mainSheet = ThisWorkbook.Sheets("Main")
    mainSheet.Range("B11:F" & mainSheet.Cells(mainSheet.Rows.Count, "B").End(xlDown).Row).ClearContents
    mainSheet.Range("I11:O" & mainSheet.Cells(mainSheet.Rows.Count, "I").End(xlDown).Row).ClearContents
    Dim partNum As String: partNum = mainSheet.Range("B5").Value
    If partNum = "" Then
        MsgBox "Please enter a part number.", vbExclamation
        Exit Sub
    End If
    
    SearchShortage mainSheet, partNum
    
    SearchPpn mainSheet, partNum
    
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    MsgBox "Search complete!", vbInformation
End Sub

Public Sub SearchShortage(mainSheet As Worksheet, partNum As String)
    Dim shortageSheet As Worksheet: Set shortageSheet = ThisWorkbook.Sheets("SHORTAGE")
    Dim shortageLastRow As Long: shortageLastRow = shortageSheet.Cells(shortageSheet.Rows.Count, "B").End(xlUp).Row
    Dim shortageData As Variant: shortageData = shortageSheet.Range("B1:F" & shortageLastRow).Value
    Dim recordFound As Boolean: recordFound = False
    Dim i As Long: For i = 1 To shortageLastRow
        If shortageData(i, 1).Value = partNum Then
            Dim mainLastRow As Long: mainLastRow = mainSheet.Cells(mainSheet.Rows.Count, "B").End(xlUp).Row + 1
            mainSheet.Range("B" & mainLastRow & ":I" & mainLastRow).Value = _
                WorksheetFunction.Index(shortageData, i, Array(1, 2, 3, 4, 5))
            recordFound = True
        End If
    Next i
    If Not recordFound Then
        MsgBox "No records found in SHORTAGE"
    End If
End Sub

Public Sub SearchPpn(mainSheet As Worksheet, partNum As String)
    Dim ppnSheet As Worksheet: Set ppnSheet = ThisWorkbook.Sheets("PPN")
    Dim ppnLastRow As Long: ppnLastRow = ppnSheet.Cells(ppnSheet.Rows.Count, "G").End(xlUp).Row
    Dim ppnData As Variant: ppnData = ppnSheet.Range("G1:N" & ppnLastRow).Value
    Dim recordFound As Boolean: recordFound = False
    Dim i As Long: For i = 1 To ppnLastRow
        If ppnData(i, 1).Value = partNum Then
            Dim mainLastRow As Long: mainLastRow = mainSheet.Cells(mainSheet.Rows.Count, "I").End(xlUp).Row + 1
            mainSheet.Range("I" & mainLastRow & ":O" & mainLastRow).Value = _
                WorksheetFunction.Index(ppnData, i, Array(1, 2, 3, 4, 5, 6, 7, 8))
            recordFound = True
        End If
    Next i
    If Not recordFound Then
        MsgBox "No records found in PPN"
    End If
End Sub

Then, I would go on with finding similarities between SearchShortage and SearchPpn and creating a more abstract function, which is called with different parameters. However, since I do not have your workbook, I cannot test any modification. And I see some inconsistencies that may cause error (e.g. mainSheet's B:I range (8 columns) is overwritten with 5 values, while its I:O range (7 columns) is overwritten with 8 values). So I 'll skip this part.

Next, I'd like to understand your problem exactly. Based on what you wrote, I think you would like to add a function, let's call it SearchPrompt that has a similar task, like this:

Public Sub Searchprompt(mainSheet As Worksheet, partNum As String)
    Dim promptSheet As Worksheet: Set promptSheet = ThisWorkbook.Sheets("PROMPT")
    Dim promptLastRow As Long: promptLastRow = promptSheet.Cells(promptSheet.Rows.Count, "B").End(xlUp).Row
    Dim promptData As Variant: promptData = Union(promptSheet.Range("A1:F" & promptLastRow), promptSheet.Range("L1:L" & promptLastRow), promptSheet.Range("N1:N" & promptLastRow)).Value,
    Dim recordFound As Boolean: recordFound = False
    Dim i As Long: For i = 1 To promptLastRow
        If promptData(i, 1).Value = partNum Then
            Dim mainLastRow As Long: mainLastRow = mainSheet.Cells(mainSheet.Rows.Count, "S").End(xlUp).Row + 1
            mainSheet.Range("S" & mainLastRow & ":Z" & mainLastRow).Value = _
                WorksheetFunction.Index(promptData, i, Array(1, 2, 3, 4, 5, 6, 7, 8))
            recordFound = True
        End If
    Next i
    If Not recordFound Then
        MsgBox "No records found in prompt"
    End If
End Sub

And the problem is that the first six cells (in the above example, S:X as I guessed, that B:F is for Shortage, then 2 empty columns, I:P for PPN, then 2 empty columns, and finally S:Z for prompt) are filled with the correct values, while the last two (Y:Z) are filled with #REF!.

You have already correctly noted that this may be caused by the non-contiguous range. I suppose that is the case, Union creates a Range object that has multiple "subranges" in its .Areas property, and if you refer to its .Value, then it is interpreted as .Areas(1).Value, and the rest (e.g. .Areas(2) is ignored).

Now there are two solutions:

(1) Iterate over these .Areas in your code - this is a little bit more difficult than the other solution, and I do not know enough about your worksheet to make a good guess about it.

(2) Simply use a contiguous range. You can search in the range A:N, and then later (when presenting the results) omit the cells that you don't need. You can do that by customizing the numbers in the array supplied as the third parameter of Application.Index (more correctly, WorksheetFunction.Index), like this:

Public Sub Searchprompt(mainSheet As Worksheet, partNum As String)
    Dim promptSheet As Worksheet: Set promptSheet = ThisWorkbook.Sheets("PROMPT")
    Dim promptLastRow As Long: promptLastRow = promptSheet.Cells(promptSheet.Rows.Count, "B").End(xlUp).Row
    Dim promptData As Variant: promptData = promptSheet.Range("A1:N" & promptLastRow).Value
    Dim recordFound As Boolean: recordFound = False
    Dim i As Long: For i = 1 To promptLastRow
        If promptData(i, 1).Value = partNum Then
            Dim mainLastRow As Long: mainLastRow = mainSheet.Cells(mainSheet.Rows.Count, "S").End(xlUp).Row + 1
            mainSheet.Range("S" & mainLastRow & ":X" & mainLastRow).Value = _
                WorksheetFunction.Index(promptData, i, Array(1, 2, 3, 4, 5, 6, 12, 14))
            recordFound = True
        End If
    Next i
    If Not recordFound Then
        MsgBox "No records found in prompt"
    End If
End Sub

Note that WorksheetFunction.Index is called with Array(1, 2, 3, 4, 5, 6, 12, 14): 1 .. 6 refer to A .. F, 12 to L and 14 to N.