Excel Search Macro Help

[HI2Japan]

Hi all,

I am a beginner at this so sorry for my ignorance.

I'm trying to make a macro that searches an Excel Spread sheet. I plan on using Data validation in cell A1 that the user selects the value they want to see, then I want them to be able to click a button roughly in cell A2 that will initiate the search through that worksheet only. I know how to make the butotn and assign a macro to it, I don't know how to make the macro search off of a value in a cell. The search just needs to take them to the specified location in the worksheet.

I do not know if this will be a difficult task, but all of my searching of google turned up nothing. Any help would be greatly appreciated.

Thank you

[Yomet]

HI2Japan,

I had this sitting from another posting here, just modified it slightly.

Try this out

Public Sub Test()
   Dim SR As Integer
   Dim ER As Integer
   Dim SC As Integer
   Dim EC As Integer
   Dim RowVar As Integer
   Dim ColVar As Integer
   Dim found As Boolean
   
'Change the indexes for your own Row range from 1 to 65536
   SR = 1
   ER = 20
'Change the indexes for your own Column range from 1 to 256 - in English from "A" to "IV"
   SC = 1
   EC = 10
   found = False
   For RowVar = SR To ER
      For ColVar = SC To EC
         If Not found Then 'This ensures that you stop at the first occurrence
            If Cells(RowVar, ColVar).Formula = "Angstrom" Then 'This is where your search text goes
               Cells(RowVar, ColVar).Select
               found = True
            End If
         End If
      Next
   Next
End Sub

Happy coding

Yomet

[Yomet]

A slightly more elegant way would be

Public Sub Test2()
   Dim c

   For Each c In ActiveSheet.Range("A1:J20")
      If c.Value = "Angstrom" Then 'This is where your search text goes
         c.Select
         Exit For
      End If
   Next
End Sub

Yomet

[HI2Japan]

Thank you for the replies, I will try them out at work on Monday and let you know how it goes.

[HI2Japan]

Yomet,

Public Sub Test2()
   Dim c

   For Each c In ActiveSheet.Range("A1:J300")
      If c.Value = Range("A1") Then 'This is where your search text goes
         c.Select
         Exit For
      End If
   Next
End Sub

I tried changing what you gave me to make it work by putting in the Range("A1"). The problem is, and I know it is doing what is programmed, It only selects cell A1. What I really want it to do, is search the worksheet based upon the value in cell A1. Cell A1 will change. I made a drop down list in cell A1 and I want the search to find whatever value the user selects from the list.

How do I modify this to make is search based off of cell A1?

Thank you in advance

Paul

[HI2Japan]

Nevermind, I got it. :surprised Thank you for all your help

Sub TESTING2()
        Cells.Find(What:=Range("A1"), After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
    Cells.FindNext(After:=ActiveCell).Activate
End Sub

[Yomet]

Happy to hear you found another way, by using the Macro recorder it looks like - good going!!

Anyway, just to clear up your problems with my code:
The range you sould have put in the line
For Each c In ActiveSheet.Range("A1:J300")
is the range you want to search, not the range A1 that contains the value you are looking for. If you put
Range("A1:A1")
then, of course, it will select A1 since it's the only cell in the search range.
The cell A1 will be selected because the If statement
If c.Value = Range("A1") Then
Will of course be true since it will read (substituting c for the range A1)
If A1.Value - Range("A1") Then

Just so you know.

Happy coding

Yomet