VBA Referencing a Header name to use as Column Letter

[Lres81715]

Good afternoon all,

Background:
I have a Spreadsheet that is the bane of my existence but it is the one of the most useful spreadsheet our staff has. It's an exception report that has 290+ Columns of data, 80 columns of Formula Calculations and can't/shouldn't be split up.

The problem is that columns of data get changed, switched out, removed or just plain added on a frequent basis. The formulas I have in place have to be changed within the VBA code constantly. Example: On Monday a formula would be "=SUM(CW2,JI2") and on Friday it could be "=SUM(CW2,AZ2)" because one manager wants to see "Seller's Price" Column moved closer up on the report this time around.

My simple yet effective work around to this is an extremely simple bit of code

    Dim HeaderFound     As Range    Dim ColHeader       As String
    Dim ColLtr          As String


    ColHeader = "Seller's Price"


            Set HeaderFound = Rows("1:1").find(ColHeader, LookIn:=xlValues, LookAt:=xlWhole, _
                                  SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False)
            If Not HeaderFound Is Nothing Then
                ColLtr = replace(Cells(1, HeaderFound.Column).Address(0, 0), 1, "")
            End If

This means I can use "=SUM(CW2," & ColLtr & 2 & ")". I realize it's crude but it works.

What I would like to incorporate this bit of code into 70 of the most commonly used columns of data and make them references.

The Problem:

I tried to mass produce this process by replacing the ColHeader("Seller's Price") and ColLtr(cl01) respectively into arrays but it didn't work. The ColLtr array appears to be the issue because I can't make the contents within the array into a usable string. Using my text example above, cl01 is Empty.. not the column letter AZ like I intended.

Can anyone else steer me in the right direction?

Sub ColNameToColLetter()



    Dim HeaderName()    As Variant
    Dim ColumnLetter()  As String


HeaderName = Array("Asset Mgr Full Name", "File Mgr Full Name", "Property Status", "Dt From Client", "Reo Setup Date", _
                    "Vacant Date (P)", "Title FC Deed Recorded", "Redemption Start", "Redemption Expires Date", "FC Due Date", _
                    "MI Cert No", "BPO Ordered", "BPO Complete", "BPO Upd Ordered", "BPO Upd Comp", "BPO2 Ordered", "BPO2 Complete", _
                    "Other BPO 3 Date", "Other BPO 4 Date", "Other BPO 5 Date", "Appr Ordered", "Appr Complete", "Appr Value", _
                    "Appr Upd Ordered", "Appr Upd Compl", "Appr Upd Value", "MI Recvd Amount", "Appr Name", "Other APR 3 Date", _
                    "Other APR 3 Value", "Other APR 4 Date", "Other APR 4 Value", "Other APR 5 Date", "Other APR 5 Value", "Contract DT (U)", _
                    "Sale Price", "Due to Close", "Exten'd Close", "Actual Close (C)", "Final Settlement Signed", "CD/HUD Settle Charges", _
                    "Dt HUD Apvd SP 95-99 Apprsl (CF-In) R-37", "Cash to Seller", "CD/HUD Gross Amt Seller", "CD/HUD Total Commision", _
                    "List Comm %", "Sell Comm %", "FC Type", "Advances Accrued", "Loan Paid in Full Date", "Portfolio", "FC Sale Date", _
                    "FC Interest Rate", "OrigLP", "Evict Complete", "Listing Expire Dt", "Revised Expire Dt", "Listing Signed Dt", _
                    "LastOfferDate", "Servicer Loan", "Client ID", "Agent Registration Expires", "License Exp Dt", "EO Exp Dt", _
                    "Insurance Exp Dt", "Unit 1 CFK Offered", "Unit 1 CFK Accepted Date", "Unit 1 CFK Accepted", "Unit 1 CFK Vacate Dt", _
                    "Unit 1 CFK Complete")
                    
ColumnLetter = Split("cl01,cl02,cl03,cl04,cl05," & _
                    "cl06,cl07,cl08,cl09,cl10," & _
                    "cl11,cl12,cl13,cl14,cl15,cl16,cl17," & _
                    "cl18,cl19,cl20,cl21,cl24,cl23," & _
                    "cl24,cl25,cl26,cl27,cl28,cl29," & _
                    "cl30,cl31,cl32,cl33,cl34,cl35," & _
                    "cl36,cl37,cl38,cl39,cl40,cl41," & _
                    "cl42,cl43,cl44,cl45," & _
                    "cl46,cl47,cl48,cl49,cl50,cl51,cl52," & _
                    "cl53,cl54,cl55,cl56,cl57,cl58," & _
                    "cl59,cl60,cl61,cl62,cl63,cl64," & _
                    "cl65,cl66,cl67,cl68,cl69," & _
                    "cl70", ",")


'    cl1 = "Asset Mgr Full Name"
'    cl2 = "File Mgr Full Name"
'    cl3 = "Property Status"
'    cl4 = "Dt From Client"
'    cl5 = "Reo Setup Date"
'    cl6 = "Vacant Date (P)"
'    cl7 = "Title FC Deed Recorded"
'    cl8 = "Redemption Start"
'    cl9 = "Redemption Expires Date"
'    cl10 = "FC Due Date"
'    cl11 = "MI Cert No"
'    cl12 = "BPO Ordered"
'    cl13 = "BPO Complete"
'    cl14 = "BPO Upd Ordered"
'    cl15 = "BPO Upd Comp"
'    cl16 = "BPO2 Ordered"
'    cl17 = "BPO2 Complete"
'    cl18 = "Other BPO 3 Date"
'    cl19 = "Other BPO 4 Date"
'    cl20 = "Other BPO 5 Date"
'    cl21 = "Appr Ordered"
'    cl22 = "Appr Complete"
'    cl23 = "Appr Value"
'    cl24 = "Appr Upd Ordered"
'    cl25 = "Appr Upd Compl"
'    cl26 = "Appr Upd Value"
'    cl27 = "MI Recvd Amount"
'    cl28 = "Appr Name"
'    cl29 = "Other APR 3 Date"
'    cl30 = "Other APR 3 Value"
'    cl31 = "Other APR 4 Date"
'    cl32 = "Other APR 4 Value"
'    cl33 = "Other APR 5 Date"
'    cl34 = "Other APR 5 Value"
'    cl35 = "Contract DT (U)"
'    cl36 = "Sale Price"
'    cl37 = "Due to Close"
'    cl38 = "Exten'd Close"
'    cl39 = "Actual Close (C)"
'    cl40 = "Final Settlement Signed"
'    cl41 = "CD/HUD Settle Charges"
'    cl42 = "Dt HUD Apvd SP 95-99 Apprsl (CF-In) R-37"
'    cl43 = "Cash to Seller"
'    cl44 = "CD/HUD Gross Amt Seller"
'    cl45 = "CD/HUD Total Commision"
'    cl46 = "List Comm %"
'    cl47 = "Sell Comm %"
'    cl48 = "FC Type"
'    cl49 = "Advances Accrued"
'    cl50 = "Loan Paid in Full Date"
'    cl51 = "Portfolio"
'    cl52 = "FC Sale Date"
'    cl53 = "FC Interest Rate"
'    cl54 = "OrigLP"
'    cl55 = "Evict Complete"
'    cl56 = "Listing Expire Dt"
'    cl57 = "Revised Expire Dt"
'    cl58 = "Listing Signed Dt"
'    cl59 = "LastOfferDate"
'    cl60 = "Servicer Loan"
'    cl61 = "Client ID"
'    cl62 = "Agent Registration Expires"
'    cl63 = "License Exp Dt"
'    cl64 = "EO Exp Dt"
'    cl65 = "Insurance Exp Dt"
'    cl66 = "Unit 1 CFK Offered"
'    cl67 = "Unit 1 CFK Accepted Date"
'    cl68 = "Unit 1 CFK Accepted"
'    cl69 = "Unit 1 CFK Vacate Dt"
'    cl70 = "Unit 1 CFK Complete"


'    Dim HeaderFound     As Range
'    Dim ColHeader       As String
'    Dim ColLtr          As String
'
'    ColHeader = "Seller's Price"
'
'            Set HeaderFound = Rows("1:1").find(ColHeader, LookIn:=xlValues, LookAt:=xlWhole, _
'                                  SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False)
'            If Not HeaderFound Is Nothing Then
'                ColLtr = replace(Cells(1, HeaderFound.Column).Address(0, 0), 1, "")
'            End If


    For i = LBound(ColumnLetter) To UBound(ColumnLetter)
        For j = LBound(HeaderName) To UBound(HeaderName)
            Set HeaderFound = Rows("1:1").find(j, LookIn:=xlValues, LookAt:=xlWhole, _
                                  SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False)
            If Not HeaderFound Is Nothing Then
                i = replace(Cells(1, HeaderFound.Column).Address(0, 0), 1, "")
            End If
        Next
    Next


MsgBox cl70


End Sub

Worksheet to test it out on. https://app.box.com/s/dlm2vkxtg2aeqs55z71pwcvwm35qni21

 

[Jerry Sullivan]

Hi Lres81715,

Have you considered using an Excel Table (a Listobject) instead of a simple range? That would allow you to use structured references in your formulas based on the field names without the use of VBA or lookup formulas.

 

[Norie]

The values in the array ColumnLetter are just strings, they aren't variables.

So if you have something like this,

MsgBox cl70

you will see a blank textbox as VBA sees cl70 as an undeclared variable with no value.

If you really want the column letters for each header try this.

Sub ColNameToColLetter()
Dim HeaderFound As Range
Dim HeaderName() As Variant
Dim ColumnLetter() As String
Dim cnt As Long
Dim I As Long


    HeaderName = Array("Asset Mgr Full Name", "File Mgr Full Name", "Property Status", "Dt From Client", "Reo Setup Date", _
                       "Vacant Date (P)", "Title FC Deed Recorded", "Redemption Start", "Redemption Expires Date", "FC Due Date", _
                       "MI Cert No", "BPO Ordered", "BPO Complete", "BPO Upd Ordered", "BPO Upd Comp", "BPO2 Ordered", "BPO2 Complete", _
                       "Other BPO 3 Date", "Other BPO 4 Date", "Other BPO 5 Date", "Appr Ordered", "Appr Complete", "Appr Value", _
                       "Appr Upd Ordered", "Appr Upd Compl", "Appr Upd Value", "MI Recvd Amount", "Appr Name", "Other APR 3 Date", _
                       "Other APR 3 Value", "Other APR 4 Date", "Other APR 4 Value", "Other APR 5 Date", "Other APR 5 Value", "Contract DT (U)", _
                       "Sale Price", "Due to Close", "Exten'd Close", "Actual Close (C)", "Final Settlement Signed", "CD/HUD Settle Charges", _
                       "Dt HUD Apvd SP 95-99 Apprsl (CF-In) R-37", "Cash to Seller", "CD/HUD Gross Amt Seller", "CD/HUD Total Commision", _
                       "List Comm %", "Sell Comm %", "FC Type", "Advances Accrued", "Loan Paid in Full Date", "Portfolio", "FC Sale Date", _
                       "FC Interest Rate", "OrigLP", "Evict Complete", "Listing Expire Dt", "Revised Expire Dt", "Listing Signed Dt", _
                       "LastOfferDate", "Servicer Loan", "Client ID", "Agent Registration Expires", "License Exp Dt", "EO Exp Dt", _
                       "Insurance Exp Dt", "Unit 1 CFK Offered", "Unit 1 CFK Accepted Date", "Unit 1 CFK Accepted", "Unit 1 CFK Vacate Dt", _
                       "Unit 1 CFK Complete")
                       
    Range("A1").Resize(, UBound(HeaderName)).Value = HeaderName


    '    cl1 = "Asset Mgr Full Name"
    '    cl2 = "File Mgr Full Name"
    '    cl3 = "Property Status"
    '    cl4 = "Dt From Client"
    '    cl5 = "Reo Setup Date"
    '    cl6 = "Vacant Date (P)"
    '    cl7 = "Title FC Deed Recorded"
    '    cl8 = "Redemption Start"
    '    cl9 = "Redemption Expires Date"
    '    cl10 = "FC Due Date"
    '    cl11 = "MI Cert No"
    '    cl12 = "BPO Ordered"
    '    cl13 = "BPO Complete"
    '    cl14 = "BPO Upd Ordered"
    '    cl15 = "BPO Upd Comp"
    '    cl16 = "BPO2 Ordered"
    '    cl17 = "BPO2 Complete"
    '    cl18 = "Other BPO 3 Date"
    '    cl19 = "Other BPO 4 Date"
    '    cl20 = "Other BPO 5 Date"
    '    cl21 = "Appr Ordered"
    '    cl22 = "Appr Complete"
    '    cl23 = "Appr Value"
    '    cl24 = "Appr Upd Ordered"
    '    cl25 = "Appr Upd Compl"
    '    cl26 = "Appr Upd Value"
    '    cl27 = "MI Recvd Amount"
    '    cl28 = "Appr Name"
    '    cl29 = "Other APR 3 Date"
    '    cl30 = "Other APR 3 Value"
    '    cl31 = "Other APR 4 Date"
    '    cl32 = "Other APR 4 Value"
    '    cl33 = "Other APR 5 Date"
    '    cl34 = "Other APR 5 Value"
    '    cl35 = "Contract DT (U)"
    '    cl36 = "Sale Price"
    '    cl37 = "Due to Close"
    '    cl38 = "Exten'd Close"
    '    cl39 = "Actual Close (C)"
    '    cl40 = "Final Settlement Signed"
    '    cl41 = "CD/HUD Settle Charges"
    '    cl42 = "Dt HUD Apvd SP 95-99 Apprsl (CF-In) R-37"
    '    cl43 = "Cash to Seller"
    '    cl44 = "CD/HUD Gross Amt Seller"
    '    cl45 = "CD/HUD Total Commision"
    '    cl46 = "List Comm %"
    '    cl47 = "Sell Comm %"
    '    cl48 = "FC Type"
    '    cl49 = "Advances Accrued"
    '    cl50 = "Loan Paid in Full Date"
    '    cl51 = "Portfolio"
    '    cl52 = "FC Sale Date"
    '    cl53 = "FC Interest Rate"
    '    cl54 = "OrigLP"
    '    cl55 = "Evict Complete"
    '    cl56 = "Listing Expire Dt"
    '    cl57 = "Revised Expire Dt"
    '    cl58 = "Listing Signed Dt"
    '    cl59 = "LastOfferDate"
    '    cl60 = "Servicer Loan"
    '    cl61 = "Client ID"
    '    cl62 = "Agent Registration Expires"
    '    cl63 = "License Exp Dt"
    '    cl64 = "EO Exp Dt"
    '    cl65 = "Insurance Exp Dt"
    '    cl66 = "Unit 1 CFK Offered"
    '    cl67 = "Unit 1 CFK Accepted Date"
    '    cl68 = "Unit 1 CFK Accepted"
    '    cl69 = "Unit 1 CFK Vacate Dt"
    '    cl70 = "Unit 1 CFK Complete"


    '    Dim HeaderFound     As Range
    '    Dim ColHeader       As String
    '    Dim ColLtr          As String
    '
    '    ColHeader = "Seller's Price"
    '
    '            Set HeaderFound = Rows("1:1").find(ColHeader, LookIn:=xlValues, LookAt:=xlWhole, _
                 '                                  SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False)
    '            If Not HeaderFound Is Nothing Then
    '                ColLtr = replace(Cells(1, HeaderFound.Column).Address(0, 0), 1, "")
    '            End If



    For I = LBound(HeaderName) To UBound(HeaderName)
        Set HeaderFound = Rows("1:1").Find(HeaderName(I), LookIn:=xlValues, LookAt:=xlWhole, _
                                           SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False)
        If Not HeaderFound Is Nothing Then
            ReDim Preserve ColumnLetter(cnt)
            ColumnLetter(cnt) = Replace(Cells(1, HeaderFound.Column).Address(0, 0), 1, "")
            cnt = cnt + 1
        End If
    Next I

    MsgBox ColumnLetter(cnt - 1)

End Sub

PS Have you considered using R1C1 notation for your formulas? With R1C1 you use the column number, not the column letter.