Find Value in Workbook

[Johnny]

I have the following VBA Code in the Active Worksheet to search for a value
(cell J3) in column( D) then activate the cell if found. I would like to
modify the code to search for the value in cell J3 in all worksheets in the
workbook, then have a prompt to activate that cell in the sheet it is located
or do another search for the same value. I realize I can do this by the Find
function but this would work much better for my application. Thank you

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Application.Intersect(Range("J3"), Target) Is Nothing Then

Dim FoundCell As Range

With ActiveSheet

Set FoundCell = .Range("D").Find(what:=Range("J3").Value)

If FoundCell Is Nothing Then
MsgBox "EIN not found. Do not include the dashes in your search."
Else
FoundCell.Activate
End If
End With
End If
End Sub

 

[joel]

VBA Code:
--------------------


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub


If Not Application.Intersect(Range("J3"), Target) Is Nothing Then

Dim FoundCell As Range
ShtName = target.parent.name

Found = False
for each sht in sheets
if Sht.name <> ShtName then
Data = Sht.Range("J3").value

if Data = target.value then
set DestLocation = Sht.Range("J3")
found = True
end if
end if
next sht
end if
Set FoundCell = .Range("D").Find(what:=Range("J3").Value)


If Found = false Then
MsgBox "EIN not found. Do not include the dashes in your search."
Else
DestLocation.parent.activate
DestLocation.select
End If
End Sub



--------------------

 

[Johnny]

Joel, thanks for your reply. I cut and pasted the code into th worksheet but
got the following error message:

"Complie error:
Invalid or unqualified reference"

'.Range' in the line of code "Set FoundCell =
..Range("D").Find(what:=Range("J3").Value)" is highlighted when I debug.


Please advise.

Thanks

VBA Code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub


If Not Application.Intersect(Range("J3"), Target) Is Nothing Then

Dim FoundCell As Range
ShtName = target.parent.name

Found = False
for each sht in sheets
if Sht.name <> ShtName then
Data = Sht.Range("J3").value

if Data = target.value then
set DestLocation = Sht.Range("J3")
found = True
end if
end if
next sht
end if
Set FoundCell = .Range("D").Find(what:=Range("J3").Value)


If Found = false Then
MsgBox "EIN not found. Do not include the dashes in your search."
Else
DestLocation.parent.activate
DestLocation.select
End If
End Sub

--------------------


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=186241

Excel Live Chat

.

 

[joel]

It was a line in your origianl code that I forgot to remove

remove
Set FoundCell = .Range("D").Find(what:=Range("J3").Value)

 

[Johnny]

I'm not gettng a runtime error this time but when I removed the code I'm
getting the "EIN Not found..." message box each time when I know the value
I'm searching searching for in cell J3 is in one of the other worksheets.

 

[joel]

I left out an exit statement

from
If Data = target.value Then
Set DestLocation = Sht.Range("J3")
found = True
End If

to
If Data = target.value Then
Set DestLocation = Sht.Range("J3")
found = True
Exit for
End If