function match not working in my code

Craig_Moore

New Member
Joined
Dec 12, 2018
Messages
23
Hi All

I am using the below code to transfer data from one sheet to another, the code is supposed to check if the date that is being downloaded has already downloaded and pop up a box to say over wright yes/no but this is always coming up whether the date is there or not any help would be much appreciated



VBA Code:
Private Sub CommandButton2_Click()


Workbooks.Open "\\duerrsfile\SS\01 PNB Production\OEE SHEETS\02. DOWN TIME SHEET\DOWN TIME SHEET PNB 2020 .xlsx"
Application.ScreenUpdating = False
    Dim copySheet As Worksheet, pasteSheet As Worksheet, lCol As Long


' LINE 3 COPY PASE CODE,
    
Set copySheet = ThisWorkbook.Sheets("OVERVIEW")
    Set pasteSheet = Workbooks("DOWN TIME SHEET PNB 2020 .xlsx").Sheets("YTD DOWNTIME")
    
    On Error Resume Next
    lCol = WorksheetFunction.Match(copySheet.Range("LINE_3_DT").Cells(1, 1), pasteSpecialxlPasteValues.Range("4:4"), 0)
    On Error GoTo 0
    If lCol > 0 Then
        If MsgBox("Duplicate date - overwrite ?", vbYesNo, "") <> vbYes Then GoTo Save
    
    Else
        lCol = pasteSheet.Cells(4, pasteSheet.Columns.Count).End(xlToLeft).Column + 1
    End If
    
    copySheet.Range("LINE_3_DT").Copy
    pasteSheet.Cells(4, lCol).PasteSpecial xlPasteValues ' THE 5 VALUE MUST MATCH THE VALUE 2 LINES OF CODE UP
    Application.CutCopyMode = False
    Application.ScreenUpdating = True

'save Workbook

Save:
ActiveWorkbook.Save


'LINE 5 COPY CODE PAST CODE
Set copySheet = ThisWorkbook.Sheets("OVERVIEW")
    Set pasteSheet = Workbooks("DOWN TIME SHEET PNB 2020 .xlsx").Sheets("YTD DOWNTIME")
    
    On Error Resume Next
    lCol = WorksheetFunction.Match(copySheet.Range("LINE_3_DT").Cells(24, 1), pasteSpecialxlPasteValues.Range("24:24"), 0)
    On Error GoTo 0
    If lCol > 0 Then
        If MsgBox("Duplicate date - overwrite ?", vbYesNo, "") <> vbYes Then GoTo Save2
    
    Else
        lCol = pasteSheet.Cells(24, pasteSheet.Columns.Count).End(xlToLeft).Column + 1
    End If
    
    copySheet.Range("LINE_5_DT").Copy
    pasteSheet.Cells(24, lCol).PasteSpecial xlPasteValues ' THE 5 VALUE MUST MATCH THE VALUE 2 LINES OF CODE UP
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    
    'save Workbook

Save2:
ActiveWorkbook.Save


   'TUBS AND BUCKETS COPY CODE PAST CODE
   
Set copySheet = ThisWorkbook.Sheets("OVERVIEW")
    Set pasteSheet = Workbooks("DOWN TIME SHEET PNB 2020 .xlsx").Sheets("YTD DOWNTIME")
    On Error Resume Next
    lCol = WorksheetFunction.Match(copySheet.Range("LINE_3_DT").Cells(45, 1), pasteSpecialxlPasteValues.Range("45:45"), 0)
    On Error GoTo 0
    If lCol > 0 Then
        If MsgBox("Duplicate date - overwrite ?", vbYesNo, "") <> vbYes Then GoTo Save3
    
    Else
        lCol = pasteSheet.Cells(45, pasteSheet.Columns.Count).End(xlToLeft).Column + 1
    End If
    
    copySheet.Range("TUB_DT").Copy
    pasteSheet.Cells(45, lCol).PasteSpecial xlPasteValues ' THE 45 VALUE MUST MATCH THE VALUE 2 LINES OF CODE UP
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    
    'save Workbook

Save3:
ActiveWorkbook.Save
'Close Workbook
'ActiveWorkbook.Close Savechanges:=True
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,406
VBA Code:
    Dim vCol As Variant

    Set pasteSheet = Workbooks("DOWN TIME SHEET PNB 2020 .xlsx").Sheets("YTD DOWNTIME")
    
    vCol = Application.Match(copySheet.Range("LINE_3_DT").Cells(1, 1), pasteSheet.Range("4:4"), 0)
    
    If Not IsError(vCol) Then
        If MsgBox("Duplicate date - overwrite ?", vbYesNo, "") <> vbYes Then GoTo Save2
    Else
        vCol = pasteSheet.Cells(4, pasteSheet.Columns.Count).End(xlToLeft).Column + 1
    End If
    
    copySheet.Range("LINE_5_DT").Copy
    pasteSheet.Cells(24, vCol).PasteSpecial xlPasteValues ' THE 5 VALUE MUST MATCH THE VALUE 2 LINES OF CODE UP
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    
    'save Workbook

Save2:
ActiveWorkbook.Save
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,130,297
Messages
5,641,415
Members
417,209
Latest member
Agbarker

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
Top