Automatically Check Each Worksheet For Duplicate Entry

[Hasan]

Maybe there's a difference in the name of the sheet you're changing and what you
typed into the table in Sheet3.

I'd add:

msgbox "***" & sh.name & "***" & vblf & "***" & res & "***"

to see if I could see a difference.









--

Dave Peterson- Hide quoted text -

- Show quoted text -

Using below code is showing the acitive sheet name & the sheet3
columnB value


res _
= Application.VLookup(Target.Value, Worksheets("Sheet3").Range
("A:M"), 13, False)
If LCase(Sh.Name) = LCase(res) Then
'no message required
Else
MsgBox "***" & Sh.Name & "***" & vbLf & "***" & res & "***"
End If

But i want the macro to compare the columnA values(which is a data
validation drop down list) in sheets(apple, orage,pineapple) with
Sheet3 ColumnA value and if the selected value(in sheet "Orange")
shows "Apple" in Sheet3 Column B then message box "this belongs to
Apple worksheet" and clear contents(or insert the value in last row of
Apple worksheet) else if it shows "Orange" in Sheet3 Column B then no
message box

 

[Dave Peterson]

I don't understand.

Maybe someone else will jump in.

 

[Hasan]

I don't understand.

Maybe someone else will jump in.









--

Dave Peterson- Hide quoted text -

- Show quoted text -

Ok. Let me explain you...

I have 4 sheets in my workbook namely Apple, Orange, Pineapple &
Sheet3

Column A in sheets Apple, Orange, Pineapple are Data Validation List
drop down whose Source is Sheet3 Column A values

My Sheet3 data is...

Column A Column B

12345 Apple
23456 Orange
45678 Pineapple
98793 Orange

As the other 3 sheets(Apple, Orange, Pineapple) are Data Validation
List drop down. Depending upon the selection, i want the message box
to point me to right sheet. Like if the active sheet is Apple and user
select "23456" from dropdown then depending upon its data in sheet3
the message box should pop up."23456 should be in Orange sheet"

Hope its clear now

 

[Dave Peterson]

I would have guessed that changing this line:

MsgBox "***" & Sh.Name & "***" & vbLf & "***" & res & "***"
to
MsgBox target.value & " should be on " & res

would have worked.

 

[Hasan]

I would have guessed that changing this line:

MsgBox "***" & Sh.Name & "***" & vbLf & "***" & res & "***"
to
MsgBox target.value & " should be on " & res

would have worked.














--

Dave Peterson- Hide quoted text -

- Show quoted text -

Sorry. Actually you were right "Maybe there's a difference in the name
of the sheet you're changing and what you
typed into the table in Sheet3."

Changed the names and it worked.

Now the code points me to the correct worksheet upon selection. But
with "Target.ClearContents" in the below code i am getiing error

"Run-time error '13':
Type mismatch



res _
= Application.VLookup(Target.Value, Worksheets("Sheet3").Range
("A:M"), 13, False)
If LCase(Sh.Name) = LCase(res) Then
'no message required
Else
MsgBox Target.Value & " should be on " & res
Target.ClearContents
End If

 

[Dave Peterson]

If you're using mergedcells, then .clearcontents won't work.

try:

Target.value = ""

 

[Hasan]

If you're using mergedcells, then .clearcontents won't work.

try:

Target.value = ""











--

Dave Peterson- Hide quoted text -

- Show quoted text -

Still the same....

res _
= Application.VLookup(Target.Value, Worksheets("Sheet3").Range
("A:M"), 13, False)
If LCase(Sh.Name) = LCase(res) Then
'no message required
Else
MsgBox Target.Value & " should be on " & res
Target.Value = ""
End If


Clicking "Debug" is highlighting

If LCase(Sh.Name) = LCase(res) Then

 

[Dave Peterson]

When you deleted the line that checked for an error, you lost that check.

Add that check back and you'll see that you don't have a match in that table.

 

[Hasan]

When you deleted the line that checked for an error, you lost thatcheck.

Add thatcheckback and you'll see that you don't have a match in that table.










--

Dave Peterson- Hide quoted text -

- Show quoted text -

In the below code Adding Target.ClearContents or Target.Value = "" is
poping additional message box after clicking 'OK' on MsgBox
Target.Value & " should be on " & res that the entry already exists in
sheet1 though the value does not exsists.

res _
= Application.VLookup(Target.Value, Worksheets("Sheet3").Range
("A:M"), 13, False)
If IsError(res) Then
'no message required
Else
MsgBox Target.Value & " should be on " & res
Target.ClearContents
End If

 

[Dave Peterson]

Stop the events from firing your worksheet_change:

application.enableevents = false
target.value = ""
application.enableevents = true

 

[Hasan]

Stop the events from firing your worksheet_change:

application.enableevents = false
target.value = ""
application.enableevents = true








--

Dave Peterson- Hide quoted text -

- Show quoted text -

This one is again showing message for the correct selected value in
the sheet and clearing the contents

res _
= Application.VLookup(Target.Value, Worksheets("Sheet3").Range
("A:M"), 13, False)
If IsError(res) Then
'no message required
Else
MsgBox Target.Value & " should be on " & res
Application.EnableEvents = False
Target.Value = ""
Application.EnableEvents = True
End If

 

[Dave Peterson]

You need all those checks.

if iserror(res) then
'no message
else
if lcase(sh.name) = lcase(res) then
'do nothing
else
'do that other stuff
end if
end if

 

[Hasan]

You need all those checks.

if iserror(res) then
  'no message
else
   if lcase(sh.name) = lcase(res) then
       'do nothing
   else
      'do that other stuff
   end if
end if




- Show quoted text -...

read more »

Thanks.Its working perfect. Just that any manual deletion of record
from Column A is showing a message box "the record already exists" and
upon clicking OK its selecting first empty cell in sheet

 

[Dave Peterson]

check to see what's in the cell first:

if target.value = "" then
'do nothing
else
'do everything
end if

 

[Hasan]

checkto see what's in the cell first:

if target.value = "" then
  'do nothing
else
  'do everything
end if




- Show quoted text -...

read more »

Thanks you very much. Its working fine

 

[Hasan]

- Show quoted text -...

read more »

I am getting error message, when trying to operate the file from other
machine "Complile error: Can't find project or library"

 

[Hasan]

- Show quoted text -...

read more »

How do i assign macro to button to refresh data from another workbook
sheet.

Refresh to overwrite the data when refresh button is clicked

 

[Dave Peterson]

No idea what you did.

When you change subjects, you should start a new thread.

 

[Dave Peterson]

When you change subjects, you should start a new thread.

If you used the button from the Forms toolbar placed on a worksheet, just
rightclick on the button and choose assign macro.

 

[Hasan]

When you change subjects, you should start a new thread.

If you used the button from the Forms toolbar placed on aworksheet, just
rightclick on the button and choose assign macro.




- Show quoted text -...

read more »

Sorry for not starting the new thread.

I have assigned and recorded macro to button to refresh data. But i
get error "Subscript out of range" whenever i click the refresh
button. Below is the recorded macro.

Sub Button2_Click()

Columns("A:Q").Select
Selection.ClearContents
Windows("excel_sheet[1].xls").Activate
Range("A1:Q34000").Select
Selection.Copy
Windows("Sheet1.xls").Activate
Range("A4").Select
ActiveSheet.Paste
Range("A4").Select
Application.CutCopyMode = False
End Sub