[Solved] Formula

[Tam]

Hi, is there any formulas i can so i will get the ABCDEF into the yellow column, I am trying to put the lastest version into the cell, for example, the first row has only A, so it will be A, but second row goes up to E, i need to put E in that cell

thx

 

[Yousuf Shovon]

is there any formulas i can so i will get the ABCDEF into the yellow column, I am trying to put the lastest version into the cell

Hello TAM,

Welcome to ExcelDemy Forum! I understand you wish to get the column headers of the latest value into the given yellow column A. This procedure could be easier with Excel VBA than with formulas.

Here are the steps to find the header names of the last values:

• First, go to Developer tab >> Macros >> click on UpdateColumnA in Macro name dialog >> Run.

​

• Consequently, the below code will run:

Dim MaxTemp As Long
Sub UpdateColumnA()
    Dim ws As Worksheet
    Dim lastRow As Long, lastCol As Long
    Dim i As Long, j As Long
   
    Set ws = ThisWorkbook.Sheets("Sheet1")
   
    lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).row
    lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
    Call FindLastRowInColumns
    For i = 2 To MaxTemp
        For j = lastCol To 2 Step -1
            If Not IsEmpty(ws.Cells(i, j)) Then
                ws.Cells(i, 1).Value = ws.Cells(1, j).Value
                Exit For
            End If
        Next j
    Next i
End Sub
Sub FindLastRowInColumns()
    Dim ws As Worksheet
    Dim lastColumn As Long
    Dim i As Long, lastRow As Long, maxRow As Long
   
    Set ws = ThisWorkbook.Sheets("Sheet1")
    lastColumn = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
    maxRow = 0
   
    For i = 1 To lastColumn
        lastRow = ws.Cells(ws.Rows.Count, i).End(xlUp).row
        If lastRow > maxRow Then
            maxRow = lastRow
        End If
    Next i
   
    MaxTemp = maxRow
End Sub

Note: Do not forget to update the sheet name in Set ws = ThisWorkbook.Sheets("Sheet1").

• Thus, the code returns the desired output.

​

I have attached the desired workbook here. Download it, practice by yourself, and let me know if it works.

Regards,
Yousuf Shovon

 

[Tam]

Hi,

I dont know VBA, but i am starting to know now,
i follow your code to run the VBA, but i don't quite understand when u say,
"Do not forget to update the sheet name in Set ws = ThisWorkbook.Sheets("Sheet1")."

how can i dot that

thx

 

[Yousuf Shovon]

"Do not forget to update the sheet name in Set ws = ThisWorkbook.Sheets("Sheet1")."

Hello Tam,

Glad to hear from you again. Thanks for your feedback. It is nice to know you are interested in Excel VBA. If you often find yourself, doing repetitive tasks over and over again in Excel and wishing that somehow, someway these tasks could be automated, Excel VBA is the right choice.
As it was for your previous problem. Follow the below article that suggests beginner to advanced VBA books:

6 Best Excel VBA Programming Books​

Also, see the below article for some practical examples for a better understanding of Excel VBA.

25 VBA Macro Example for Enhanced Productivity
​

To answer your question, this particular code line Set ws = ThisWorkbook.Sheets("Sheet1") sets a variable named ws to refer to a specific worksheet in the workbook where the code is running. The worksheet being referred to is Sheet1.

​

When you are working with your Excel file, suppose in a sheet named Yellow Format, you need to update the sheet name in that code. So, the code line will appear like this:

Set ws = ThisWorkbook.Sheets("Yellow Format")

Consequently, the code will run in that particular sheet only.

Hopefully, you can implement the code in your Excel project now. Thank you.

Regards,
Yousuf Shovon