On Oct 13, 1:26*am, Hasan wrote:
On Oct 10, 12:40*pm, Joel wrote:



See if this helps. *I used the VBA find instead of the worksheet function
VLookup.

Private Sub Workbook_SheetChange(ByVal Sh As Object, _
* *ByVal Target As Range)
* * Dim wsLoop As Worksheet
* * Dim FoundCell As Range
* * Dim myAddr As String
* * Dim TopRng As Range
* * Dim BotRng As Range
* * Dim BigRng As Range
* * Dim LastRow As Long
* * Dim FirstRow As Long
* * Dim res As Variant

* * myAddr = "A2:A2000"
* * With Sh.Range(myAddr)
* * * * FirstRow = .Row
* * * * LastRow = .Rows(.Rows.Count).Row
* * End With

* * If Intersect(Target, Sh.Range(myAddr)) Is Nothing Then
* * * * Exit Sub
* * End If

* * If Target.Cells.Count 1 Then
* * * * Exit Sub 'single cell at a time
* * End If

Application.EnableEvents = False

If Target.Value = "" Then
'do nothing
Else
* * For Each wsLoop In ThisWorkbook.Worksheets
* * * * Select Case LCase(wsLoop.Name)
* * * * * * Case Is = LCase("Sheet3")
* * * * * * * * 'skip it
* * * * * * Case Else
* * * * * * * * Set BigRng = wsLoop.Range(myAddr)
* * * * * * * * If LCase(wsLoop.Name) = LCase(Sh.Name) Then
* * * * * * * * * * With BigRng
* * * * * * * * * * * * If Target.Row = FirstRow Then
* * * * * * * * * * * * * * 'in row 2, don't include it
* * * * * * * * * * * * * * Set BigRng = _
* * * * * * * * * * * * * * * *.Resize(..Rows.Count - 1).Offset(1, 0)
* * * * * * * * * * * * Else
* * * * * * * * * * * * * * If Target.Row = LastRow Then
* * * * * * * * * * * * * * * * 'in row 200, don't include it
* * * * * * * * * * * * * * * * Set BigRng = .Resize(.Rows.Count - 1)
* * * * * * * * * * * * * * Else
* * * * * * * * * * * * * * * * Set TopRng = _
* * * * * * * * * * * * * * * * * *wsLoop.Range("A" & FirstRow & _
* * * * * * * * * * * * * * * * * * * ":A" & Target.Row - 1)
* * * * * * * * * * * * * * * * Set BotRng = _
* * * * * * * * * * * * * * * * * *wsLoop.Range("A" & Target.Row + 1 & _
* * * * * * * * * * * * * * * * * * * ":A" & LastRow)
* * * * * * * * * * * * * * * * Set BigRng = Union(TopRng, BotRng)
* * * * * * * * * * * * * * End If
* * * * * * * * * * * * End If
* * * * * * * * * * End With
* * * * * * * * End If

* * * * * * * * With BigRng
* * * * * * * * * * Set FoundCell = .Find(What:=Target.Value, _
* * * * * * * * * * * * * * * * * * * *After:=.Cells(1), _
* * * * * * * * * * * * * * * * * * * *LookIn:=xlValues, _
* * * * * * * * * * * * * * * * * * * *lookat:=xlWhole, _
* * * * * * * * * * * * * * * * * * * *SearchOrder:=xlByRows, _
* * * * * * * * * * * * * * * * * * * *SearchDirection:=xlNext, _
* * * * * * * * * * * * * * * * * * * *MatchCase:=False)
* * * * * * * * End With

* * * * * * * * If FoundCell Is Nothing Then
* * * * * * * * * * 'not found
* * * * * * * * Else
* * * * * * * * * *MsgBox "That entry already exists he" & vbLf _
* * * * * * * * * * * *& FoundCell.Address(external:=True)
* * * * * * * * * *Application.EnableEvents = False
* * * * * * * * * *Target.ClearContents

* * * * * * * * * *With Worksheets("Sheet3")
* * * * * * * * * * * Set c = .Range("A").Find(What:=Target, _
* * * * * * * * * * * * *LookIn:=xlValues, lookat:=xlWhole)
* * * * * * * * * * * If c Is Nothing Then
* * * * * * * * * * * * *'no message
* * * * * * * * * * * Else
* * * * * * * * * * * * *res = LCase(.Range("R" & c.Row))
* * * * * * * * * * * * *If LCase(Sh.Name) = res Then
* * * * * * * * * * * * * * 'do nothing
* * * * * * * * * * * * *Else
* * * * * * * * * * * * * * MsgBox Target.Value & " should be on " & res
* * * * * * * * * * * * * * Col_D = LCase(.Range("D" & c.Row))
* * * * * * * * * * * * * * Col_G = LCase(.Range("G" & c.Row))
* * * * * * * * * * * * * * Col_F = LCase(.Range("F" & c.Row))
* * * * * * * * * * * * * * With wsLoop
* * * * * * * * * * * * * * * * .Range("C" & FoundCell.Row) = Col_D
* * * * * * * * * * * * * * * * .Range("D" & FoundCell.Row) = Col_G
* * * * * * * * * * * * * * * * .Range("E" & FoundCell.Row) = Col_F
* * * * * * * * * * * * * * *End With
* * * * * * * * * * * * * End If

* * * * * * * * * * * * * Exit For
* * * * * * * * * * *End If
* * * * * * * * * End With
* * * * * * * *End If
* * * * End Select
* * Next wsLoop
End If

Application.EnableEvents = True

End Sub

"Hasan" wrote:
Hi,

Functionality of below macro :

Search for the selected value from the data validation list(from
Sheet3ColumnA) in the entire workbook(except Sheet3) and if found
then

1. Shows message "Value already exists in sheet" and select that cell
where the value exists

2. Checks for its correspondingvaluesin Sheet3columnB. Say if its
apple then shows message "this is be on apple sheet"

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxx

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
* * Dim wsLoop As Worksheet
* * Dim FoundCell As Range
* * Dim myAddr As String
* * Dim TopRng As Range
* * Dim BotRng As Range
* * Dim BigRng As Range
* * Dim LastRow As Long
* * Dim FirstRow As Long
* * Dim res As Variant

* * myAddr = "A2:A2000"
* * With Sh.Range(myAddr)
* * * * FirstRow = .Row
* * * * LastRow = .Rows(.Rows.Count).Row
* * End With

* * If Intersect(Target, Sh.Range(myAddr)) Is Nothing Then
* * * * Exit Sub
* * End If

* * If Target.Cells.Count 1 Then
* * * * Exit Sub 'single cell at a time
* * End If

If Target.Value = "" Then
'do nothing
Else
* * For Each wsLoop In ThisWorkbook.Worksheets
* * * * Select Case LCase(wsLoop.Name)
* * * * * * Case Is = LCase("Sheet3")
* * * * * * * * 'skip it
* * * * * * Case Else
* * * * * * * * Set BigRng = wsLoop.Range(myAddr)
* * * * * * * * If LCase(wsLoop.Name) = LCase(Sh.Name) Then
* * * * * * * * * * With BigRng
* * * * * * * * * * * * If Target.Row = FirstRow Then
* * * * * * * * * * * * * * 'in row 2, don't include it
* * * * * * * * * * * * * * Set BigRng = .Resize(.Rows.Count -
1).Offset(1, 0)
* * * * * * * * * * * * Else
* * * * * * * * * * * * * * If Target.Row = LastRow Then
* * * * * * * * * * * * * * * * 'in row 200, don't include it
* * * * * * * * * * * * * * * * Set BigRng = .Resize(.Rows.Count - 1)
* * * * * * * * * * * * * * Else
* * * * * * * * * * * * * * * * Set TopRng = wsLoop.Range("A" &
FirstRow _
* * * * * * * * * * * * * * * * * * * * * * * * & ":A" & Target.Row -
1)
* * * * * * * * * * * * * * * * Set BotRng = wsLoop.Range("A" &
Target.Row + 1 _
* * * * * * * * * * * * * * * * * * * * * * * * & ":A" & LastRow)
* * * * * * * * * * * * * * * * Set BigRng = Union(TopRng, BotRng)
* * * * * * * * * * * * * * End If
* * * * * * * * * * * * End If
* * * * * * * * * * End With
* * * * * * * * End If

* * * * * * * * With BigRng
* * * * * * * * * * Set FoundCell = .Cells.Find(what:=Target.Value, _
* * * * * * * * * * * * * * * * * * * * * * * * After:=.Cells(1), _
* * * * * * * * * * * * * * * * * * * * * * * * LookIn:=xlValues, _
* * * * * * * * * * * * * * * * * * * * * * * * LookAt:=xlWhole, _
* * * * * * * * * * * * * * * * * * * * * * * * SearchOrder:=xlByRows,
_

SearchDirection:=xlNext, _
* * * * * * * * * * * * * * * * * * * * * * * * MatchCase:=False)
* * * * * * * * End With

* * * * * * * * If FoundCell Is Nothing Then
* * * * * * * * * * 'not found
* * * * * * * * Else
* * * * * * * * * * *MsgBox "That entry already exists he" & vbLf _
* * * * * * * * * * * * & FoundCell.Address(external:=True)
* * * * * * * * * * Application.EnableEvents = False
* * * * * * * * * * Target.ClearContents
* * * * * * * * * * Application.Goto FoundCell, Scroll:=True 'or
false??
* * * * * * * * * * Application.EnableEvents = True
* * * * * * * * * * Exit For
* * * * * * * * End If
* * * * End Select
* * Next wsLoop

* * * res _
*= Application.VLookup(Target.Value, Worksheets("Sheet3").Range
("A:R"), 18, False)
If IsError(res) Then
* 'no message
Else
* *If LCase(Sh.Name) = LCase(res) Then
* * * *'do nothing
* *Else
* * * MsgBox Target.Value & " should- Hide quoted text -

- Show quoted text -...

read more »

How do i vlookup 2 columns(say sheet1 & sheet2 column A) in different
sheet and get it corresponding sheet 2 column D value in sheet 1 ?