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

tkraju

Board Regular
Joined
Apr 1, 2009
Messages
79
Office Version
  1. 2016
Platform
  1. Windows
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
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Does my Post #9 do what is required.
 
Upvote 0
Try this. It's untested because I don't have your file structure, so let me know if it works.
VBA Code:
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
 
Upvote 0
Try this. It's untested because I don't have your file structure, so let me know if it works.
VBA Code:
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?
 
Upvote 0
Changed code to copy into open Workbook TKR. Copy into ThisWorkbook.
VBA Code:
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
 
Upvote 0
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...
VBA Code:
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.
 
Upvote 0
Is this what you are looking for. This code should be run from the TKR.xlsx workbook.

VBA Code:
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.
 
Upvote 0
.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
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

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