Find Value in Workbook

  • Thread starter Thread starter Johnny
  • Start date Start date
J

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: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, 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:D").Find(what:=Range("J3").Value)" is highlighted when I debug.


Please advise.

Thanks


joel said:
VBA Code:
Click to expand...
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: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
Click to expand...
--------------------


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

Excel Live Chat

.
Click to expand...
 
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.
 
Back
Top