Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 97 search macro help please
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 97 search macro help please
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 97 search macro help please
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 :) *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 97 search macro help please
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 "Charles Harmon" wrote: 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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 97 search macro help please
Thanks Gocush - that loop worked very well - thanks very much - greatly
appreciated. Kind regards, Chris. Live Long and Prosper :) *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I set up a search macro in Excel? | Excel Discussion (Misc queries) | |||
Search, Copy, Paste Macro in Excel | Excel Worksheet Functions | |||
Excel XP VBA code to search all macro code in Excel module for specific search string criteria | Excel Programming | |||
Excel XP VBA code to search all macro code in Excel module for specific search string criteria | Excel Programming | |||
Excel VBA to search all macro code in Excel module for specific string criteria | Excel Programming |