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

 

[Skyybot]

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

 

[igold]

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

 

[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

Thank you so much, Skybot. Perfectly its done.

 

[tkraju]

Thank you so much, Skybot. Perfectly its done.

Sorry to say, I forgotten to mention that this macro runs from my second w/book TKR as this is my destination w/book. if no match found copy ws1.Range(Ä2:E4") and paste the values in destination w/book TKR at last row. What changes are to be done in your code ?

 

[igold]

I am confused, do you mean to be talking with me (igold) or @Skyybot...

 

[tkraju]

I am confused, do you mean to be talking with me (igold) or @Skyybot...

Sir, I am talking to you sir/madam, Skyybot.

 

[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

Thank You igold. If date does not exists please add this action in your code. Copy ABC.xlsx active sheet.Range A2 to E used range(the sheet's dynamic range) and paste copied values in TKR.xlsx column B last row. Close the Sub with msg "w/book updated".

 

[igold]

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

 

[Skyybot]

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

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