Match values in spreadsheet

[Zzz999]

Hello. I'm trying to build a macro that can find and match two sheets. In my workbook I have three sheets: MACRO (Where the buttons are located), FIL, and SHIPMENT. I already have a macro for FIL, where it finds the column header TOTAL and find its last cell, and copy that cell value to a specific cell in the MACRO sheet. Here comes the problem: I need to find the same value in the SHIPMENT sheet, and before it finds the value it needs to find the words "FIL WAGE SUMMARY" (Column H) before it finds the same value of the TOTAL in sheet FIL (Same row as FIL WAGE SUMMARY, Column P). It needs to be that way to avoid finding values that aren't in the same row as the FIL WAGE SUMMARY row. And after it finds the needed value in SHIPMENT sheet, it needs to be copied and placed in a specific cell in the MACRO sheet.

To sum up:

Using the cell value of TOTAL in sheet FIL, find the words "FIL WAGE SUMMARY" in column H and find the cell in column P that matches the value in sheet FIL (Same row), and place that duplicate value to a specific cell in MACRO sheet.


This is the code for the FIL sheet:

 Dim headerRow As LongDim totalColumnsInHeaderRow As Long
Dim searchColumn As Long
Dim lastRowInSearchColumn As Long
Dim currentColumn As Long
Dim columnSearchString As String


With Sheets("FIL")
    headerRow = 9
    totalColumnsInHeaderRow = .Cells(headerRow, Columns.Count).End(xlToLeft).Column
    columnSearchString = "TOTAL"


    searchColumn = 0
    For currentColumn = 1 To totalColumnsInHeaderRow
        If StrComp(.Cells(headerRow, currentColumn).value, columnSearchString, vbTextCompare) = 0 Then
            searchColumn = currentColumn
            Exit For
        End If
    Next currentColumn


    If searchColumn > 0 Then
      
        lastRowInSearchColumn = .Cells(Rows.Count, searchColumn).End(xlUp).Row
        Sheets("MACRO").Range("B2") = _
            .Cells(lastRowInSearchColumn, searchColumn).value
    End If
End With

Any suggestions will be greatly appreciated. Thank you!

 

[oldbrewer]

I need to find the same value in the SHIPMENT sheet, and before it finds the value it needs to find the words "FIL WAGE SUMMARY" (Column H) before it finds the same value of the TOTAL in sheet FIL (Same row as FIL WAGE SUMMARY, Column P). It needs to be that way to avoid finding values that aren't in the same row as the FIL WAGE SUMMARY row

Even after drawing 3 mock up sheets I cannot understand what you want so can you knock up a pretend spreadsheet representing the 3 sheets in cols 1 to 5 10 to 14 and 20 to 24 and show us what you want to achieve, please

 

[Zzz999]

I need to find the same value in the SHIPMENT sheet, and before it finds the value it needs to find the words "FIL WAGE SUMMARY" (Column H) before it finds the same value of the TOTAL in sheet FIL (Same row as FIL WAGE SUMMARY, Column P). It needs to be that way to avoid finding values that aren't in the same row as the FIL WAGE SUMMARY row

Even after drawing 3 mock up sheets I cannot understand what you want so can you knock up a pretend spreadsheet representing the 3 sheets in cols 1 to 5 10 to 14 and 20 to 24 and show us what you want to achieve, please

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]FIL[/TD]
[TD]SHIPMENT[/TD]
[/TR]
[TR]
[TD]Match 1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]------*COMMAND BUTTON*------[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The first sheet is the MACRO sheet. It should show the values that were fetched in the two sheets when the command button is pressed.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Purchase[/TD]
[TD]TOTAL[/TD]
[/TR]
[TR]
[TD]10/02/2014[/TD]
[TD]Drums[/TD]
[TD]2,500[/TD]
[/TR]
[TR]
[TD]11/24/2014[/TD]
[TD]Piano[/TD]
[TD]5,000[/TD]
[/TR]
</tbody>[/TABLE]

The second sheet is the FIL sheet. I need to find the column that has the word "TOTAL" on it and find the last cell in that column (which is 5,000) and place that value in the MACRO sheet.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Remarks[/TD]
[TD]Date[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]Salary Bonus[/TD]
[TD]11/11/11[/TD]
[TD]4,000[/TD]
[/TR]
[TR]
[TD]FIL WAGE SUMMARY[/TD]
[TD]08/06/2012[/TD]
[TD]5,000[/TD]
[/TR]
[TR]
[TD]Misc[/TD]
[TD]10/01/2012[/TD]
[TD]1,850[/TD]
[/TR]
[TR]
[TD]FIL WAGE SUMMARY[/TD]
[TD]09/06/2012[/TD]
[TD]2,450[/TD]
[/TR]
</tbody>[/TABLE]

The last sheet is the SHIPMENT. On the FIL sheet, the TOTAL is 5,000. Then we need to find its match on the SHIPMENT sheet. First, the macro should find the "FIL WAGE SUMMARY" from the Remarks column. There's a possibility of duplicate entries (FIL WAGE SUMMARY in Remarks column) but differ in amount (5,000 and 2,450 in Amount column). If there's a duplicate entry like the example above, it should find between those entries the exact amount (5,000), so 5,000 will be copied and placed in the MACRO sheet. And if there is none, it should return 0 to the MACRO sheet.

The product should look like this:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]FIL[/TD]
[TD]SHIPMENT[/TD]
[/TR]
[TR]
[TD]MATCH 1[/TD]
[TD]5,000[/TD]
[TD]5,000[/TD]
[/TR]
[TR]
[TD]------*COMMAND BUTTON*------[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

As much as I would like to do it manually or to use formulas, etc., I need to make it using vba. I apologize if I haven't explained my question clearly on my earlier post. And also, thank you for taking your time to help me.