Find Value in Workbook
 

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

Find Value in Workbook
 

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/sh...d.php?t=186241

Excel Live Chat

Find Value in Workbook
 

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


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/sh...d.php?t=186241

Excel Live Chat

.

Find Value in Workbook
 

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

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


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

http://www.thecodecage.com/forumz/chat.php

Find Value in Workbook
 

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


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

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


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

http://www.thecodecage.com/forumz/chat.php

.

Find Value in Workbook
 

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


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

http://www.thecodecage.com/forumz/chat.php