Page 1 of 1
fetch cell value to activecell
Posted: Wed Aug 02, 2023 11:50 am
by 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
Re: fetch cell value to activecell
Posted: Wed Aug 02, 2023 9:08 pm
by snasui
Please attach the example of source file.
Re: fetch cell value to activecell
Posted: Thu Aug 03, 2023 11:23 am
by sna
here is a sample
Re: fetch cell value to activecell
Posted: Thu Aug 03, 2023 3:17 pm
by snasui
Your code does not match with your latest file. Please check the file name and sheet name in your code again.
Re: fetch cell value to activecell
Posted: Thu Aug 03, 2023 3:47 pm
by sna
I change but it still not working
strWorkbookName = "FA Depreciation as of Jul-23.xlsx"
strSheetName = "After"
Re: fetch cell value to activecell
Posted: Thu Aug 03, 2023 4:05 pm
by snasui
Please attach the updated file again.
Re: fetch cell value to activecell
Posted: Thu Aug 03, 2023 4:11 pm
by sna
here it is
Re: fetch cell value to activecell
Posted: Thu Aug 03, 2023 4:36 pm
by 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
Re: fetch cell value to activecell
Posted: Fri Aug 04, 2023 8:53 am
by 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".
Re: fetch cell value to activecell
Posted: Fri Aug 04, 2023 9:37 am
by 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.
Re: fetch cell value to activecell
Posted: Fri Aug 04, 2023 11:32 am
by sna
i try when run code it shows nothing
Re: fetch cell value to activecell
Posted: Fri Aug 04, 2023 11:57 am
by 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
Re: fetch cell value to activecell
Posted: Fri Aug 04, 2023 1:08 pm
by sna
thanks