Hey all, I have a sheet set up as follows:
A2: Actor Name
B2: Movie Name
F2: Scene 3
A3: Actress Name
B3: Movie Name
F3: Scene 6
ect...
I first search for the movie name and then check cell to the left to see if it has the desired name. The problem arises when the first search result doesn't have the desired name left of it. I would like to keep searching the sheet for the movie and looking at the name until it is a match.
Search:
Set FndRng = Sheet8.Columns(2).Find(strMovie, SearchDirection:=xlNext, LookIn:=xlValues, LookAt:=xlWhole)
'Movie not found
If FndRng Is Nothing Then
'Insert row with data
ActiveCell.Offset(1).EntireRow.Insert
ActiveCell.Offset(1, 0).Select 'Selects the same cell, 1 Row DOWN
Sheet8.Cells(ActiveCell.row, 1).Value = Sheet1.Cells(row, 1).Value
Sheet8.Cells(ActiveCell.row, 2).Value = strMovie
Sheet8.Cells(ActiveCell.row, 6).Value = strScene
GoTo missing
End If
'Does found movie have same star?
FndRng.Activate
If Sheet1.Cells(row, 1).Value = FndRng.Offset(0, -1).Value Then
'Determine if movie has multiple scenes with same star.
'If oldMovie = strMovie And oldScene <> strScene Then
If Not Sheet8.Cells(ActiveCell.row, 6).Value = strScene Then
If Not Sheet8.Cells(ActiveCell.row, 6).Value = "" Then
Sheet8.Cells(ActiveCell.row, 6).Value = Sheet8.Cells(ActiveCell.row, 6).Value & ", " & strScene
Else
Sheet8.Cells(ActiveCell.row, 6).Value = strScene
End If
End If
Else
GoTo Search ' <-----------causes to freeze
End If
Thanks all for your help. This will conclude my macro once fixed!
Bookmarks