How can I match 2 criteria in a VBA IfError function?-CodePudding

I have 2 lists, the first is tasks that are Open, the second is a compilation of every Project.

In the Open tab there are 2 identifying factors col A-Project number and col J-Sales order.
In the Project tab, the same 2 factors are present col A-Project number and col B-Sales order

Currently, I can check to see if a project is already listed in the Project tab and add information as needed to the row of specifics. Here is what I am using and IS WORKING:

*p = track.Sheets("Projects").Range("A1").End(xlDown).Row   1
o = track.Sheets("Open Task").Range("A1").End(xlDown).Row
For l = 2 To o
s = Application.WorksheetFunction.IfError(Application.Match(track.Sheets("Open Task").Range("A" & l), track.Sheets("Projects").Range("A:A"), 0), 0)
If s = 0 Then
If Not track.Sheets("Open Task").Range("K" & l).Value Like "X" Then
    track.Sheets("Projects").Range("A" & p).Value = track.Sheets("Open Task").Range("A" & l) 'PROJECT #.....*

WHAT I WANT TO DO is to check if there is an existing Project Number, make sure that the Sales order is there, if not, add it. The error that I am running into, is that it lists the missing Sales Order at the end of Project list, instead of adding it to the existing project row. Here is the code I tried:

*p = track.Sheets("Projects").Range("B1").End(xlDown).Row
o = track.Sheets("Open Task").Range("B1").End(xlDown).Row
For m = 2 To o
s = Application.WorksheetFunction.IfError(Application.Match(track.Sheets("Open Task").Range("J" & m), track.Sheets("Projects").Range("B:B"), 0), 0)
If s = 0 Then
If Not track.Sheets("Open Task").Range("K" & m).Value Like "X" Then
    track.Sheets("Projects").Range("B" & p).Value = track.Sheets("Open Task").Range("J" & m) 'SALES ORDER #......*

It may be something I can do in one step, I just can't figure out how to code it. Check to see if Project Number exists, if not create a new project row and fill (done) If the Project Number exists, does that row have a Sales Order, if not, fill it in (current issue)

I hope that makes sense. I am learning VBA as I need it, but can't figure this one out.

CodePudding user response：

Best guess but to be honest I'm not sure I follow your flow:

    Dim p As Long, o As Long, m As Long, wsP As Worksheet, wsOT As Worksheet
    
    Set wsP = track.Sheets("Projects")
    Set wsOT = track.Sheets("Open Task")
    
    p = wsP.Range("B1").End(xlDown).Row
    o = wsOT.Range("B1").End(xlDown).Row
    
    For m = 2 To o
        
        If IsError(Application.Match(wsOT.Range("J" & m), wsP.Range("B:B"), 0)) Then
        
            If Not wsOT.Range("K" & m).Value Like "X" Then
                wsP.Range("B" & p).Value = wsOT.Range("J" & m) 'SALES ORDER #......*

CodePudding user response：

I was able to fix the problem by defining a project row, that matched the Project Number in both sheets. Then, if there was a Sales Order in the Open Task sheet, it fills in on the Projects sheet.

projrow = Application.Match(track.Sheets("Open Task").Range("A" & m), track.Sheets("Projects").Range("A:A"), 0)

If track.Sheets("Open Task").Range("J" & m).Value Like "5*" Then
    track.Sheets("Projects").Range("B" & projrow).Value = track.Sheets("Open Task").Range("J" & m).Value 'SO#
    track.Sheets("Projects").Range("C" & projrow).Value = track.Sheets("Open Task").Range("H" & m).Value 'KP#
    track.Sheets("Projects").Range("D" & projrow).Value = track.Sheets("Open Task").Range("I" & m) 'NET VALUE