Converting dynamic ranges to a table using VBA

[Fudging]

Hi,

For context, I currently have functions that analyzes a table and I'm trying to make a new table from that data. I have multiple selection dropdown menus and I want to know all selections selected per row by making the possible selections as headers and indicating 1 or null if selected or not (please refer to the image). The end goal is to get this as a table and export it to Power BI for visuals.

The issue is that the formulas spill the data and for some reason, spilled data is not compatible with tables (using spill as the original table will constantly be growing). The plan is to have a VBA script that can read a worksheet and convert the range of spilled data into a table (if that is possible), assuming that my spilled data is in a separate worksheet. The reason for this odd approach is because the initial table will be constantly growing with more inputs, meaning the range of spilled data will also grow.

To summarize, I need help to write a VBA script that can read spilled data ranges that is bound to grow and convert it to a table.

Thanks in advance for the help

 

[OaklandJim]

I hope that does what you need.

My workbook is HERE

Function FindItem(psItemsString As String, psItemToFind As String) As Variant

'   Array to hold individual item.
    Dim asStrings() As String

'   Individual item.
    Dim iStringNum As Long
    
    FindItem = ""
    
    asStrings = Split(psItemsString, ", ")
    
'   Iterate through psItemsString to look for the value in psItemToFind
    For iStringNum = 0 To UBound(asStrings)
        
        If asStrings(iStringNum) = Trim(psItemToFind) _
         Then
            FindItem = 1
            Exit For
        End If
        
    Next iStringNum

End Function

 

[OaklandJim]

Did my code work for you?