Ad:

Visual Basic 4 / 5 / 6 Discussion Thread
Marked Solved
Views: 28053

Mar 2nd, 2006

Excel Search Macro Help

Expand Post »

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

Similar Threads

Excel Macro VBA Help in Visual Basic 4 / 5 / 6

HI2Japan

Reputation Points: 35

Solved Threads: 21

Junior Poster

Offline

195 posts
since Mar 2006

Permalink

Mar 3rd, 2006

Re: Excel Search Macro Help

HI2Japan,

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

Try this out

Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text)
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
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

Reputation Points: 16

Solved Threads: 10

Junior Poster

Offline

134 posts
since Nov 2005

Permalink

Mar 3rd, 2006

Re: Excel Search Macro Help

A slightly more elegant way would be

Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text)
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
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

Reputation Points: 16

Solved Threads: 10

Junior Poster

Offline

134 posts
since Nov 2005

Permalink

Mar 3rd, 2006

Re: Excel Search Macro Help

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

HI2Japan

Reputation Points: 35

Solved Threads: 21

Junior Poster

Offline

195 posts
since Mar 2006

Permalink

Mar 5th, 2006

Re: Excel Search Macro Help

Yomet,

Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text)
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
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

Last edited by Comatose; Mar 5th, 2006 at 10:42 pm. Reason: Code Tags

HI2Japan

Reputation Points: 35

Solved Threads: 21

Junior Poster

Offline

195 posts
since Mar 2006

Permalink

Mar 5th, 2006

Re: Excel Search Macro Help

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

Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text)
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
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

Last edited by Comatose; Mar 5th, 2006 at 10:43 pm. Reason: Yeah Know, Code Tags

HI2Japan

Reputation Points: 35

Solved Threads: 21

Junior Poster

Offline

195 posts
since Mar 2006

Permalink

Mar 5th, 2006

Re: Excel Search Macro Help

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

Yomet

Reputation Points: 16

Solved Threads: 10

Junior Poster

Offline

134 posts
since Nov 2005

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in Visual Basic 4 / 5 / 6 Forum Timeline: MDI forms and their Children

Next Thread in Visual Basic 4 / 5 / 6 Forum Timeline: Begin transaction in VB?

DaniWeb Message

Cancel Changes

User Name
Password