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

 

[SkipVought]

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,

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

 

[rider1234]

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/getfi...-433a-4681-bf4b-7ab3c8748ec1&file=book11.xlsx

https://files.engineering.com/getfi...-9ca0-4500-9809-f5176bcb3517&file=book12.xlsx

 

[SkipVought]

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,

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

 

[rider1234]

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

 

[SkipVought]

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,

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

 

[SkipVought]

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

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]

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

 

[SkipVought]

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,

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

 

[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

https://files.engineering.com/getfi...5-d12f-4e85-95d4-8a35aed46fdc&file=book11.xls

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

 

[SkipVought]

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,

_{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 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

 

[SkipVought]

Untested, from my iPad...

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
   [b]Dim wbk3 As Workbook, wsh3 As Worksheet, lRow3 As Long[/b]

   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)

   [b]Set wbk3 = Worksheets.Add
   Set wsh3 = wbk3.Worksheets(1)
   lRow3 = 1
   wsh3.Cells(lRow3, 1).Value = "Not Found"[/b]

   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)
            [b]If IsError(vRow2) then
               lRow3 = lRow3 + 1
               wsh3.Cells(lRow3, 1).Value = sLookup
            End If[/b]
          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]

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

 

[SkipVought]

Sorry,

Set wbk3 = Workbooks.Add

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]

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

 

[rider1234]

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

 

[SkipVought]

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,

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

 

[rider1234]

https://files.engineering.com/getfile.aspx?folder=8393407e-585b-4177-9dac-025f310c0531&file=1.xls

https://files.engineering.com/getfile.aspx?folder=50fbbdaf-9b0b-4eb3-a2aa-a49ac118a5f3&file=2.xls

plz download the file i have mentioned details

 

[SkipVought]

Please pay attention. You uploaded the wrong workbooks!

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

Skip,

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