fetch cell value to activecell

[sna]

Hi there
i have some codes to copy values from file in a folder of sharedrive .it is not working due to last row containing blank cells

Code: Select all

  Dim wb As Workbook
    Dim ws As Worksheet
    Dim cellValue As Variant
    Dim strPath As String
    Dim strWorkbookName As String
    Dim strSheetName As String
    Dim strCellAddress As String
    strPath = "Z:\Finance-Dept\6. End of Month_EoM\2. End of Month_EoM for 2023\7. July 2023\" 
    strWorkbookName = "Fixed Assets Depreciation as of Jul 23.xlsx" 
    strSheetName = "FA List Update" 
    strCellAddress = "" ' Initialize the cell address
    Set wb = Workbooks.Open(strPath & strWorkbookName)
    Set ws = wb.Sheets(strSheetName)
    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, "D").End(xlUp).Row
    Dim i As Long
    For i = 4 To lastRow
        If Right(ws.Cells(i, "D"), 5) = "Total" Then
            strCellAddress = "L" & i
         
            Exit For
        End If
    Next i
    If strCellAddress <> "" Then
        cellValue = ws.Range(strCellAddress).Value
        ActiveCell.Value = cellValue
    End If
    wb.Close False
End Sub

thanks

[snasui]

Please attach the example of source file.

[sna]

here is a sample

[snasui]

Your code does not match with your latest file. Please check the file name and sheet name in your code again.

[sna]

I change but it still not working
strWorkbookName = "FA Depreciation as of Jul-23.xlsx"
strSheetName = "After"

[snasui]

Please attach the updated file again.

[sna]

here it is

[snasui]

The word "Total" is in column A not D. You can adjust your code as below.

Code: Select all

'Other code
    For i = 28 To lastRow
        If Right(ws.Cells(i, "A"), 5) = "Total" Then
            strCellAddress = "L" & i
            Exit For
        End If
    Next i
    If strCellAddress <> "" Then
        cellValue = ws.Range(strCellAddress).Value
        ActiveCell.Value = cellValue
    End If
'Other code

[sna]

I run code it happens nothing .i want to pupulate if col A has the word " Total " populate value in column L to active cell and down ward for all col A has the word "Total".

[snasui]

Your code shows the result in active cell in each time, now it shows the first one that match condition. If you break or delete Exit for in your code the result in active cell will show the last one in column L.

[sna]

i try when run code it shows nothing

[snasui]

You need to know what the active cell is while running your code. For easy to understand you should specify the target cell to show your result like this.

Code: Select all

'Other code
    If strCellAddress <> "" Then
        cellValue = ws.Range(strCellAddress).Value
'        ActiveCell.Value = cellValue
        With ThisWorkbook.Worksheets("Sheet1")
            .Range("h5").Value = cellValue
        End With
    End If
 'Other code

[sna]

thanks