Christos79
04-05-2008, 06:23 AM
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 search for movies (http://www.excelforum.com/showthread.php?p=1900750#), 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.
Marvel Comics (http://www.excelforum.com/showthread.php?p=1900750#), 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: Jennifer Garner (http://www.excelforum.com/showthread.php?p=1900750#), 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. Elektras (http://www.excelforum.com/showthread.php?p=1900750#) 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 blade runner (http://www.excelforum.com/showthread.php?p=1900750#) 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
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 search for movies (http://www.excelforum.com/showthread.php?p=1900750#), 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.
Marvel Comics (http://www.excelforum.com/showthread.php?p=1900750#), 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: Jennifer Garner (http://www.excelforum.com/showthread.php?p=1900750#), 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. Elektras (http://www.excelforum.com/showthread.php?p=1900750#) 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 blade runner (http://www.excelforum.com/showthread.php?p=1900750#) 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