Thread: Search macro

Hi,

I'm making an excel sheet for all the movies I own.

This gives you an idea of what it looks like:
http://i112.photobucket.com/albums/n...g/Knipsel1.jpg


I already made macros for sorting different columns.
Now I'm making a macro for searching values and highlight the the results.
This is what I got so far (partial Dutch):

Sub ZoekEnKleur()

Dim q As Range
Dim Findstr As String



Findstr = InputBox("Vul zoekterm in:", Title:="Zoeken") ' Enter search string

With Worksheets(1).Range("A2", [E999]) ' Reflect search range
Set q = .Find(Findstr, LookIn:=xlValues, Lookat:=xlPart)
If Not q Is Nothing Then
FirstAddress = q.Address
Do
q.Columns("A:G").Interior.ColorIndex = 36 ' Set color
Set q = .FindNext(q) ' Look for next occurence of search string
Loop While q.Address <> FirstAddress
End If
End With

End Sub

It already works pretty well but I get one small issue which I don't exactly know how to fix.

When the search string is found in an particular cell, I want the colums A:G of that row highlighted.
When my search string is a value in column A it works fine, but when the search string is a value in another column the result highlight moves to the right. Let me show you:

http://i112.photobucket.com/albums/n...g/Knipsel2.jpg
http://i112.photobucket.com/albums/n...g/Knipsel3.jpg

Could anyone help me with this?


Additionally I'd like a message box to pop up when there are no results found.
And I'd like it if Excel jumps to the row with the first result in it.

Thank you

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

Your code is highlighting the whole column, not the row of data

Sub ZoekEnKleur()

    Dim q As Range
    Dim Findstr As String



    Findstr = InputBox("Vul zoekterm in:", Title:="Zoeken")    ' Enter search string

    With Worksheets(1).Range("A2", [E999])    ' Reflect search range
        Set q = .Find(Findstr, LookIn:=xlValues, Lookat:=xlPart)
        If Not q Is Nothing Then
            FirstAddress = q.Address
            Do
                Range(Cells(q.Row, 1), Cells(q.Row, 7)).Interior.ColorIndex = 36 ' Set color
                Set q = .FindNext(q)    ' Look for next occurence of search string
            Loop While q.Address <> FirstAddress
        End If
    End With

End Sub

Hi,

Thanks for you reply! That indeed does the trick.

In the dummy worksheet the search function highlights the whole column because every cell in one column has the same value. So it's logical that it highlights every row. But in the real worksheet there are different values in the cells (e.a. genres: drama/action etc).


Do you, or anyone else also know a simple solution for these things I want to add to the macro?:

I'd like a message box to pop up when there are no results found.
And I'd like it if Excel jumps to the row with the first result in it.

Thanks!

*edit*
There occurs one problem with the new script you made:
When I don't fill any search word or just press cancel, it fills the empty rows, that are in range, yellow.
I know that when I change the range to exactly the amount of rows I use for the movies, this won't be the case. But I don't want to change the range in the macro everytime I add a new movie.

*edit 2*
I attached the original version of my worksheet just so you can see what I mean.

Hi,

As mentioned by royUK your existing code colors the cells. Here's the code with add line to exit if you do not enter data in the input box, and too if movie not found a msgbox will appear.

Sub ZoekEnKleur()

Dim q As Range
Dim Findstr As String



Findstr = InputBox("Vul zoekterm in:", Title:="Zoeken") ' Enter search string
If Findstr = "" Then Exit Sub ''' added this line
With Worksheets(1).Range("A9", [E999]) ' Reflect search range
Set q = .Find(Findstr, LookIn:=xlValues, Lookat:=xlPart)
If Not q Is Nothing Then
FirstAddress = q.Address
Do
Range(Cells(q.Row, 1), Cells(q.Row, 7)).Interior.ColorIndex = 36 ' Set color
Set q = .FindNext(q) ' Look for next occurence of search string
Loop While q.Address <> FirstAddress
Else
    MsgBox "Movie  " & Findstr & "  Not found"
End If
End With

End Sub

The code that I posted highlights the row containing the found text, yours didn't. I do actually have the sense to add different words to search for having noticed your data was all the same.

royUK,


If you assumed I was copying your code and trying to make it look like my work then I'm sorry I did not intend to do this I only wanted add some code to yours and I probably should have said so. I will do this in the future.

No Charles, I can see that you simply added the msgbox & check for blank entry. The OP seemed to imply that his code only coloured his columns because they all contained the same data, it did that because he specified columns, not the row.

royUK,

Thanks for letting me know. I will however when updating another person code give them credit for it.

Thanks both for your help so far. The new script works flawlessy.

@ RoyUK: At first I did not fully understand what you meant by "Your code is highlighting the whole column, not the row of data". But now I do, thanks for clarifying.

At last the only thing I'd like to see is that Excel jumps to the row which contains the first match with the search string.

Any ideas?

thanks


*edit* solved