Sep 06 2022 07:49 PM
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!
Sep 07 2022 01:55 AM
Sep 07 2022 10:14 PM - edited Sep 07 2022 10:19 PM
@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
Sep 07 2022 11:02 PM
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.
Sep 10 2022 07:38 AM
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)