Find value in Multiple Sheets

[jlclyde]

I am trying to find a vlaue in multiple sheets. It will only be
listed once. I just do not know what sheet it will appear on. Thanks
is advance for the help. I am doing this in VBA.

Thanks,
Jay

 

[jlclyde]

After rereading my post I realized that I need to clarify a couple of
things.
The sheet names are going to be four digit years 2008, 2009 etc. The
number that I want to find is going to be in one of the sheets that's
name is This Year or last year. Here is the code I have so far. It
doesn't work, but it is what I have.

Thanks,
Jay

Sub AddFormsRunButton()
Dim TrkBk As Workbook
Dim SentPrepRow As Long
Dim Sht As Worksheet
Dim Item As String

Set TrkBk = Workbooks.Open("G:\New Items\Tracking Lists\New Item
Tracking Log.xls")

If TrkBk.Sheets(Year(Date)).Range("C4:C3000").Find(Item) Is
Nothing Then
Set Sht = TrkBk.Sheets(Year(Date) - 1)
Else
Set Sht = TrkBk.Sheets(Year(Date))
End If

ActiveSheet.Shapes("button 88").Visible = True
End Sub

 

[Dave Peterson]

Option Explicit
Sub AddFormsRunButton()
Dim TrkBk As Workbook
Dim SentPrepRow As Long
Dim Sht As Worksheet
Dim myItem As String

Dim FoundCell As Range
Dim myYear As Long
Dim yCtr As Long
Dim HowManyYearsToCheck As Long

Set TrkBk = _
Workbooks.Open("G:\New Items\Tracking Lists\New Item Tracking Log.xls")

myItem = "qwer"

HowManyYearsToCheck = 2
For yCtr = (Year(Date) - 1) To (Year(Date) - 1 + HowManyYearsToCheck - 1)
Set Sht = Nothing
On Error Resume Next
Set Sht = TrkBk.Worksheets(CStr(yCtr))
On Error GoTo 0
If Sht Is Nothing Then
'worksheet doesn't exist, do nothing
Else
With Sht.Range("c4:c3000")
Set FoundCell = .Cells.Find(what:=myItem, _
after:=.Cells(.Cells.Count), _
lookat:=xlWhole, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)
If FoundCell Is Nothing Then
'keep looking
Else
Exit For
End If
End With
End If
Next yCtr

If FoundCell Is Nothing Then
MsgBox myItem & " wasn't found"
Else
MsgBox Sht.Name & vbLf _
& FoundCell.Parent.Name & vbLf _
& FoundCell.Address(0, 0)
End If

'I'm not sure what this is supposed to do.
'ActiveSheet.Shapes("button 88").Visible = True
End Sub

 

[Dave Peterson]

Sub AddFormsRunButton()
Dim TrkBk As Workbook
Dim SentPrepRow As Long
Dim Sht As Worksheet
Dim Item As String

Set TrkBk = Workbooks.Open("G:\New Items\Tracking Lists\New Item
Tracking Log.xls")

If TrkBk.Sheets(Year(Date)).Range("C4:C3000").Find(Item) Is
Nothing Then
Set Sht = TrkBk.Sheets(Year(Date) - 1)
Else
Set Sht = TrkBk.Sheets(Year(Date))
End If

ActiveSheet.Shapes("button 88").Visible = True
End Sub

 

[Dave Peterson]

Ignore this message. It's just a copy of your original code.