How to use left on a range in VBA?

Question

I am working on a macro that iterates through multiple xlsx files in a folder and applies the same formulas to them all referencing the same named sheet in each file.

I am using xlookup to get values but I only want to look at the left part of the range when I search.

This line is not working Left(PTDws.Range("N:N"),5).

Dim n As Long
Dim PTDws As Worksheet
Dim ws As Worksheet

Set PTDws = wb.Sheets("PTD")
Set ws = wb.Sheets("YTD")
ws.Select   
 
For n = 2 To 14
  
    ws.Cells(n, 2).Value = WorksheetFunction.XLookup(Left(ws.Cells(n, 1), 5), Left(PTDws.Range("N:N"), 5), PTDws.Range("O:O"), "", 0)
    ws.Cells(n, 3).Value = WorksheetFunction.XLookup(Left(ws.Cells(n, 1), 5), Left(PTDws.Range("N:N"), 5), PTDws.Range("R:R"), "", 0)
    
    If ws.Cells(n, 2).Value = "-" Then
        ws.Cells(n, 2).Value = 0
    End If
    
    If ws.Cells(n, 3).Value = "-" Then
        ws.Cells(n, 3).Value = 0
    End If
    
    ws.Cells(n, 5).Value = (ws.Cells(n, 2).Value - ws.Cells(n, 3).Value)

Next n

Answer 1

Left in your post is a VBA function instead of worksheet function. It doesn't support a Range argument.

Please try.

Sub demo()
    Dim n As Long, c As Range
    Dim PTDws As Worksheet
    Dim ws As Worksheet
    Set PTDws = wb.Sheets("PTD")
    Set ws = wb.Sheets("YTD")
    ' ws.Select
    For n = 2 To 14
        With PTDws.Range("N:N")
            Set c = .Find(Left(ws.Cells(n, 1), 5) & "*", .Cells(.Cells.Count), xlFormulas, xlWhole)
            If c Is Nothing Then
                Debug.Print "no matching cell for " & Left(ws.Cells(n, 1), 5) 
            Else
                ws.Cells(n, 2).Value = c.Offset(, 1).Value
                ws.Cells(n, 3).Value = c.Offset(, 4).Value
                If ws.Cells(n, 2).Value = "-" Then
                    ws.Cells(n, 2).Value = 0
                End If
                If ws.Cells(n, 3).Value = "-" Then
                    ws.Cells(n, 3).Value = 0
                End If
                ws.Cells(n, 5).Value = (ws.Cells(n, 2).Value - ws.Cells(n, 3).Value)
            End If
        End With
    Next n
End Sub