Yeah I feel like an idiot about that lol.
Is it possible to have the report format like I was hoping in previous post? Just curious is all.
Yeah I feel like an idiot about that lol.
Is it possible to have the report format like I was hoping in previous post? Just curious is all.
Yep - here you go: just make the first paste of the first two columns conditional on booMatchFound being false and remove the sort at the end.
![]()
Option Explicit Option Base 1 Sub MatchAndPaste() Dim strPartsSheet As String, strDetailSheet As String, strSummarySheet As String Dim intFirstDataRow As Integer, intLastDataRow1 As Integer, intLastDataRow2 As Integer Dim sht As Worksheet Dim strPart As String, strDetail As String, strOrder As String Dim intCount As Integer Dim booMatchFound As Boolean Dim i As Integer, j As Integer 'setup/intialise: strPartsSheet = "Sheet1" strDetailSheet = "Sheet2" strSummarySheet = "Sheet3" intFirstDataRow = 2 intLastDataRow1 = Worksheets(strPartsSheet).Cells(intFirstDataRow, 1).CurrentRegion.Rows.Count intLastDataRow2 = Worksheets(strDetailSheet).Cells(intFirstDataRow, 1).CurrentRegion.Rows.Count For Each sht In Worksheets If sht.Name = strPartsSheet Or sht.Name = strDetailSheet Then Worksheets(sht.Name).Select Worksheets(sht.Name).Range("A1").Sort _ key1:=Worksheets(sht.Name).Columns("A"), order1:=xlAscending, _ key2:=Worksheets(sht.Name).Columns("B"), order2:=xlAscending, _ header:=xlYes End If Next sht intCount = 0 Worksheets(strSummarySheet).Cells.ClearContents For i = intFirstDataRow To intLastDataRow1 strOrder = Worksheets(1).Cells(i, 1).Text strPart = Worksheets(1).Cells(i, 3).Text strDetail = "NO MATCH" For j = 1 To intLastDataRow2 If Worksheets(1).Range("A" & i) = Worksheets(2).Range("A" & j) Then If Worksheets(1).Range("B" & i) = Worksheets(2).Range("B" & j) Then strDetail = Worksheets(2).Cells(j, 3).Text intCount = intCount + 1 If booMatchFound = False Then Worksheets(strSummarySheet).Range("A" & intFirstDataRow + intCount - 1).Value = strOrder Worksheets(strSummarySheet).Range("B" & intFirstDataRow + intCount - 1).Value = strPart End If Worksheets(strSummarySheet).Range("C" & intFirstDataRow + intCount - 1).Value = strDetail booMatchFound = True End If End If Next j 'if you get to here, you've cycled through the current record in sheet1 and attempted to 'match it against all the records in sheet2 and found no match; in this case, need to 'copy the Order and Part Number to the Summary sheet: If booMatchFound = False Then intCount = intCount + 1 Worksheets(strSummarySheet).Range("A" & intFirstDataRow + intCount - 1).Value = strOrder Worksheets(strSummarySheet).Range("B" & intFirstDataRow + intCount - 1).Value = strPart Worksheets(strSummarySheet).Range("C" & intFirstDataRow + intCount - 1).Value = strDetail Else booMatchFound = False End If Next i With Worksheets(strSummarySheet) .Columns("A:C").NumberFormat = "@" .Columns("A:C").HorizontalAlignment = xlRight .Cells(1, 1).Value = "Order Number" .Cells(1, 2).Value = "Part Number" .Cells(1, 3).Value = "Detail" End With End Sub
MatrixMan.
--------------------------------------
If this - or any - reply helps you, remember to say thanks by clicking on *Add Reputation.
If your issue is now resolved, remember to mark as solved - click Thread Tools at top right of thread.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks