×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Notification by vba

Notification by vba

Notification by vba

(OP)
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

RE: Notification by vba

Hi,

I'm guessing, but you might be much better off, perhaps, if all your related data were in one workbook in different sheets, sheets with names that correspond to the table in the sheet.

At any rate, let me restate what I think you want.

In book11, loop through the data in column M, matching values in book12, column V.
If there is no match, then put the unmatched value (and all other unmatched values) in another workbook or a new workbook in some undefined sheet/column.

Please post whatever code you have related to this process.

Please upload each of the relevant workbooks, including book11 & book12.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Notification by vba

(OP)
loop in column M of book11.xlsx if column M has data then look the data in Column V in that row of book11.xlsx and find column V data of book11.xlsx in column F of book12.xlsx and if it is not found then notify
Sir i am new to vba i dont know how to write the code for this process so plz guide

https://files.engineering.com/getfile.aspx?folder=...
https://files.engineering.com/getfile.aspx?folder=...

RE: Notification by vba

In your previous thread you had
1) code to open 2 other workbooks and I added
2) code to loop thru a column in the first workbook/sheet,
3) match values to another column in that workbook/sheet and
4) get a vRow (lookup row number from a MATCH() function) from a column in another workbook/sheet.

So, since you are a programmer, you have the capacity to use that code as a sample and adapt it as far as you can go, to begin solving this task.

When you get that far, post back with your modified code to get help putting the unmatched value into another workbook.

BTW, this is exactly how I would start to code a solution for this task!

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Notification by vba

(OP)
i am not a programmer
what i know till yet that i have studied from the code
but for this problem i dont know what to do so plz guide

RE: Notification by vba

You have enough information to use the previous code as a guide, programmer or not. It will take only a few changes and it will be instructive.

Tek-Tips is not a free coding service! We give TIPS for solving problems. I have given you some tips. Please post your modified code.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Notification by vba

Hey, there is NOTHING in workbooks 11 & 12 that you uploaded. What goes?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Notification by vba

(OP)
i have highlighted the column
Currently that file is in my office pc
but i explained all the details we know which column we have to target and what we have to do
Any doubts plz aak sir i will let u know

RE: Notification by vba

Well, sir, it is customary and proper that you should supply target workbooks suitable for testing your data.

1) please upload adequate test data
2) please post your modified code.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Notification by vba

(OP)
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


https://files.engineering.com/getfile.aspx?folder=...

i have attached book11.xls plz have a look and in book12.xlsx our targeted column is column F so plz have a look

RE: Notification by vba

I need to see your code modified for this task, up to the point of saving the unmatched values, which would be new code that I will supply.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Notification by vba

(OP)
Sub Code()
Dim wbk1 As Workbook
Dim wsh1 As Worksheet
Dim wbk2 As Workbook
Dim wsh2 As Worksheet
Dim r1 As Range, vRow2 As Variant, sLookup As String

Application.ScreenUpdating = False

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

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

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)

Next
End With

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

Application.ScreenUpdating = True
End Sub


this code is not complete so plz have a look

RE: Notification by vba

Untested, from my iPad...

CODE

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 & "\book11.xls")
   Set wsh1 = wbk1.Worksheets(1)

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

   Set wbk3 = Worksheets.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 

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Notification by vba

(OP)
i am getting type mismatch error
Set wbk3 = Worksheets.Add
Set wsh3 = wbk3.Worksheets(1)
lRow3 = 1
wsh3.Cells(lRow3, 1).Value = "Not Found"
why we are adding a sheet in this code
we can run the code if condition met then we can get the notification in a new tab (list of names)
so plz have a relook sir

RE: Notification by vba

Sorry,

Set wbk3 = Workbooks.Add

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Notification by vba

(OP)
but now also there is an error it is providing only two names
i want all names to be displayed so plz relook sir

RE: Notification by vba

(OP)
and one more thing we can use msgbox option also sir it will be best i think so
plz relook i think this code contains some error

RE: Notification by vba

Please post your current code.
Please upload the target workbooks your code is opening.
Please list the values that you expect to be No Match.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Notification by vba

Please pay attention. You uploaded the wrong workbooks!

Please upload book11 & book12.
Please post the code in question.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Notification by vba

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

RE: Notification by vba

Then post the code you are currently running.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Notification by vba

(OP)
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

RE: Notification by vba

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Notification by vba

CODE

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
   Dim lLastRow 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
      lLastRow = .UsedRange.Rows.Count
      For Each r1 In .Range(.Cells(2, "M"), .Cells(lLastRow, "M"))
         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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Notification by vba

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

RE: Notification by vba

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


---- Andy

There is a great need for a sarcasm font.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close