PDA

View Full Version : Need help to create a search function that searches through sheets



Christos79
02-23-2008, 05:45 AM
Hey

I have a problem.

I need a search function that searches through Excels sheets and returns the cell I was looking for in a combobox. The code I have now just works for the the active sheet.

One other problem... I need a search function that searches through the comments on every cell on every sheet and returns the cells with the common text in the comment in a combobox

mdmackillop
02-23-2008, 05:48 AM
Hi Christos,
Welcome to VBAX
If you can post a simple sample with your combobox references etc. it's easier to make sure it's working for you.
Use Manage Attachments in the Go Advanced section
Regards
MD

Christos79
02-23-2008, 06:13 AM
Hey and thanx!

Here is the code for the search function that just works for the first sheet:



Dim datatoFind
Dim sheetCount As Integer
Dim counter As Integer
Dim currentSheet As Integer

kategori.Text = " "

If film.Text = " " Then
Exit Sub
End If


currentSheet = ActiveSheet.Index

datatoFind = film.Text

If datatoFind = "" Then Exit Sub

sheetCount = ActiveWorkbook.Sheets.Count

For counter = 1 To sheetCount
Sheets(counter).Activate
If IsError(Cells.Find(What:=datatoFind, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate) = False Then Exit For

Next counter
If film.Text = film.Text Then handling.Text = ActiveCell.Comment.Text

handling.MultiLine = True

If film.Text = film.Text Then kategori.Text = Range("A1")

kategori.MultiLine = True


And for the second search I just have this (?cause I haven?t found a way to solve how to make it searxh through all of the comments on all of the sheets and show in a textbox with cells that have the common comment)


If ActiveCell.Comment.Text = hittaskadespelare8.Text Then infoomskadespelare4.Text = "Sk?despelaren " & hittaskadespelare8.Text & " ?r med i filmen/filmerna:" & vbCrLf & vbCrLf & vbCrLf & ActiveCell.Text & " kategori: " & Range("A1") & vbCrLf

mdmackillop
02-23-2008, 06:29 AM
Private Sub film_AfterUpdate()
DoFind
End Sub

Sub DoFind()
Dim datatoFind As String
Dim c As Range
Kategori.Text = " "
If film.Text = " " Then
Exit Sub
End If
datatoFind = film.Text
If datatoFind = "" Then Exit Sub
For Each sh In Worksheets
Set c = sh.Cells.Find(What:=datatoFind, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False)
If Not c Is Nothing Then Exit For
Next sh
If film.Text = film.Text Then handling.Text = c.Comment.Text
handling.MultiLine = True
If film.Text = film.Text Then Kategori.Text = Range("A1")
Kategori.MultiLine = True
End Sub

Christos79
02-23-2008, 07:51 AM
Thanx.

by the way can you help me with the other problem? where I wanted to search something that is in a comment and then return which cells has that comment in common?

Christos79
02-23-2008, 07:57 AM
I just tested the code you gave me... it doesn?t work all the way... it searches through the sheets but it doesn?t activate the cell I was looking for or even go to that specific sheet where the cell is.

mdmackillop
02-23-2008, 08:13 AM
Hey

I have a problem.

I need a search function that searches through Excels sheets and returns the cell I was looking for in a combobox. The code I have now just works for the the active sheet.

One other problem... I need a search function that searches through the comments on every cell on every sheet and returns the cells with the common text in the comment in a combobox
I answered this question, based on your own code, not the one you are asking now.
I don't understand this part.
returns the cells with the common text in the comment in a combobox
What is Common Text?
Return what, Values, Addresses?

Christos79
02-23-2008, 08:29 AM
sorry. What I meant was... for example if I have two sheets with a lots of cells in excel with commentaries stuck to each and one of them... I want to have a search function so I can see if I have written the same commentary for more then one cell. it can be numers and text in the commentary fields.


I must have refrazed myself real bad before... the first thing you helped me with... which I appreciate... just worked half the way. It searched through the sheets but it didn?t do one thing that the sample I gave you did. The sample "marked" the cell I searched for to let me know where it was but it didn?t go to the other sheets. The code you gave me searched through the sheets but it didn?t "mark" the cell I looked for to let me know where it was and it didn?t also let me search for more cells with the same word in the beginning.

if I still don?t explain it good enough let me know, okay? =)

mdmackillop
02-23-2008, 08:46 AM
To activate the found cell, just add

sh.activate
c.activate

after the loop.
To return comments, have a look at this recent thread (http://www.vbaexpress.com/forum/showthread.php?t=17775&highlight=comment)

Christos79
02-23-2008, 08:57 AM
Thanx! Now it worked as it should :D thanx for all your help!!!

Christos79
02-23-2008, 09:07 AM
One last thing though...
...if I have the same name in two sheets how do I make the search function look for it. for example if I have the name Chris in sheet a and in sheet c how do I search for both? I tried but it only finds the name in sheet a.. every time

Christos79
02-23-2008, 09:08 AM
Oops! the last reply came up more then one time... sorry!

mdmackillop
02-23-2008, 09:13 AM
You can find them by not exiting the loop and usin FindNext (see VBA Help) but also:
Can there be more than one found cell per sheet?
How do you want them "marked"? You can only have one active sheet/cell.

Christos79
02-23-2008, 09:19 AM
with marked I meant active cell... and that part works perfect thanx to you :)

Yes there can be more than one found cell per sheet. But that works.. the only time it won?t work is if the other foudn cell is on another sheet... but that is a small problem. the bigger ones are solved thanx to you. thanx again :D

mdmackillop
02-23-2008, 09:50 AM
This will colour all found cells, but I've disabled the last bit of code due to multiple results. The code will clear all cell colouring though.

Sub DoFind()
Dim datatoFind As String, sh As Worksheet
Dim FirstAddress As String
Dim c As Range
Kategori.Text = " "
If Film.Text = " " Then
Exit Sub
End If
datatoFind = Film.Text
If datatoFind = "" Then Exit Sub
For Each sh In Worksheets
sh.Activate
sh.Cells.Interior.ColorIndex = xlNone
FirstAddress = ""
With sh.Cells
Set c = .Find(What:=datatoFind, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
If Not c Is Nothing Then
FirstAddress = c.Address
Do
c.Interior.ColorIndex = 8
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> FirstAddress
End If
End With
Next
Exit Sub

If Film.Text = Film.Text Then handling.Text = c.Comment.Text
handling.MultiLine = True
If Film.Text = Film.Text Then Kategori.Text = Range("A1")
Kategori.MultiLine = True
End Sub

Christos79
02-23-2008, 10:04 AM
Thanx ! :)

Christos79
02-24-2008, 01:10 AM
Hey again!

I was just wondering... if I want to insert the FindNext in the first code that you wrote.. how much must I change the original code or can I just insert it?

in this code I mean:


Private Sub film_AfterUpdate()
DoFind
End Sub

Sub DoFind()
Dim datatoFind As String
Dim c As Range
Kategori.Text = " "
If film.Text = " " Then
Exit Sub
End If
datatoFind = film.Text
If datatoFind = "" Then Exit Sub
For Each sh In Worksheets
Set c = sh.Cells.Find(What:=datatoFind, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False)
If Not c Is Nothing Then Exit For
Next sh
If film.Text = film.Text Then handling.Text = c.Comment.Text
handling.MultiLine = True
If film.Text = film.Text Then Kategori.Text = Range("A1")
Kategori.MultiLine = True
End Sub

Christos79
02-26-2008, 11:59 AM
Hey. I was wondering if you could help me with the findnext function. I tried to put it into the code but I get error all the time.

mdmackillop
02-26-2008, 12:57 PM
Post 15 contains FindNext. What am I missing?

Christos79
02-26-2008, 09:51 PM
Like I said before.. The search function works fine BUT when the datatofind lies on more then it one sheet then it doesn?t look it up. it jsut looks up the first one, so when I press the button to find the next one (for example the name chris... say that I have it on 3-4 sheets, the search function just finds the first one, it?s doesn?t look for the other ones) it doesn?t continue the search on that word

Christos79
02-27-2008, 10:09 AM
WIll the findnext that you have in post 15 work in the code on post 17 or must that code be rewritten?

mdmackillop
02-27-2008, 10:17 AM
It's very similar. The question is: what action you take when you find each occurrence? FindNext will end up showing the last instead of the first occurrence. Can you clariffy and I'll amend the code accordingly.

Christos79
02-27-2008, 10:41 AM
Ok.. it?s like this. I have around 10 sheets... The code you gave me on post 17 works perfectly when I jsut search for one name.then it shows me the sheet and the cell where that name is. When I search for a name that is on 2-3 sheets then the code doesn?t work. it just shows me the first one. WHen I press the search but on my userform to look for the other cells where the same name is located nothing happens it won?t move from where it was. What I want is a search function that does what this code on post 17 does BUT also can look for a name that is on more then 1 sheet.

mdmackillop
02-27-2008, 03:22 PM
I don't want to appear difficult, but what happerns when you find a cell?

Christos79
02-27-2008, 09:12 PM
If I search for a name that exist on several sheets it only finds the first cell then it stops, norhing happens. Until I search for a different name, then it goes to that cell.

I?m not sure if I explain my problem very good. What I?m trying to say is that I want the search function to work the same way the findnext funtion on excel works. it looks up the name you looked for and when you press the findnext it continues to search for that one until there is no more sheets containing that name, that I searched for.

Christos79
02-27-2008, 10:03 PM
Let me add somethine to my last reply.

My excel list is a list over my DVDs. so I have different sheets for different categories. the code on post 17 works fine when you want to search for a movie that only exist on one categori or when you search for a movie that exist more then 1 time on one specific category, like young guns for example ?cause it?s in the same place. BUT if you search for example after the movie hercules then it would be a problem.. ?cause I have it on two different sheets, the movie hercules in new yourk is on the sheet action while the movie hercules is in the adventure category. When I search for the name hercules I just get the sheet Action and the movie Hercules in new york. when I push the find next button that I created in my findfilmuserform then it doesn?t continure until I search for a different movie

Christos79
03-06-2008, 10:01 AM
I tried many diffrent ways to make the code that you gave me search for the same name on different sheets but still nothing.