VBA to get value based on last column and using find

Regular Contributor

I would like to have a macro to get values from last column by executing a find function. There is no errors but currently it is not displaying any values.


For example; based on the screenshot ws.Range("B31") would populate 117.83 based on the last column


The code as follows:


Dim find_2022_TCA As Range
Dim lastCol_TCA As Long
Dim Destws_TCA_corp As Worksheet

        Set find_2022_TCA = Destws_TCA_corp.Cells.Find(What:="MISC", After:=ActiveCell, LookIn:=xlValues _
    , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _

    lastCol_TCA = Destws_TCA_corp.Cells(2, Columns.Count).End(xlToLeft).Column

   ws.Range("B31").Value = Destws_TCA_corp.Cells(find_2022_TCA, lastCol_TCA).Value


appreciate the help in advance!

4 Replies


In the attached file you will find some examples, hope they help you.




@NikolinoDE , sorry would u be able to include a find function in the example? The reason for the find function is that my find variable (in this example is "MISC") may not always be the lastrow


To quickly find something in the active worksheet, you can use the search function.

To do this, press CTRL+F and enter the search terms.

Keyboard shortcuts in Excel



Managed to figure out a solution for this, decided to put the solution for everyone's easy reference.


    Dim Destwb_TCA  As Workbook
    Dim Destws_TCA  As Worksheet
    Dim find_TCA_MISC As Range
    Dim lastCol_TCA As Long

    Set Destwb_TCA = Workbooks.Open('indicate your workbook file path)
    Set Destws_TCA = Destwb_TCA.Sheets("Sum of Payment For invoicing")
    lastCol_TCA = Destws_TCA.Cells(2, Columns.Count).End(xlToLeft).Column 'looking at row 2
    Set find_TCA_MISC = Destws_TCA.Range("A:A").Find(What:="MISC") 'Text "MISC" will always be in range("A:A")
    ws.Cells(3, lastCol).Value = Destws_TCA.Cells(find_TCA_MISC.Row, lastCol_TCA)