CODEWITHSUNDEEP
excelvba
answerSeeker
answerSeeker

Reputation: 2772

Create a dictionary from excel named columns as Keys

The columns that I'm using are from E to BH but they are named. For example, Column e is named "problems". I am trying to create a dictionary from a cell value which will act as a primary key in order to query the ranges from E to BH.

For example, if I have a cell with value "Late payment" located on E6, I want to be able to get the ranges from columns E6 to Bh6 and then be able to query the other columns like "Summary" which is on column AQ and be able to get the related summary which is on AQ6.

I have been thinking how to approach this problem, I think a dictionary of the wanted range would be the best idea, because of the {Key, value} pair data set where a named column can be a key the value will be what's in the cell and I want to be able to know the other values on a column like summary for example.

Below is what I'm doing for now to get the other values. I don't have much experience in vba and only started using it a week ago for work.

Set cdata = Worksheets("Table")
Dim newRange As Range
dim keyWord as String
KeyWord = "Late payment"
Dim Summary as String


Set rng1 = cdata.Cells.Find(What:=KeyWord, LookAt:=xlWhole)
If Not rng1 Is Nothing Then
    'Sets the range based on cell value
    Set newRange = ccdata.Range("E" & rng1.Row & ":BH" & rng1.Row)
End If

'Gets value and sets column from row number
Summary = cdata.Range("$AQ$" & rng1.Row).Value

Upvotes: 0

Views: 1131

Answers (1)

Warcupine
Warcupine

Reputation: 4640

This should do what you're looking for. I included the header in the array to keep the index = row.

You'll also have to be aware of the fact that creating an array from a range creates a 2d array so it will always be arr(row, 1). The 1 won't change because each range consists of 1 column.

    Dim dict As Object
    Dim lc As Long
    Dim lr As Long
    Dim i As Long
    Dim arr As Variant
    
    Set dict = CreateObject("Scripting.Dictionary") 'Add Microsoft Scripting Runtime to early-bind
    
    With Sheets("Sheet5") 'Change as needed
        lc = .Cells(1, .Columns.Count).End(xlToLeft).Column
        For i = 5 To lc
            lr = .Cells(.Rows.Count, i).End(xlUp).Row
            arr = .Range(.Cells(1, i), .Cells(lr, i)).Value
            dict.Add .Cells(1, i).Value, arr
        Next i
    End With
    
    'return the value like this: dict("problems")(6, 1)

Upvotes: 3

Related Questions