Excel 97 search macro help please

[Chris Hankin]

Hello,

I am trying to get the below macro to search column H of my workbook. It
works fine but i'd need it to also search columns: J, L, N, P, R, T. All
these columns have asset numbers.

Could someone please help me modifiy my existing code so that this may
happen.

All help is greatly appreciated.

Kind regards,

Chris.




Sub AssetNoSearch()
'
'AssetNoSearch Macro
'
'
Dim Found As Range
Dim Sh As Worksheet
Dim SheetsToCheck As Variant
Dim AssetNumber As String


Application.ScreenUpdating = False

SheetsToCheck = Array("DTP Hardware")

AssetNumber = Application.InputBox("Please Enter The Asset Number You
Wish to Locate and either click on the <OK> button and then hit <ENTER> or
hit <ENTER> twice: ", _
"Find Asset Number")

For Each Sh In Sheets(SheetsToCheck)
Set Found = Sh.Columns("H").Find(AssetNumber, _
LookIn:=xlFormulas, _
Lookat:=xlPart)
If Not Found Is Nothing Then
Found.Parent.Activate
Found.Activate
Exit For
End If
Next Sh


If Found Is Nothing Then
MsgBox "Asset Number: " & AssetNumber & " does not exist."
End If
End Sub

 

[Charles Harmon]

Chris,
Not tested but try,
Option Base 1
Sub AssetNoSearch()
'
'AssetNoSearch Macro
'
'
Dim Found As Range
Dim Sh As Worksheet
Dim SheetsToCheck As Variant
Dim AssetNumber As String
Dim myarray As Variant
Dim mycnt As Integer
myarray = Array("H", "L", "N", "P", "R", "T")
Application.ScreenUpdating = False

SheetsToCheck = Array("DTP Hardware")

AssetNumber = Application.InputBox("Please Enter The Asset Number You _
Wish to Locate and either click on the <OK> button and then hit <ENTER> or _
hit <ENTER> twice: ", _
"Find Asset Number")
For mycnt = 1 To UBound(myarray)
For Each Sh In Sheets(SheetsToCheck)
Set Found = Sh.Columns(myarray(mycnt)).Find(AssetNumber, _
LookIn:=xlFormulas, _
Lookat:=xlPart)
If Not Found Is Nothing Then
Found.Parent.Activate
Found.Activate
Exit For
End If
Next Sh
Next mycnt
If Found Is Nothing Then
MsgBox "Asset Number: " & AssetNumber & " does not exist."
End If
End Sub

 

[Chris Hankin]

Thanks Charles for you prompt reply - much appreciated.

Unfortunately, I was unable to get your modified code to work. I even
tried using the F8-key in the Visual Basic Editor of Excel 97 and went
through line-by-line of code.

I am not sure but possibly myarray does not work all that well - but I
am only taking a educated guess.

I was wondering if maybe you could please take another look at it or on
your request, I could send you my spreadsheet to look at - it's very
basic.

Kind regards,

Chris.

Live Long and Prosper

 

[Guest]

does it help to put the mycnt loop inside of the Sh loop:

For Each Sh In Sheets(SheetsToCheck)
For mycnt = 1 To UBound(myarray)

Set Found = Sh.Columns(myarray(mycnt)).Find(AssetNumber, _
LookIn:=xlFormulas, _
Lookat:=xlPart)
If Not Found Is Nothing Then
Found.Parent.Activate
Found.Activate
Exit For
End If

Next mycnt
Next Sh

 

[Chris Hankin]

Thanks Gocush - that loop worked very well - thanks very much - greatly
appreciated.

Kind regards,

Chris.

Live Long and Prosper