Find Value in Workbook

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
 
J

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



--------------------
 
J

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


Please advise.

Thanks


joel said:
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: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

.
 
J

joel

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

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

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.
 
J

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top