VBA to get value based on last column and using find

Iron 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, _
    MatchCase:=False)

    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

@hrh_dash

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

 

NikolinoDE

 

@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

@hrh_dash 

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)