find a value in two w/book sheets for value exists or not - vba code

[tkraju]

I need Excel Vba code in excel for a following task.
Two w/books >first w/book -- ABC.xlsx >sheet1> Sheet1(A2) > has date value> if this value occurs in > second w/book--TKR.xlsx>sheet1>
search in entire Column B>If the date value exists> message>"Date already exists">Exit Sub> If date value not exists> do something else code.

both w/books are not opened. Location of 2 w/books>C:\Raju\My documents. I am using windows11> office365

 

[tkraju]

So to be clear, the Workbook TKR is open, and being used when the code runs?

yes, w/book TKR open when code runs. Place the macro in w/book TKR

 

[igold]

Does my Post #9 do what is required.

 

[tkraju]

Try this. It's untested because I don't have your file structure, so let me know if it works.

Sub chkDate()
Dim wb1 As Excel.Workbook, wb2 As Excel.Workbook, ws1 As Worksheet, ws2 As Worksheet, chkVal As Variant, chkRng As Range
Dim myDir As String
Dim app1 As New Excel.Application, app2 As New Excel.Application
Dim wb As Excel.Workbook

myDir = "C:\Raju\My Documents\"
Set wb1 = app1.Workbooks.Add(myDir & "ABC.xlsx")
Set wb2 = app2.Workbooks.Add(myDir & "TKR.xlsx")
Set ws1 = wb1.Worksheets(1): Set ws2 = wb2.Worksheets(1)
Set chkRng = ws2.Range.Columns(2)
chkVal = ws1.Range("A2")

If Not chkRng.Find(chkVal) = "" Then
    MsgBox "Date already exists.", vbOKOnly, "Found"
Else
    'Other actions if no match
End If

wb1.Close False: wb2.Close False
Set app1 = Nothing: Set app2 = Nothing

End Sub

Code is not working.I am getting Complile Error: Argument not optional. code line : Set ChkRng=ws2.Range.Columns(2) is highlighting

 

[tkraju]

Try this. It's untested because I don't have your file structure, so let me know if it works.

Sub chkDate()
Dim wb1 As Excel.Workbook, wb2 As Excel.Workbook, ws1 As Worksheet, ws2 As Worksheet, chkVal As Variant, chkRng As Range
Dim myDir As String
Dim app1 As New Excel.Application, app2 As New Excel.Application
Dim wb As Excel.Workbook

myDir = "C:\Raju\My Documents\"
Set wb1 = app1.Workbooks.Add(myDir & "ABC.xlsx")
Set wb2 = app2.Workbooks.Add(myDir & "TKR.xlsx")
Set ws1 = wb1.Worksheets(1): Set ws2 = wb2.Worksheets(1)
Set chkRng = ws2.Range.Columns(2)
chkVal = ws1.Range("A2")

If Not chkRng.Find(chkVal) = "" Then
    MsgBox "Date already exists.", vbOKOnly, "Found"
Else
    'Other actions if no match
End If

wb1.Close False: wb2.Close False
Set app1 = Nothing: Set app2 = Nothing

End Sub

Where to paste this code ? which w/book?

 

[tkraju]

Thank you so much, Skybot. Perfectly its done.

Tested.giving compile error. Argument not optional

 

[Skyybot]

Changed code to copy into open Workbook TKR. Copy into ThisWorkbook.

Sub chkDate()
Dim wb1 As Excel.Workbook, wb2 As Workbook, ws1 As Worksheet, ws2 As Worksheet, chkVal As Variant, chkRng As Range
Dim myDir As String
Dim app1 As New Excel.Application
Dim wb As Excel.Workbook

myDir = "C:\Raju\My Documents\"
Set wb1 = app1.Workbooks.Add(myDir & "ABC.xlsx")
Set wb2 = ThisWorkbook
Set ws1 = wb1.Worksheets(1): Set ws2 = wb2.Worksheets(1)
Set chkRng = ws2.Range.Columns(2)
chkVal = chkRng.Find(ws1.Range("A2").Value)

If Not chkVal Is Nothing Then
    MsgBox "Date already exists.", vbOKOnly, "Found"
Else
    MsgBox "No match found.", vbOKOnly, "Not Found"
End If

wb1.Close False
Set app1 = Nothing

End Sub

 

[tkraju]

I am thinking that you are running this from a workbook other than ABC or TKR. If that is the case try this code in that workbook...

Sub FindDate()

    Dim wb As Workbook
    Dim dt As String, strFolder As String, strFile As String
    Dim rng As Range
   
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
   
    strFolder = "C:\Raju\My documents\"
    strFile = "ABC.xlsx"
   
    Set wb = Workbooks.Open(strFolder & strFile)
    dt = ActiveSheet.Range("A2")
    wb.Close
    strFile = "TKR.xlsx"
    Set wb = Workbooks.Open(strFolder & strFile)
    Set rng = ActiveSheet.Range("B1:B" & Cells(Rows.Count, 2).End(xlUp).Row).Find(What:=dt, _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
                           
    If Not rng Is Nothing Then
        MsgBox "Date already exists"
        wb.Close
        Exit Sub
    End If
    wb.Close
    MsgBox "do something else"
   
End Sub

I trying from TKR w/book which is destination w/book. ABC w/ book is a sourch w/book to find the Date exists or not in my destination w/book. After this condition met,if date does not exists my macro shoud "do something else" ( some dynamic data from ABC w/book to be copied and pasted to Destination w/book TKR.

 

[tkraju]

Is this what you are looking for. This code should be run from the TKR.xlsx workbook.

Sub FindDate()

    Dim wb As Workbook, wbT As Workbook
    Dim dt As String, strFolder As String, strFile As String
    Dim rng As Range, usRng As Range, lRow As Long
   
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
   
    strFolder = "C:\Raju\My documents\"
    strFile = "ABC.xlsx"
    Set wbT = ThisWorkbook
    Set wb = Workbooks.Open(strFolder & strFile)
    With wb.Worksheets("Sheet1")
        dt = .Range("A2")
        .UsedRange.Copy
    End With
   
    wb.Close
    lRow = wbT.Worksheets("Sheet1").Cells(Rows.Count, 2).End(xlUp).Row
    Set rng = Worksheets("Sheet1").Range("B1:B" & lRow).Find(What:=dt, _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
                           
    If Not rng Is Nothing Then
        MsgBox "Date already exists"
        Exit Sub
    End If
    wbT.Worksheets("Sheet1").Range("B" & lRow + 1).PasteSpecial xlPasteAll
    Application.CutCopyMode = False
   
    MsgBox "Workbook Updated"
   
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
   
End Sub

.Thank you igold.As per your message Is this what you are looking for. This code should be run from the TKR.xlsx workbook.in your new code referring (Find Date) macro ,I had done some changes > Changed Str folder ,Str file names> . I tested your code. My first requirement is not accomplishing. Your code is not checking A2 date(in my string file named “niftycsv” with Column B values of wbT (w/b DateCheck). Second part of my requirement is executing partly, total file is copying and pasting in last row of my wbT.Second part of your code is working > it is copying total sheet of “niftycsv”sheet data > but I don’t need header row data > only data below of header row has to be copied and pasted in wbT. Please change your code as per my requirement. 1.first check my niftycsv range(“A2”) date exists in column B of wbT.> if exists give message “Date already Exists” 2.If date does not exists in ColB of wbT then > copy niftycsv data excluding header row (from row2 to last row) and paste them in last row of wbT.

 

[Skyybot]

@tkraju, you need to at mention @igold if you want to get his attention.

 

[igold]

.Thank you igold.As per your message Is this what you are looking for. This code should be run from the TKR.xlsx workbook.in your new code referring (Find Date) macro ,I had done some changes > Changed Str folder ,Str file names> . I tested your code. My first requirement is not accomplishing. Your code is not checking A2 date(in my string file named “niftycsv” with Column B values of wbT (w/b DateCheck). Second part of my requirement is executing partly, total file is copying and pasting in last row of my wbT.Second part of your code is working > it is copying total sheet of “niftycsv”sheet data > but I don’t need header row data > only data below of header row has to be copied and pasted in wbT. Please change your code as per my requirement. 1.first check my niftycsv range(“A2”) date exists in column B of wbT.> if exists give message “Date already Exists” 2.If date does not exists in ColB of wbT then > copy niftycsv data excluding header row (from row2 to last row) and paste them in last row of wbT.

I have no idea what changes you have made. The code that I suggested was based upon information provided in your OP, and then on your requirement change that the code be run from an open workbook not a closed workbook as previously stated, along with some other new requirements (Post #8). Definitely not on what is quoted above.

If you want to provide some sample data along with the code you are attempting to use, perhaps someone could help you.

Also, you may want to change your profile so that the community knows you are using Microsoft 365