Excel Macro Help

[Andreew]

Hi,

I know this forum isnt really the place for Excel Macro questions but i'm not sure where else to go :L

I have created an excel sheet which holds all the movie's I own and a macro which allows me to add a new movie, find a movie, delete a movie, etc.

The problem is when I search for a movie using my 'FIND' button and entering my search, the worksheet scrolls through the movie's and finds the movie's which meet my criteria alphabetically (first word). Instead I would like for the macro to open a new box or even sheet which contains all the movie's which contain the word/phrase I have entered in my search field.

So I was wondering if anyone has any ideas about how I should change my code or how I should go about doing it. More information can be provided as needed. (:

thanks,

[si_the_geek]

Thread moved from the 'VB.Net' forum to the 'Office Development/VBA' forum.

[westconn1]

how I should change my code

as we do not know what your code is we can not suggest changes,

or how I should go about doing it

try instr function

[Andreew]

Hi the code i have been using is :
Private Sub CommandButton1_Click()
'button 1 = "FIND"
Dim Message, Default, MyValue
Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Dim Row, ColA, ValueRow
Dim n, nn As Variant
Dim wrksht As Worksheet
Dim SR, r, ws As Object
'Dim objListObj As ListObject 'find reference for this
Dim MaxMovie As Variant
Dim endrows

Set SR = Range("MyRange") 'MyRange is a named range

MaxMovie = 1850
Row = 9
ColA = "A9"

Message = "Enter a Movie Title Name" ' Set prompt.
Title = "Find Movie Title - Dialog Box" ' Set title.
Default = "No Cash, No Movie, eh" ' Set default.
'Display dialog box at position 100, 100.
MyValue = InputBox(Message, Title, Default, 100, 100)
MyValue = UCase(MyValue)

Worksheets("Edit MASTER Only").Activate

ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("Edit MASTER Only").Select
Range("A9").Select
Range("MyRange").Sort Key1:=Range("A9"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

For nn = 9 To SR.Rows.Count
If SR.Cells(nn, 1) = "" Then 'r.Cells(n + 1, 1) Then
'MsgBox "blank is at " & SR.Cells(nn, 1).Address
endrows = SR.Cells(nn, 1).Address
GoTo GOTEND
Else
'MsgBox "ZZZZZ is not found " & SR.Cells(nn - 1, 1).Address
End If

Next nn

GOTEND:

nn = nn + 7
For Row = 9 To nn
'start at row 9 in col A
ColA = "A" & Row
Worksheets("Edit MASTER Only").Select
Worksheets("Edit MASTER Only").Range(ColA).Select
ValueRow = Worksheets("Edit MASTER Only").Range(ColA).Value
If MyValue <= ValueRow Then
GoTo FOUND
End If

If Row = nn + 8 Then
GoTo FOUND
End If
'continue
Next Row

FOUND:

Unload Me
End Sub


Do you know how i could change this so that the macro will open a new box or even sheet which contains all the movie's which contain the word/phrase I have entered in my search field?

thanks

[vbfbryce]

I have a workbook with two worksheets in it. The first is called "List" and the second is "Output." My list of movies is in column A, with the heading "Name" in cell A1. The Output sheet is blank before I run this macro:

Sub test()
    Dim rowCount As Integer
    Dim myLookup As String
    
    rowCount = 1
    
    myLookup = InputBox("Enter partial search string", "Lookup")
        
    With Worksheets("List").Range("a2:a1851")
        Set c = .Find(myLookup, LookIn:=xlValues)
        If Not c Is Nothing Then
            firstaddress = c.Address
            Do
                Worksheets("Output").Cells(rowCount, 1).Value = c.Value
                rowCount = rowCount + 1
                Set c = .FindNext(c)
            Loop While Not c Is Nothing And c.Address <> firstaddress
        End If
    End With
End Sub

Any movie with the word(s) I input into the inputbox are then listed on the output sheet in the first column. Does this get you what you need?

[Andreew]

Hi vbfbryce yes that worked great. thanks. sorry to be a pain but the only thing is other than the information i have in coulmn A which holds the movies i have Information in Coulmn B, C and D which holds information such as Date Added, Location etc. Is there anyway i can include these coulmns in the search aswell, for example, say i search and the movie is located at A9, the search also shows information from B9, C9 and D9. Thanks again.