Advice and Tips for instr & for Findnext

[Christos79]

Hey!

I?m kinda new to this macro/vba programming so I?ll need some help. I?m trying to create a DVDlist for my movies with a lots of sheets, one sheet per category. And each category has one column where every cell is a movie title.. in alphabetical order. And every cell has a comment attached to it. In the comment field I have Actors, Plot, Directo and so on (I will explain it further down). I have it this way ?cause I want the list to work like a list without the macro but also have the possibility to have a GUI where I can [COLOR=blue ! important][COLOR=blue ! important]search [COLOR=blue ! important]for [/COLOR][COLOR=blue ! important]movies[/COLOR][/COLOR][/COLOR], actors and so on. What I need help with is with 2 problems. I know that a database would be more suitable for this kind of stuff but I reall would like to try this first. Plz help?
first problem) How do I search through a part of a comment and stop on a particular part. On the text that follows show how my comments can look like.

[COLOR=blue ! important][COLOR=blue ! important]Marvel [COLOR=blue ! important]Comics[/COLOR][/COLOR][/COLOR], 2005

Speltid: ca 1h 39min Klassad: PG-13

Region 1, NTSC

Ljud: Engelska 5.1 och DTS

Text: Engelska & Spanska

Regi: Rob Bowman

Sk?despelare: [COLOR=blue ! important][COLOR=blue ! important]Jennifer [COLOR=blue ! important]Garner[/COLOR][/COLOR][/COLOR], Goran Visnjic, Terence Stamp & Cary-Hiroyuki
Tagawa

Handling: Hon l?mnas f?r att d?. Men var l?ngt ifr?n d?d. Nu s?ker hon
h?mnd. [COLOR=blue ! important][COLOR=blue ! important]Elektras[/COLOR][/COLOR] h?mndbeg?r leder till en karri?r som
yrkesm?rdare och hon f?r snabbt ett rykte om sig att vara den
b?sta och d?dligaste i sitt slag. Hon f?r ett uppdrag som g?r att
hon f?r f?rsta g?ngen ifr?gas?tter sina val och f?r anv?nda all sin
skicklighet d? hon st?lls mot den farligaste motst?ndare hon
n?gonsin m?tt..

How Can I start the search from the beginning in the comment but make it stop on the word Text: ?

And also how can I make it start at the word Text: and stop at the word Regi: ?

This is how my code looks like:

Code:

Dim sh As Worksheet
Dim rngA As Range
Dim strA As String

If taltext.text = "" Then
Exit Sub
End If

If OptionButton55 Then
For Each sh In Worksheets
For Each rngA In sh.Columns(1).Cells.SpecialCells(xlCellTypeComments)
If InStr(1, rngA.Comment.text, taltext.text, vbTextCompare) > 0 Then
strA = strA & vbCr & vbCr & "Kategori: " & sh.Range("A1") & vbCr & "-----------------------------------------------------------------------------------------------" & vbCr & vbCr & rngA.Value & vbCr & vbCr
infoomtaltext.Value = strA
ElseIf InStr(1, rngA.Comment.text, "Text:") = 1 Then Exit For
End If
Next rngA
Next sh
End If
infoomtaltext.MultiLine = True


If OptionButton56 Then
For Each sh In Worksheets
For Each rngA In sh.Columns(1).Cells.SpecialCells(xlCellTypeComments)
If InStr(50, rngA.Comment.text, taltext.text, vbTextCompare) > 0 Then
strA = strA & vbCr & vbCr & "Kategori: " & sh.Range("A1") & vbCr & "-----------------------------------------------------------------------------------------------" & vbCr & vbCr & rngA.Value & vbCr & vbCr
infoomtaltext.Value = strA
ElseIf InStr(50, rngA.Comment.text, "Regi:") = 1 Then Exit For
End If
Next rngA
Next sh
End If

second problem) How do I make this code be able to search for the same name on the other sheets as well? I mean if I search for example after the movie blade, I just get the movie [COLOR=blue ! important][COLOR=blue ! important]blade [COLOR=blue ! important]runner[/COLOR][/COLOR][/COLOR] that exists in the SciFi sheet but when I push the next button it doesn?t go on to look for another movie with the word blade in. That happens only if the movies containing the word blade exist in the same sheet. How do I make it work for the other sheets as well? I tried to use findnext but I do something wrong. plz help?


Code:

Dim datatoFind As String
Dim c As Range
Dim counter As Integer
Dim currentSheet As Integer

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

sh.Activate
c.Activate

If film.text = film.text Then handling.text = c.Comment.text
If film.text = film.text Then kategori.text = Range("A1")
kategori.MultiLine = True
handling.MultiLine = True

[mdmackillop]

Can you post a small sample of your workbook?

[mdmackillop]

Hi Christos,
Rather than comments, here is another method using Data Validation maybe worth looking at in answer to this question.

[Christos79]

to mdmackillop: I read the post you mentioned but I didn?t understand exactly how it whould help. could you explain it plz, it sounded interesting =)

[mdmackillop]

I'm not totally clear on all the functionality. This was not my solution to the question. It uses cells with the same address on a second sheet to store the data which appears in the comment. This would appear to offer a cleaner method for inserting/editing text. Also, seaching in sheet2 will return the address for the relevant cell in sheet 1.

[Christos79]

I have attached a sample version of my excel file, where only 2 sheets exist. But the both have one word common that exist on both sheets. It?s the word "blade" If I use the find code it find only the first one... in other words the word blade that exists on the first sheet... it never searches the other sheet when I press the search button a second time.

But how do I make the code that is for the comment to start at the word "Tal:" and stop at the word "Text:" and how do I also make the last part of the code start at the word "text:" and stop at the word "Regi:" ?