Using Excel VBA to find a range

Question

I have been given some workbooks which are formatted for the eye rather than anything functional. As such, I have quite a few column headings that look like this:

  A             B         C         D      E
1               'YTD      'Monthly  'YTD   'Monthly
2               Figures'  Figures'  Plan'  Plan'
3  
4  Account1     1         3         5      7
5  Account2     2         4         6      8

What I have been doing is using .Find to identify the row for 'Account 1' and then the column heading for 'Monthly Plan', and copying that cell value in an array. However, there are several areas (such as my example above) where I cannot easily find the column E, because searching for either 'Monthly' or 'Plan' obviously gives me unreliable results.

So far I have been using Do... While... Loop in order to find a cell address for 'Monthly', then checking the cell value immediately below for the word 'Plan', and looping using .FindNext until there is a match.

This isn't very elegant - so is there a way I can search for a dummy array/range with the arrangement of words I am looking for?

Answer 1

What if you find the concatenation of one cell and the immediate below?

You could refine it by trimming or deleting spaces, symbols and converting everything to lowercase to make detection easy.

Instead of using find you should loop though the cells.

Edit: Here's a possible solution:

Sub Macro1()

    Dim idx_row As Long
    Dim idx_column As Long
    Dim idx_row_temp As Long
    Dim found_row As Boolean
    Dim found_column As Boolean
    Dim str_concat As String

    found_row = False
    idx_row = 1
    idx_column = 1
    Do While (Not found_row)
        If (Cells(idx_row, idx_column).Value = "Account1") Then
            found_row = True
        Else
            idx_row = idx_row + 1
        End If
    Loop

    found_column = False
    idx_row_temp = 1
    Do While (Not found_column)

        str_concat = Cells(idx_row_temp, idx_column).Value & Cells(idx_row_temp + 1, idx_column).Value

        MsgBox (str_concat)

        If (str_concat = "'MonthlyPlan'") Then
            found_column = True
        Else
            idx_column = idx_column + 1
        End If
    Loop

    MsgBox "Row: " & idx_row & vbCrLf & "Column: " & idx_column

End Sub

Of course it can be refined and improved, but this would be the basic idea.

Or... why don't you find the first numerical value?