VBA

Alvaroro84

Board Regular
Joined
May 13, 2022
Messages
65
Office Version
  1. 2016
Platform
  1. Windows
I want the macro to pick up the most recent Xl Doc from the My path=F:\VMWare\" location I'm just not sure what else to add to make it pick the most recent

VBA Code:
Sub macro2()
'
'
Dim LastRow As Long
Dim MyPath As String
Dim MyFile As String
Dim LatestFile As String
Dim LatestDate As Date
Dim LMD As Date
Dim WB As Workbook
Set WB = ActiveWorkbook
LastRow = Sheets("Sheet1").UsedRange.SpecialCells(xlCellTypeLastCell).Row


MyPath ="F:\VMWare\"



Range("D2").Select
ActiveCell.Formula2R1C1 = _
      "=XLOOKUP(RC[-3],'[" & MyPath & "]Cos'!C3,'[" & MyPath & "]Cos'!C4,0)"


Selection.AutoFill Destination:=Sheets("Sheet1").Range("D2:D" & LastRow), Type:=xlFillDefault


End Sub
 
In the formula that you originally had (and is at the beginning of the line that is giving the error), there is:
VBA Code:
RC[-3]
So that may indicate that the contents in 3 columns to the left of where the formula is being applied (in the active sheet in your Workbook with the VBA) may be wrong.
I've successfully used the formula below but with file name instead so I'm sure that the formula works
ActiveCell.Formula2R1C1 = "=XLOOKUP(RC[-3],'[" & MyPath & LatestFile & "]Cos'!C3,'[" & MyPath & LatestFile & "]Cos'!C4,0)"
 
Upvote 0

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
I've successfully used the formula below but with file name instead so I'm sure that the formula works
If that's the case, wonderful!

Then that means either the cell C3 and/or C4 in Sheet Cos in the last modified Excel file could be causing the error.

But don't quote me on that.

(Remember, the title of this thread. I provided the code/function you asked for regarding finding the name of the last modified file in the folder. Therefore, I believe it's out of scope of this thread to resolve this issue. If the above is not the cause of the error, and no one else replies to this thread, then start a new one to have someone else help you correct the issue.)
 
Upvote 0
Another cause could be that the active cell is in any of the columns A - C (and not Column D or greater).
 
Upvote 0
If that's the case, wonderful!

Then that means either the cell C3 and/or C4 in Sheet Cos in the last modified Excel file could be causing the error.

But don't quote me on that.

(Remember, the title of this thread. I provided the code/function you asked for regarding finding the name of the last modified file in the folder. Therefore, I believe it's out of scope of this thread to resolve this issue. If the above is not the cause of the error, and no one else replies to this thread, then start a new one to have someone else help you correct the issue.)
Thank you I appreciate the effort once I have a solution I'll make sure to update the post
 
Upvote 0
Wow, I don't know how that happened, but see my post above you. I apparently beat you by a millisecond!
 
Upvote 0

Forum statistics

Threads
1,212,934
Messages
6,110,762
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top