Check If Value Is In Closed Worksheet

[leighmetcalfe]

I currently have the below macro to copy data from a my sales workbook and paste the values to a closed workbook.

I'm struggling to find a macro that can check column C of the closed workbook for "VE01", if this appears then delete the line and run the transfervalues sub

This is to prevent duplicate entries.

Sub TransferValues()
Application.ScreenUpdating = False
Dim wsMain As Worksheet
Dim wbData As Workbook
Dim wsData As Worksheet
Dim rngToCopy As Range
Dim C As Long
Dim ar As Range
Dim cl As Range


Dim Lastrow As Long
Dim rngDestination As Range


Set wsMain = ThisWorkbook.Sheets("Enquiry Ticket")


Application.DisplayAlerts = False
' you need to open the workbook
Set wbData = Workbooks.Open("T:\Sales\Sales\Test.xlsb")
Set wsData = wbData.Sheets("C&B")
Set rngToCopy = wsMain.Range("A3:AI3")


'Get the last row in Database sheet:
Lastrow = wsData.Cells(wsData.Rows.Count, "A").End(xlUp).Row


C = 1
For Each cl In rngToCopy
    cl.Copy
    wsData.Cells(Lastrow + 1, C).PasteSpecial xlPasteValues
    C = C + 1
Next cl


wbData.Close True '<-- close and save the changes made
Application.DisplayAlerts = True '<-- restore settings
Application.ScreenUpdating = True
End Sub

 

[Yongle]

Are you trying to delete the row in wsData ?
Try putting this code before copying the data from wsMain.Range("A3:AI3")

  On Error Resume Next
    With wsData
        .Rows(WorksheetFunction.Match("VE01", .Columns(3), 0)).EntireRow.Delete
    End With
  On Error GoTo 0

 

[Yongle]

To get row number before opening the workbook ..
(in all cases if > 0 then that row is the one to delete)

EITHER
1. Put this formula in any cell in the open workbook (value returned is row number)
=IFERROR(MATCH("VE01",'[T:\Sales\Sales\Test.xlsb]C&B'!$C:$C,0),0)

OR
2. Put MATCH formula in the closed workbook and use the archaic ExecuteExcel4Macro to get its value

With formula in cell A1 in Test.xlsb
=IFERROR(MATCH("VE01",C:C,0),0)

Place VBA in open workbook - where r is the ROW number (or 0 if not found)

Get the syntax correct like this (note R1C1 notation - C1 would be R1C3)

  [COLOR=#ff0000]r[/COLOR] = ExecuteExcel4Macro("'T:\Sales\Sales\[Test.xlsb]C&B'![B]R1C1[/B]")

or call a function that does the job

  [COLOR=#ff0000]r[/COLOR] = GetValue("T:\Sales\Sales", "Test.xlsb", "C&B", "A1")

 Function GetValue(path, file, sheet, ref)
    Dim arg As String
[COLOR=#006400][I]'   Make sure the file exists[/I][/COLOR]
    If Right(path, 1) <> "" Then path = path & ""
    If Dir(path & file) = "" Then
        GetValue = "File Not Found"
        Exit Function
    End If
[COLOR=#006400][I]'   Create the argument[/I][/COLOR]
    arg = "'" & path & "[" & file & "]" & sheet & "'!" & Range(ref).Range("A1").Address(, , xlR1C1)
[COLOR=#006400]'[I]   Execute an XLM macro[/I][/COLOR]
    GetValue = ExecuteExcel4Macro(arg)
End Function

credit for function: http://spreadsheetpage.com/index.php/tip/a_vba_function_to_get_a_value_from_a_closed_file/

 

[leighmetcalfe]

Yongle - your the best

worked perfectly.

 

[Yongle]

thanks for your feedback