Notification by vba 1

[rider1234]

If column M of book11.xlsx has data then look the column V data of book11.xlsx in that row and look the same data(column V data of book11.xlsx) in column F of book12.xlsx and if it is not there then notify the name of all that in once
notify the names means the data pesent in column V of book11.xlsx
suppose column M contains data and column V of book11 contains 12abc12-ak
and if 12abc12-ak is not present in book12.xlsx then notify the name means "12abc12-ak"
vba is placed in seperte file
all files are located in same place
So plz have a look and do needful

 

[rider1234]

files that i have attached sir is right but i forget to inform u that
book11 is 1.xls
book12 is 2.xls

 

[SkipVought]

Then post the code you are currently running.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[rider1234]

Sub Code()
Dim wbk1 As Workbook
Dim wish As Worksheet
Dim wbk2 As Workbook
Dim wsh2 As Worksheet
Dim r1 As Range, vRow2 As Variant, sLookup As String
Dim wbk3 As Workbook, wsh3 As Worksheet, lRow3 As Long

Application.ScreenUpdating = False

Set wbk1 = Workbooks.Open(ThisWorkbook.Path & "\1.xls")
Set wsh1 = wbk1.Worksheets(1)

Set wbk2 = Workbooks.Open(ThisWorkbook.Path & "\2.xls")
Set wsh2 = wbk2.Worksheets(1)

Set wbk3 = Workbooks.Add
Set wsh3 = wbk3.Worksheets(1)
lRow3 = 1
wsh3.Cells(lRow3, 1).Value = "Not Found"

With wsh1
For Each r1 In .Range(.Cells(2, "M"), .Cells(2, "M").End(xlDown))
If r1.Value > 0 Then
sLookup = .Cells(r1.Row, "V").Value
vRow2 = Application.Match(sLookup, wsh2.Range("F:F"), 0)
If IsError(vRow2) Then
lRow3 = lRow3 + 1
wsh3.Cells(lRow3, 1).Value = sLookup
End If
End If
Next
End With

Application.DisplayAlerts = False
wbk1.Close SaveChanges:=True
wbk2.Close SaveChanges:=True
Application.DisplayAlerts = True

Application.ScreenUpdating = True
End Sub

 

[SkipVought]

I am out and about now 14:00. Will not be back till 18:00.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[SkipVought]

Sub Code()
   Dim wbk1 As Workbook
   Dim wish As Worksheet
   Dim wbk2 As Workbook
   Dim wsh2 As Worksheet
   Dim r1 As Range, vRow2 As Variant, sLookup As String
   Dim wbk3 As Workbook, wsh3 As Worksheet, lRow3 As Long
   [b]Dim lLastRow As Long[/b]

   Application.ScreenUpdating = False

   Set wbk1 = Workbooks.Open(ThisWorkbook.Path & "\1.xls")
   Set wsh1 = wbk1.Worksheets(1)

   Set wbk2 = Workbooks.Open(ThisWorkbook.Path & "\2.xls")
   Set wsh2 = wbk2.Worksheets(1)

   Set wbk3 = Workbooks.Add
   Set wsh3 = wbk3.Worksheets(1)
   lRow3 = 1
   wsh3.Cells(lRow3, 1).Value = "Not Found"

   With wsh1
      [b]lLastRow = .UsedRange.Rows.Count
      For Each r1 In .Range(.Cells(2, "M"), .Cells(lLastRow, "M"))[/b]
         If r1.Value > 0 Then
            sLookup = .Cells(r1.Row, "V").Value
            vRow2 = Application.Match(sLookup, wsh2.Range("F:F"), 0)
            If IsError(vRow2) Then
               lRow3 = lRow3 + 1
               wsh3.Cells(lRow3, 1).Value = sLookup
            End If
         End If
      Next
   End With

   Application.DisplayAlerts = False
   wbk1.Close SaveChanges:=True
   wbk2.Close SaveChanges:=True
   Application.DisplayAlerts = True

   Application.ScreenUpdating = True
End Sub

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[rider1234]

Thnx Sir for giving ur precious time and great support to this post

 

[Andrzejek]

To show the appreciation for help received, click on [blue]Great Post![/blue] link in the helpful post(s)


---- Andy

There is a great need for a sarcasm font.