On Oct 1, 11:25*pm, Dave Peterson wrote:
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.





Hasan wrote:

On Oct 1, 5:41 am, Dave Peterson wrote:
If you're using mergedcells, then .clearcontents won't work.

try:

Target.value = ""

Hasan wrote:

On Oct 1, 3:10 am, Dave Peterson wrote:
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 wrote:

On Oct 1, 1:14 am, Dave Peterson wrote:
I don't understand.

Maybe someone else will jump in.

Hasan wrote:

On Sep 30, 6:37 am, Dave Peterson wrote:
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.

Hasan wrote:

On Sep 30, 1:35 am, Dave Peterson wrote:
if lcase(sh.name) = lcase(res) then
* 'no message required
else
* 'show the message
end if

Hasan wrote:

On Sep 29, 5:20 am, Dave Peterson wrote:
Add target.clearcontents to clear the cell that had the value entered.

Hasan wrote:

On Sep 29, 2:23 am, Dave Peterson wrote:
I'm not sure if this gets incorporated into the earlier code or if it's for a
single sheet, but maybe this will get you started:

You could use something like:

Dim res as variant

res _
*= application.vlookup(target.value,worksheets("Sheet 3").range("A:B"),2,false)

if iserror(res) then
* *msgbox "Not found on sheet3"
else
* *msgbox "This Number should go in " & res & "worksheet."
end if

Hasan wrote:

Coloum A data validation list in all worksheets is from sheets3 column
A. And Sheet3 has following data

Column A * * * *ColumnB
123456 * * * * *Apple
456789 * * * * *Orange
147894 * * * * *Pineapple
159357 * * * * *Orange

If the user is in appleworksheetand select value "456789"(which is a
new value in the workbook) from drop down a message box should pop up
saying "this Number should go in Orangeworksheet"

--

Dave Peterson

I have pasted this in the earlier code.

- Even though the value selected is for the correctworksheet, i am
getting the message which i should not

- Its not clearing the data after clicking "OK" on message box"

- As the Coloum A data validation list in all worksheets is from
sheets3 column A. Below code is not required

if iserror(res) then
* *msgbox "Not found on sheet3"

--

Dave Peterson- Hide quoted text -

- Show quoted text -

How do i aviod the message if the value selected is for the correct
sheet ?

--

Dave Peterson- Hide quoted text -

- Show quoted text -

I have edited the code as shown below and pasted in "ThisWorkbook" but
still the same. Its poping up the message for the selected values.

Dim res as variant
res _
*= application.vlookup(target.value,worksheets("Sheet 3").range("A:B"),
2,false)

if lcase(sh.name) = lcase(res) then
* 'no message required
else
* msgbox "This Number should go in " & res & "worksheet."
Target.Clearcontents
end if

--

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
Appleworksheet" and clear contents(or insert the value in last row of
Appleworksheet) else if it shows "Orange" in Sheet3 Column B then no
message box

--

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- 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 correctworksheetupon 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- 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- 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