Find value in the last cell based on column header

Question

I'm trying to build a macro that can find and copy the last cell based on its column header("TOTAL")(Sheet name: FIL), and copy it to a specific cell(B2) in another sheet(Sheet name: MACRO TEMPLATE). I found a code for it but it doesn't copy the value in the other sheet. I tried tweaking it to make it work but no luck (The code works if the command button on the same sheet(FIL), but if placed in another sheet (MACRO TEMPLATE), the code doesn't work.).

I also have another problem about the code. The cells in which the header is located are merged (rows 9 and 10). Is it possible to search by columns instead of finding the header row so that the merged cells are untouched? Any help will be greatly appreciated.

Here's the code:

Dim headerRow As Integer
Dim totalColumnsInHeaderRow As Integer
Dim searchColumn As Integer
Dim lastRowInSearchColumn As Integer
Dim columnSearchString As String
With Sheets("FIL")
headerRow = 9   
totalColumnsInHeaderRow = Sheets("FIL").Cells(headerRow, Columns.Count).End(xlToLeft).Column
columnSearchString = "TOTAL" 
searchColumn = 0


Dim currentColumn As Integer
For currentColumn = 1 To totalColumnsInHeaderRow

    If StrComp(Sheets("FIL").Cells(headerRow, currentColumn).value, columnSearchString, vbTextCompare) = 0 Then

        searchColumn = currentColumn
        Exit For
    End If
Next


If searchColumn > 0 Then
    'Set F2 equal to the last value in that column
    lastRowInSearchColumn = Sheets("FIL").Cells(Rows.Count, searchColumn).End(xlUp).Row
    Sheets("MACRO TEMPLATE").Range("B2").value = Cells(lastRowInSearchColumn, searchColumn).value
End If

End With

Unknown Unknown · Accepted Answer · 2015-08-19T02:15:05

The last line before you close off the End If and End With contains Cells(lastRowInSearchColumn, searchColumn).Value. If this supposed to be from the FIL worksheet?

You are using a With ... End With statement but not taking advantage of the parent worksheet reference opportunities that it offers.

Dim headerRow As Long
Dim totalColumnsInHeaderRow As Long
Dim searchColumn As Long
Dim lastRowInSearchColumn As Long
Dim currentColumn As Long
Dim columnSearchString As String

With Sheets("FIL")
    headerRow = 9
    totalColumnsInHeaderRow = .Cells(headerRow, Columns.Count).End(xlToLeft).Column
    columnSearchString = "TOTAL"

    searchColumn = 0
    For currentColumn = 1 To totalColumnsInHeaderRow
        If StrComp(.Cells(headerRow, currentColumn).Value, columnSearchString, vbTextCompare) = 0 Then
            searchColumn = currentColumn
            Exit For
        End If
    Next currentColumn

    If searchColumn > 0 Then
        'Set F2 equal to the last value in that column
        lastRowInSearchColumn = .Cells(Rows.Count, searchColumn).End(xlUp).Row
        Sheets("MACRO TEMPLATE").Range("B2") = _
            .Cells(lastRowInSearchColumn, searchColumn).Value
    End If
End With

Note that any Range.Cells property need only a period (aka full stop or .) to have its parent worksheet assigned by the worksheet noted with the With ... End With statement. That does not stop you from specifying another worksheet in special circumstances as in the last value transfer target,

Find value in the last cell based on column header

1 Answers