VBA Using Array's to create a series of strings

[Lres81715]

I've coded myself into a corner with this one

The original code works beautifully as it searches my worksheet, finds predefined header name via Array("headername1", etc) and uses it to reference the Column letter("A") it's found at. It makes my sheet dynamic in that I can make changes with ease.

So this works and used as a working reference for my new bit of code below this

    Dim ColIndex        As Integer
    Dim counter         As Integer
    Dim HeaderFound     As Range
    Dim ReArrangeCol    As Variant

    ReArrangeCol = Array("HeaderName1", "HeaderName2", "HeaderName3", "HeaderName4", "Ect")


    counter = 1
    
    For ColIndex = LBound(ReArrangeCol) To UBound(ReArrangeCol)
    
        Set HeaderFound = Rows("1:1").find(ReArrangeCol(ColIndex), LookIn:=xlValues, LookAt:=xlWhole, _
                          SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False)
        
        If Not HeaderFound Is Nothing Then
            If HeaderFound.Column <> counter Then
                HeaderFound.EntireColumn.Cut
                Columns(counter).Insert Shift:=xlToRight
                Application.CutCopyMode = False
            End If
            counter = counter + 1
        End If
        
    Next ColIndex

So what I'm doing now is referencing and converting Header name that I find into a Column letter. (lets say Header "Mr Excel" is a header on K1. I would refer to the Mr Excel column found on K as iColLtr. So on and so forth for each Header I need to reference.)

The problem is the code to reference multiple column headers is lengthy if I have to do more than 10. So I am trying to condense the process only I'm running into a problem.

    Dim counter         As Integer
    Dim HeaderFound     As Range
    Dim ColHeaderArray  As Variant
    Dim ColLetterArray  As Variant
    Dim ndx1            As Integer

    ColHeaderArray = Array("HeaderName1", "HeaderName2", "HeaderName3", "HeaderName4", "Ect")
    ColLetterArray = Array(iColLtr, jColLtr, kColLtr, lColLtr, lColLtr)


    counter = 1
    
    For ndx1 = LBound(ColHeaderArray) To UBound(ColHeaderArray)
    
    Set HeaderFound = Rows("1:1").find(ColHeaderArray(ndx1), LookIn:=xlValues, LookAt:=xlWhole, _
                          SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False)
        If Not HeaderFound Is Nothing Then
            [COLOR=#ff0000][B][U]ColLetterArray(ndx1)[/U][/B][/COLOR] = replace(Cells(1, HeaderFound.Column).address(0, 0), 1, "")
        End If
    Next ndx1

The ColLetterArray(ndx1) in my code is not doing what I'd like it to do which is to refer to iColLtr = replace(Cells...etc) then jColLtr = replace(Cells...etc) and so on and so forth.

I know I'm close but I'm missing a key piece of the puzzle.

 

[Norie]

You can't use code to set the values of variables like that, why don't you use a dynamic(?) array for ColLetterArray?

    Dim HeaderFound     As Range
    Dim ColHeaderArray  As Variant
    Dim ColLetterArray  As Variant
    Dim ndx1            As Integer

    ColHeaderArray = Array("HeaderName1", "HeaderName2", "HeaderName3", "HeaderName4", "Ect")
    Redim ColLetterArray(LBound(ColHeaderArray) To UBound(ColHeaderArray))
    
    For ndx1 = LBound(ColHeaderArray) To UBound(ColHeaderArray)
    
    Set HeaderFound = Rows("1:1").find(ColHeaderArray(ndx1), LookIn:=xlValues, LookAt:=xlWhole, _
                          SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False)
        If Not HeaderFound Is Nothing Then
            ColLetterArray(ndx1) = replace(Cells(1, HeaderFound.Column).address(0, 0), 1, "")
        End If
    Next ndx1

You could then use something like this to find the column letter for a specific column.

Col = ColLetterArray(Application.Match("HeaderName2", ColHeaderArray,0)-1)

 

[Lres81715]

Norie,

Thanks for responding.
I sort of understand dynamic Array for ColLetterArray. I tried your suggestion with the

ReDim ColLtr(LBound(ColHeaderArray) To UBound(ColHeaderArray))

looked like I was making progress with using ColHeaderArray0, ColHeaderArray1, ColHeaderArray2, etc as reference. But alas, they end up "empty" after running it. I'm still missing that crucial piece. I'm not sure where to use

[COLOR=#333333]Col = ColLetterArray(Application.Match("HeaderName2", ColHeaderArray,0)-1)[/COLOR]

As I tried it but wasn't getting me the results I was after.

This what I currently do which works but not pretty.

    ColHeader1 = "Client Sale Date" ' Header name I need to use to dynamically ensure all reports has this format no matter where the column is at

    Set HeaderFound = Rows("1:1").find(ColHeader1, LookIn:=xlValues, LookAt:=xlWhole, _
                          SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False)
        If Not HeaderFound Is Nothing Then
            [B]iColLtr[/B] = replace(Cells(1, HeaderFound.Column).address(0, 0), 1, "")
        End If


    Range(iColLtr & ":" & iColLtr).NumberFormat = "mm/dd/yyyy" 'just a quick example of how I would use it

I'm pretty sure you know what I'm trying to do at this point. As in, make it so I don't have to copy/paste 2 pages of the above code just to change ColHeader(#) and iColLtr the 30 or so times I need to reference the header. I'm quite possibly over thinking this as this is just out of reach for me skill-wise.

 

[Norie]

To be honest I'm not 100% sure what you want to do.

I think you want to find the column letters for the headers you have in an array and then use those column letters later/elsewhere in code.

My idea was to set up a 'columns' array that is kind of parallel to the 'headers' array.

You could then easily find the column letter for a particular header as the relevant column letter will be in the same position in the column array as the header is in the header array.

Anywhere close?

 

[Lres81715]

I think you want to find the column letters for the headers you have in an array and then use those column letters later/elsewhere in code.

My idea was to set up a 'columns' array that is kind of parallel to the 'headers' array.

Yup, That's exactly what I want to do. I've got several dozen spreadsheets that pulled from a large 400+ column database and it's ever expanding. Often enough for it to become annoying, the programmers insert in new columns of data into this master spreadsheet in various regions that shift the columns out of order. Thus screwing up from a hardcoded macro.

This is the reason behind my column header reference code above.

As for setting up a 'columns' array... Hmmm, I'm probably not following you exactly but referencing column letters that change semi-frequently... not sure how that would work.

Ideally I want to set up a Call macro that would be similar to the following.

Call zHelperMacros.ColumnHeaderToColLetter("HeaderName", "ColLtr1") 'where ColLtr1 would return the reference of "K" or whatever from the Macro

Sub ColumnHeaderToColLetter(ColHeader As String, i As Variant)

    Dim ColIndex        As Integer
    Dim HeaderFound     As Range
    Dim ColLtr          As String
 
Set HeaderFound = Rows("1:1").Find(ColHeader, 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
End Sub

Obviously that doesn't work because i = Replace(Cells(1, HeaderFound.Column).Address(0, 0), 1, "") isn't working nor is it the correct method of accomplishing what I want to do. The problem is, I know just enough to get me into trouble and here on out I'm stuck.

 

[Norie]

Why not use a function?

Function ColumnHeaderToColLetter(ColHeader As String) As String
Dim HeaderFound     As Range
Dim ColLtr          As String
 
    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

    ColumnHeaderToColLetter = ColLtr

End Sub

Which would be used like this.

ColLtr1 = zHelperMacros.ColumnHeaderToColLetter("HeaderName")

PS I hope you aren't using an individual variable eg ColLtr1, ColLtr2... for each column letter.

 

[Lres81715]

Why not use a function?

Function ColumnHeaderToColLetter(ColHeader As String) As String

Which would be used like this.

ColLtr1 = zHelperMacros.ColumnHeaderToColLetter("HeaderName")

PS I hope you aren't using an individual variable eg ColLtr1, ColLtr2... for each column letter.

Never used Functions before. I'll look into it but it looks like this is pretty close to what I need.

As for individual variable... ummm, maybe? =P
In my defense, I use the individual variable for multiple times for application.function(...)'s, references to formulas and filtering calculations. My big spreadsheet called the "Exception Report" has over 100 error spotting calculations on top of numerous references to major columns that need to be fed off the information the cells provide. Example "Closing Date" column header has nearly 20 other columns that feed off the date provided. Is "Closing Value" filled in if "Closing Date" is present? Is "Shipping Scheduled" date within 2 days after "Closing Date" etc etc.

So while I currently do use it that way, I would be interested to know of a better way. Keeping in mind that this report adds and removes columns of information in various locations throughout the sheet pretty frequently and it's over 200 columns in size currently.

May or may not be important but I've never taken classes for VBA. All self taught out of necessity