VBA to get value based on last column and using find

Question

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.&nbsp;For example; based on the screenshot ws.Range("B31") would populate 117.83 based on the last column&nbsp;The code as follows:&nbsp;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&nbsp;appreciate the help in advance!

nikolinode · Answer

hrh_dashIn the attached file you will find some examples, hope they help you.&nbsp;NikolinoDE&nbsp;

hrh_dash · Answer

NikolinoDE&nbsp;, 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

nikolinode · Answer

hrh_dash&nbsp;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&nbsp;&nbsp;

hrh_dash · Answer

Managed to figure out a solution for this, decided to put the solution for everyone's easy reference.&nbsp;    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)

Forum Discussion

VBA to get value based on last column and using find

Share

Resources