Macro help

[Alvaroro84]

The code seem to capture the file path correctly. However, it is saying that the formula is incorrect Does any one have any suggestion on what is wrong with the code below?

Sub Macro1()

Dim lastrow As Long
lastrow = Sheets("Sheet1").UsedRange.SpecialCells(xlCellTypeLastCell).Row

Dim MyPath As String
MyPath = "F:\VMWare\"

Dim LatestFile As String
LatestFile = Most_Recently_Modified_ExcelFile_In_This_Folder(MyPath, "xls")
Debug.Print MyPath & LatestFile

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

Sheets("Sheet1").Range("D2:D" & lastrow).Cells.SpecialCells(xlCellTypeVisible).Formula2 = ActiveCell.Formula2

End Sub

Function Most_Recently_Modified_ExcelFile_In_This_Folder(folderPath As String, fileExtension As String)
fileExtension = Replace(fileExtension, ".", "")
If Right(folderPath, 1) = "\" Then folderPath = Left(folderPath, Len(folderPath))
Dim xFolder, xFile, fileName As String, counter As Integer, latestDate As Date
counter = 0
With CreateObject("Scripting.FileSystemObject")
    Set xFolder = .GetFolder(folderPath)
    For Each xFile In xFolder.Files
        If Mid(xFile.Name, InStrRev(xFile.Name, ".") + 1, 3) = fileExtension Then
            If counter = 0 Then
                fileName = xFile.Name
                latestDate = xFile.DateLastModified
                counter = 1
            Else
                If xFile.DateLastModified > latestDate Then
                    latestDate = xFile.DateLastModified
                    fileName = xFile.Name
                End If
            End If
        End If
    Next xFile
End With
Most_Recently_Modified_ExcelFile_In_This_Folder = fileName
End Function

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

 

[Alex Blakenburg]

Having a comma afer the single quote mark at the begginning and before the single quote mark at the end doesn't make sense.
Untested but try this:

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

 

[Alvaroro84]

Having a comma afer the single quote mark at the begginning and before the single quote mark at the end doesn't make sense.
Untested but try this:

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

doesn't look like that's what the issue is either

 

[Alex Blakenburg]

Before I try another suggestion, are you running Office 2016 per your profile or MS 365.
Try changing Formula2R1C1 to FormulaR1C1 (drop the "2").

PS: Do you know how to use Breakpoints and also the immediate window ?

 

[Alvaroro84]

Before I try another suggestion, are you running Office 2016 per your profile or MS 365.
Try changing Formula2R1C1 to FormulaR1C1 (drop the "2").

PS: Do you know how to use Breakpoints and also the immediate window ?

I'm using office 2016 and I tried dropping the 2 and didn't have much luck.

I don't know how to use breakpoints or immediate window can you provide me with a resource?

 

[Alvaroro84]

Before I try another suggestion, are you running Office 2016 per your profile or MS 365.
Try changing Formula2R1C1 to FormulaR1C1 (drop the "2").

PS: Do you know how to use Breakpoints and also the immediate window ?

I try to use this one to to see if it would work and no luck

ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-3],'[" & MyPath & LatestFile & "]Cos'!R2C3:R400000C4,2,False)"

 

[Alex Blakenburg]

try replacing your previous XLookup line with the lines I have below.
The copy the formula from the Immediate window (ctrl+G if it is not visible)
Put that formula into Excel and see what you need to change to get it to work directly in Excel.

' REPLACE THIS
'ActiveCell.Formula2R1C1 = _
'      "=XLOOKUP(RC[-3],'[" & MyPath & LatestFile & "]Cos'!C3,'[" & MyPath & LatestFile & "]Cos'!C4,0)"

' With this
Dim sFormula As String

' Create formula string
sFormula = "=XLOOKUP(RC[-3],'[" & MyPath & LatestFile & "]Cos'!C3,'[" & MyPath & LatestFile & "]Cos'!C4,0)"
' Print formula string to immediate window (Ctrl+G) if window is not visible
Debug.Print sFormula

ActiveCell.FormulaR1C1 = sFormula

 

[Alvaroro84]

try replacing your previous XLookup line with the lines I have below.
The copy the formula from the Immediate window (ctrl+G if it is not visible)
Put that formula into Excel and see what you need to change to get it to work directly in Excel.

' REPLACE THIS
'ActiveCell.Formula2R1C1 = _
'      "=XLOOKUP(RC[-3],'[" & MyPath & LatestFile & "]Cos'!C3,'[" & MyPath & LatestFile & "]Cos'!C4,0)"

' With this
Dim sFormula As String

' Create formula string
sFormula = "=XLOOKUP(RC[-3],'[" & MyPath & LatestFile & "]Cos'!C3,'[" & MyPath & LatestFile & "]Cos'!C4,0)"
' Print formula string to immediate window (Ctrl+G) if window is not visible
Debug.Print sFormula

ActiveCell.FormulaR1C1 = sFormula

The code you provide works great the formula is what needed to be change this is the working fomula

sFormula = "=XLOOKUP(RC[-3],[" & LatestFile & "]Cos!C3,[" & LatestFile & "]Cos!C4,0)"

 

[Alex Blakenburg]

Thanks for coming back and showing the changes. Glad I could help.
I find being able to print the formula string to the immediate window very helpful in trouble shooting this sort of thing