VLOOKUP/MATCH help needed

shina67

Board Regular
Joined
Sep 18, 2014
Messages
138
Hi,
Hope someone can help. I have a excel sheet sent to me on a daily basis, this sheet changes daily. I alter this sheet over the course of the working day. I have uploaded the sheet to the following link https://www.dropbox.com/s/zrmcxnbey5...rder.xlsx?dl=0. The following sheet is what it looks like after I have altered things https://www.dropbox.com/s/risd3d4bbh...%201.xlsx?dl=0. If the numbers in column F & G on sheet "Book 1" can be found in columns F & G in "Doorsets Open Order" then I need the colour coding copied from "Book 1" to "Doorsets Open Order". The numbers in column F & G will eventually disappear as the order is shipped out to my customer. Basically the sheet "Doorset Open Order" is a sheet with all my outstanding customer orders and sheet "Book 1" is what I save to let myself know where we are as a department with the order.
Hope someone can help as this will save me hours of work everyday transferring data from "Book 1" to "Doorsets Open Order".
Hope I have explained correctly what I require.

Regards
Sean
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Hi,

You're in quite a hurry to bump after 4 hours.
What you've asked can't be done by a VLOOKUP or a MATCH formula.
You will need to insert in a macro which will compare all lines and copy the format to the new sheet if they match.
Looking at the number of lines to compare it will be somewhat time consuming but this will work.

Be careful! Your file lay out weren't equal in both files. I deleted column A in the Doorset Open Order File to make them equal!

Code:
Sub FindMatch()

Dim i, j, Rows1, Rows2 As Integer
Dim OrderNoS, OrderNoD, LineNoS, LineNoD As String
Dim sourceRange, destinationRange As Excel.Range


Rows1 = Workbooks("Book 1.xlsx").Worksheets("Sheet1").Cells(Rows.Count, "B").End(xlUp).Row             'rows count in Source Sheet
Rows2 = Workbooks("Doorset Open Order.xlsx").Worksheets("Sheet1").Cells(Rows.Count, "B").End(xlUp).Row 'rows count in Destination Sheet

'Optimize Macro Speed
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual

'Loop Through Rows
    For i = 1 To 10
        OrderNoS = Workbooks("Book 1.xlsx").Worksheets("Sheet1").Cells(i, "F").Value                    'Order Number  Source Sheet
        LineNoS = Workbooks("Book 1.xlsx").Worksheets("Sheet1").Cells(i, "G").Value                     'Line Number Source Sheet
        
        For j = 1 To 10
            OrderNoD = Workbooks("Doorset Open Order.xlsx").Worksheets("Sheet1").Cells(j, "F").Value    'Order Number Destination Sheet
            LineNoD = Workbooks("Doorset Open Order.xlsx").Worksheets("Sheet1").Cells(j, "G").Value     'Line Number Destination Sheet

            If (OrderNoS = OrderNoD) And (LineNoS = LineNoD) Then   'Compare Rows
                Set sourceRange = Workbooks("Book 1.xlsx").Worksheets("Sheet1").Rows(i)
                Call sourceRange.Copy
                Set destinationRange = Workbooks("Doorset Open Order.xlsx").Worksheets("Sheet1").Rows(j)
                Call destinationRange.PasteSpecial(xlPasteFormats)
            End If

        Next j

    Next i
    
'Reset Macro Optimization Settings
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

End Sub
 
Last edited:
Upvote 0
Hi jorismoerings,

I cannot seem to get your code to work.
Is it a standalone vba or is it inserted in either the Doorset Open Order.xlsx or the Book 1.xlsx.

thanks so far for your help.
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,140
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top