Better Way Than Using Dictionaries?

[Snort]

I've got a macro for the purpose of standardizing column headings on data received from numerous different sources. I'm using a different dictionary for each relevant column, and it works great, but I'm curious if there's a better / different way of doing it?

Here's a snippet of the macro capped at 5 entries per dictionary for this example. There's many more dictionaries and entries in the dictionaries than displayed here - I'm just looking to see if there's a better way.

'**********************************************
Dim AgencyName As New Scripting.Dictionary
AgencyName.CompareMode = TextCompare

AgencyName.Add "ProvName", 1
AgencyName.Add "FACIL_NM", 1
AgencyName.Add "Svc Prov Org Name", 1
AgencyName.Add "Serv Group Practice Name", 1
AgencyName.Add "Billing Prov LName", 1
'***********************************************
'*********************************************
Dim AgencyProviderNumber As New Scripting.Dictionary
AgencyProviderNumber.CompareMode = TextCompare

AgencyProviderNumber.Add "PayToProviderNumber", 1
AgencyProviderNumber.Add "clinic_npi", 1

'**********************************************
'**********************************************
Dim BilledAmount As New Scripting.Dictionary
BilledAmount.CompareMode = TextCompare

BilledAmount.Add "SUBMITTED", 1
BilledAmount.Add "Submitted Charge", 1
BilledAmount.Add "billed_amt", 1
BilledAmount.Add "Billed Amt", 1
BilledAmount.Add "BILLED AMOUNT", 1
'***********************************************

Range("A1").Select

For X = 1 To LastColumn

    Z = Cells(1, X)
        
    If xRecipID.Exists(Z) Then
        ActiveCell.Value = "Recipient ID"
            
    ElseIf AgencyName.Exists(Z) Then
        ActiveCell.Value = "Agency Name"
        
    ElseIf AgencyProviderNumber.Exists(Z) Then
        ActiveCell.Value = "Agency Provider Number"
        
    ElseIf AgencyType.Exists(Z) Then
        ActiveCell.Value = "Agency Type"
        
    ElseIf BilledAmount.Exists(Z) Then
        ActiveCell.Value = "Billed Amount"
        
    End If
Next X

 

[rlv01]

Not tested

    AgencyName = "ProvName$!FACIL_NM$!Svc Prov Org Name$!Serv Group Practice Name$!Billing Prov LName$!"
    AgencyProviderNumber = "PayToProviderNumber$!clinic_npi$!"
    BilledAmount = "SUBMITTED$!Submitted Charge$!billed_amt$!Billed Amt$!BILLED AMOUNT$!"

    Range("A1").Select

    For X = 1 To LastColumn
        Z = Cells(1, X)

        If InStr(AgencyName, Z & "$!") > 0 Then
            ActiveCell.Value = "Agency Name"

        ElseIf InStr(AgencyProviderNumber, Z & "$!") > 0 Then
            ActiveCell.Value = "Agency Provider Number"

        ElseIf InStr(BilledAmount, Z & "$!") > 0 Then
            ActiveCell.Value = "Billed Amount"
        Else
            ActiveCell.Value = "n/a"
        End If
    Next X

 

[Akuini]

My suggestion is to put the data in a sheet instead of in the macros, like this:

Book1
	A	B	C
1	AgencyName	AgencyProviderNumber	BilledAmount
2	ProvName	PayToProviderNumber	SUBMITTED
3	FACIL_NM	clinic_npi	Submitted Charge
4	Svc Prov Org Name		billed_amt
5	Serv Group Practice Name		Billed Amt
6	Billing Prov LName		BILLED AMOUNT
Sheet2

Then, use the Find method to retrieve the correct header. I can help you with the code, but I don't understand your code in this part:
Range("A1").Select .. and then in the loop ... ActiveCell.Value = ....
but you didn't activate any other cell in the process, so Range("A1") is always the ActiveCell.
Could you please provide an example along with the expected result?

 

[Snort]

I don't understand your code in this part:
Range("A1").Select .. and then in the loop ... ActiveCell.Value = ....
but you didn't activate any other cell in the process, so Range("A1") is always the ActiveCell.

The Range("A1").Select is to ensure that the active cell was A1. Sounds like it's not necessary.

The ActiveCell.Value stuff is a whoops on my end. The code was a Do Until loop where I activated each cell, and just yesterday I learned how to change this into a For Loop. Hadn't fully updated the code when I copy pasted as you can tell from my post.

Thanks for your idea about the sheet instead of dictionaries. I'll give it some thought and experiment some on my end to see which way I like better - dictionaries or the sheet.

Have a great day!

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.

 

[rlv01]

I don't see anything inherently bad or inefficient about using dictionaries. You could also just change your existing code minimally to load the dictionaries from the worksheet location.

    Dim rng As Range
    Dim rngCol As Range
    Dim RC As Range, RR As Range
    
    'Populate dictionaries from worksheet definitions.
    With Worksheets("Sheet2") 'per @Akuini's example
        Set rngCol = .Range(.Cells(1, 1), .Cells(1, .Columns.Count).End(xlToLeft))
        For Each RC In rngCol
            Set rng = .Range(.Cells(2, RC.Column), .Cells(.Rows.Count, RC.Column).End(xlUp))
            For Each RR In rng
                Select Case RC.Value
                    Case "AgencyName"
                        AgencyName.Add RR.Value, 1
                    Case "AgencyProviderNumber"
                        AgencyProviderNumber.Add RR.Value, 1
                    Case "BilledAmount"
                        BilledAmount.Add RR.Value, 1
                End Select
            Next RR
        Next RC
    End With