Debugging on .match

[stuartchaffey]

Hi, i hope someone can help. i have the below code that keeps debugging at "newRow = APPLICATION.Match(commCell.Value, newColumnA, 0)", this was working last night but has since stopped, any help appreciated.

'COPIES DATA FROM THE RETURNED SHEET AND PLACES DATA INTO NEW SHEET
Sub MatchAndCopyData()
Dim commWs As Worksheet
Dim newWs As Worksheet
Dim commColumnA As Range
Dim newColumnA As Range
Dim commRow As Long
Dim newRow As Long
' Set references to the relevant sheets
Set commWs = ThisWorkbook.Sheets("Communication")
Set newWs = ThisWorkbook.Sheets("New")
' Set references to the columns
Set commColumnA = commWs.Range("A:A")
Set newColumnA = newWs.Range("A:A")
' Loop through each cell in commColumnA
For Each commCell In commColumnA
If Not IsEmpty(commCell.Value) Then
' Find the matching row in newColumnA
On Error Resume Next
newRow = APPLICATION.Match(commCell.Value, newColumnA, 0)
On Error GoTo 0
If Not IsError(newRow) Then
' Copy data from columns F, G, H, I, J, & K
commWs.Cells(commCell.Row, "F").Resize(1, 6).Copy _
Destination:=newWs.Cells(newRow, "F")
End If
End Sub

 

[Joe4]

What is the exact error message?
Are you sure that there is a match?
What are the values of commCell and newColumnA at the time of the error?

 

[stuartchaffey]

I have attached images for you to see

 

[Joe4]

Try changing:

Application.Match.

to:

Application.WorksheetFunction.Match.

and see if that fixes it.

 

[stuartchaffey]

Try changing:

Application.Match.

to:

Application.WorksheetFunction.Match.

and see if that fixes it.

now the worksheetfunction.match is debugging

 

[Joe4]

One issue is you have two "If" lines, and but only one "End If" line.
You are also missing the "Next" that goes with the corresponding "For Each..." line.

 

[FormR]

Also, the fact that APPLICATION is capitalised suggests you have named a Module, Sub or Function "APPLICATION" - if you have, you should rename it avoiding words that have a meaning in VBA, like "MyApplication"

 

[dmt32]

Hi,
untested but see if this update to your code resolves your issue

Sub MatchAndCopyData()
    Dim commWs      As Worksheet, newWs As Worksheet
    Dim commColumnA As Range, newColumnA As Range
    Dim commRow     As Long
    Dim newRow      As Variant
    
    ' Set references to the relevant sheets
    Set commWs = ThisWorkbook.Sheets("Communication")
    Set newWs = ThisWorkbook.Sheets("New")
    
    ' Set references to the columns
    Set commColumnA = commWs.Range("A:A")
    Set newColumnA = newWs.Range("A:A")
    
    ' Loop through each cell in commColumnA
    For Each commcell In commColumnA
    
        If Not IsEmpty(commcell.Value) Then
            ' Find the matching row in newColumnA
            newRow = Application.Match(commcell.Value, newColumnA, 0)
            
            If Not IsError(newRow) Then
                ' Copy data from columns F, G, H, I, J, & K
                commWs.Cells(commcell.Row, "F").Resize(1, 6).Copy _
                Destination:=newWs.Cells(CLng(newRow), "F")
            End If
        End If
        
    Next commcell
    
End Sub

Dave