I'm trying to use data I'm retrieving from a web query. Its film data
from IMDb.com. Sometimes the director is on line 4, sometimes line 5,
etc.

How do I automate which data to import into my movie worksheet?

I'm guessing that I have to use a loop to find the cell with "directed
by", and then somehow choose the next two cells below it and transfer
them to the worksheet.

Below is how I've been doing it until now. But I have to step in and
delete/insert rows to conform to the macro.
Thanks in advance for any advice. I also have to change the sheet
name to "sheet2" every time that the web query adds a new worksheet.

Sheets("MDB").Select
r = Application.WorksheetFunction.CountA(Range("A:A")) + 1
Cells(r, 1) = Worksheets("Sheet2").Range("A2")
Cells(r, 2) = Worksheets("Sheet2").Range("A2")
Cells(r, 10) = Worksheets("Sheet2").Range("A5")
Cells(r, 11) = Worksheets("Sheet2").Range("A6")
Cells(r, 13) = Worksheets("Sheet2").Range("B13")
Cells(r, 14) = Worksheets("Sheet2").Range("B14")
Cells(r, 15) = Worksheets("Sheet2").Range("B15")
Cells(r, 16) = Worksheets("Sheet2").Range("B16")
Cells(r, 17) = Worksheets("Sheet2").Range("B17")
Cells(r, 18) = Worksheets("Sheet2").Range("B18")
Cells(r, 19) = Worksheets("Sheet2").Range("B19")
Cells(r, 20) = Worksheets("Sheet2").Range("B20")
Cells(r, 21) = Worksheets("Sheet2").Range("B21")
Cells(r, 22) = Worksheets("Sheet2").Range("B22")
Cells(r, 23) = Worksheets("Sheet2").Range("B23")
Cells(r, 24) = Worksheets("Sheet2").Range("B24")
Cells(r, 25) = Worksheets("Sheet2").Range("B25")
Cells(r, 26) = Worksheets("Sheet2").Range("B26")
Cells(r, 27) = Worksheets("Sheet2").Range("B27")
Cells(r, 29) = Worksheets("Sheet2").Range("A9")
Cells(r, 30) = Worksheets("Sheet2").Range("A10")
Application.DisplayAlerts = False
Worksheets("Sheet2").Delete
Application.DisplayAlerts = True
End Sub

Try this:
Dim myRange as range
Dim r as range



Set myRange = Nothing
on error resume next
Set myRange = union(Cells(r,1),cells(r,2),cells(r,10),cells(r,11 ),cells(r,
13) ,
you can finish the rest
on error goto 0

if not myrange is nothing then
for each rng in myRange
if rng.value like "*directed by*" then
debug.print "Directed by is found in row: "&rng.row & " and
column: "&rng.column
end if
next rng
end if
" wrote:

I'm trying to use data I'm retrieving from a web query. Its film data
from IMDb.com. Sometimes the director is on line 4, sometimes line 5,
etc.

How do I automate which data to import into my movie worksheet?

I'm guessing that I have to use a loop to find the cell with "directed
by", and then somehow choose the next two cells below it and transfer
them to the worksheet.

Below is how I've been doing it until now. But I have to step in and
delete/insert rows to conform to the macro.
Thanks in advance for any advice. I also have to change the sheet
name to "sheet2" every time that the web query adds a new worksheet.

Sheets("MDB").Select
r = Application.WorksheetFunction.CountA(Range("A:A")) + 1
Cells(r, 1) = Worksheets("Sheet2").Range("A2")
Cells(r, 2) = Worksheets("Sheet2").Range("A2")
Cells(r, 10) = Worksheets("Sheet2").Range("A5")
Cells(r, 11) = Worksheets("Sheet2").Range("A6")
Cells(r, 13) = Worksheets("Sheet2").Range("B13")
Cells(r, 14) = Worksheets("Sheet2").Range("B14")
Cells(r, 15) = Worksheets("Sheet2").Range("B15")
Cells(r, 16) = Worksheets("Sheet2").Range("B16")
Cells(r, 17) = Worksheets("Sheet2").Range("B17")
Cells(r, 18) = Worksheets("Sheet2").Range("B18")
Cells(r, 19) = Worksheets("Sheet2").Range("B19")
Cells(r, 20) = Worksheets("Sheet2").Range("B20")
Cells(r, 21) = Worksheets("Sheet2").Range("B21")
Cells(r, 22) = Worksheets("Sheet2").Range("B22")
Cells(r, 23) = Worksheets("Sheet2").Range("B23")
Cells(r, 24) = Worksheets("Sheet2").Range("B24")
Cells(r, 25) = Worksheets("Sheet2").Range("B25")
Cells(r, 26) = Worksheets("Sheet2").Range("B26")
Cells(r, 27) = Worksheets("Sheet2").Range("B27")
Cells(r, 29) = Worksheets("Sheet2").Range("A9")
Cells(r, 30) = Worksheets("Sheet2").Range("A10")
Application.DisplayAlerts = False
Worksheets("Sheet2").Delete
Application.DisplayAlerts = True
End Sub

Let me do this again. I'm now assuming that directed by is either in A4 or
A5 of sheet2

set myRange = Worksheets("Sheet2").range("A4:A5")
for each r in myrange

next r


I"m not going to assume that the web query adds a new sheet at the end.
Dim newSheet as worksheet

Set newSheet = Worksheets(activeworkbook.worksheets.count)

When you reference the range of this sheet do it this way

newSheet.Range("A5")

for cells(r,13) - cells(r,27)

for i = 13 to 27
Cells(r,i) = newSheet.cells(i,2)
next i

for i = 29 to 30
Cells(r,i) = newSheet.cells(i,1)
next i

Without knowing what you have to "step in" to change, I can't help anymore.


" wrote:

I'm trying to use data I'm retrieving from a web query. Its film data
from IMDb.com. Sometimes the director is on line 4, sometimes line 5,
etc.

How do I automate which data to import into my movie worksheet?

I'm guessing that I have to use a loop to find the cell with "directed
by", and then somehow choose the next two cells below it and transfer
them to the worksheet.

Below is how I've been doing it until now. But I have to step in and
delete/insert rows to conform to the macro.
Thanks in advance for any advice. I also have to change the sheet
name to "sheet2" every time that the web query adds a new worksheet.

Sheets("MDB").Select
r = Application.WorksheetFunction.CountA(Range("A:A")) + 1
Cells(r, 1) = Worksheets("Sheet2").Range("A2")
Cells(r, 2) = Worksheets("Sheet2").Range("A2")
Cells(r, 10) = Worksheets("Sheet2").Range("A5")
Cells(r, 11) = Worksheets("Sheet2").Range("A6")
Cells(r, 13) = Worksheets("Sheet2").Range("B13")
Cells(r, 14) = Worksheets("Sheet2").Range("B14")
Cells(r, 15) = Worksheets("Sheet2").Range("B15")
Cells(r, 16) = Worksheets("Sheet2").Range("B16")
Cells(r, 17) = Worksheets("Sheet2").Range("B17")
Cells(r, 18) = Worksheets("Sheet2").Range("B18")
Cells(r, 19) = Worksheets("Sheet2").Range("B19")
Cells(r, 20) = Worksheets("Sheet2").Range("B20")
Cells(r, 21) = Worksheets("Sheet2").Range("B21")
Cells(r, 22) = Worksheets("Sheet2").Range("B22")
Cells(r, 23) = Worksheets("Sheet2").Range("B23")
Cells(r, 24) = Worksheets("Sheet2").Range("B24")
Cells(r, 25) = Worksheets("Sheet2").Range("B25")
Cells(r, 26) = Worksheets("Sheet2").Range("B26")
Cells(r, 27) = Worksheets("Sheet2").Range("B27")
Cells(r, 29) = Worksheets("Sheet2").Range("A9")
Cells(r, 30) = Worksheets("Sheet2").Range("A10")
Application.DisplayAlerts = False
Worksheets("Sheet2").Delete
Application.DisplayAlerts = True
End Sub

Thank-you very much for your help. I'll try and make this work.

I'm sorry i wasn't very clear. I'm trying to save myself from having
to enter too much info by hand for movies in my collection. I'm
saving info like "directed by", "writers", and "15 actors" for each
movie. I've figured out how to import the info I need from IMDb.com
with the web query. The problem is once I get the worksheet with all
of the info, the worksheets aren't uniform. Sometimes the director's
name will be in A80, and sometimes a few other cells. It is always
preceded by a cell above "Directed by". The writers are in cells
below that, but their placement is even more random as are the
actors. So when I said "step in" I meant that I have two macros, the
first getting the info imported from the web into a worksheet, the I
"step in" delete or insert rows to make sure the directors are on rows
5&6, the writers on 9&10, and the actors on rows 13-27. Once I've
done that I run the next macro that places the info I want in the main
worksheet. I'm sorry I'm so dense, I've tried figuring out VBA
through trial and error. Most of my "tutoring" come from recording
macros and trying to make sense of the result and then try to
customize it. I figured out how to get around making a new sheet each
time by creating a named sheet for the imported data.

The two macros:

Sub IMDb_Consolidated()
Sheets("Import").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://imdb.com/title/[""titlenumber"",""Enter title
number.""]/fullcredits#cast", Destination:=Range( _
"A1"))
.Name = "fullcredits#cast"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = False
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
Range("A1").Select
End Sub
Sub left2right()
Sheets("MDB").Select
r = Application.WorksheetFunction.CountA(Range("A:A")) + 1
Cells(r, 1) = Worksheets("Import").Range("A2")
Cells(r, 2) = Worksheets("Import").Range("A2")
Cells(r, 10) = Worksheets("Import").Range("A5")
Cells(r, 11) = Worksheets("Import").Range("A6")
Cells(r, 13) = Worksheets("Import").Range("B13")
Cells(r, 14) = Worksheets("Import").Range("B14")
Cells(r, 15) = Worksheets("Import").Range("B15")
Cells(r, 16) = Worksheets("Import").Range("B16")
Cells(r, 17) = Worksheets("Import").Range("B17")
Cells(r, 18) = Worksheets("Import").Range("B18")
Cells(r, 19) = Worksheets("Import").Range("B19")
Cells(r, 20) = Worksheets("Import").Range("B20")
Cells(r, 21) = Worksheets("Import").Range("B21")
Cells(r, 22) = Worksheets("Import").Range("B22")
Cells(r, 23) = Worksheets("Import").Range("B23")
Cells(r, 24) = Worksheets("Import").Range("B24")
Cells(r, 25) = Worksheets("Import").Range("B25")
Cells(r, 26) = Worksheets("Import").Range("B26")
Cells(r, 27) = Worksheets("Import").Range("B27")
Cells(r, 29) = Worksheets("Import").Range("A9")
Cells(r, 30) = Worksheets("Import").Range("A10")
Application.DisplayAlerts = False
Worksheets("Import").Delete
Application.DisplayAlerts = True
End Sub

We're going to to this step by step.

First, I'm going to assume that you data is in Column A.

THis section will determine the last entry in column A

lastrow = activesheet.cells(rows.count,1).end(xlup)
Dim myRange as Range, rng as Range

set myRange = cells(1,1).resize(lastrow,1)

for each rng in myRange
if lcase(rng.value) like "*directed by*"
'The value for directed by is in the column to the right of rng
'I'm not sure what you want to do, but this is how you get it
debug.print rng.row, rng.column, rng.value, rng.offset(1,0).value
eise if lcase(rng.value) like "*writers*"
'I'm guessing there is one or more writers below this
debug.print rng.row, rng.column, rng.value, rng.offset(1,0).value
end if
next r

Is this what you're looking for?

" wrote:

Thank-you very much for your help. I'll try and make this work.

I'm sorry i wasn't very clear. I'm trying to save myself from having
to enter too much info by hand for movies in my collection. I'm
saving info like "directed by", "writers", and "15 actors" for each
movie. I've figured out how to import the info I need from IMDb.com
with the web query. The problem is once I get the worksheet with all
of the info, the worksheets aren't uniform. Sometimes the director's
name will be in A80, and sometimes a few other cells. It is always
preceded by a cell above "Directed by". The writers are in cells
below that, but their placement is even more random as are the
actors. So when I said "step in" I meant that I have two macros, the
first getting the info imported from the web into a worksheet, the I
"step in" delete or insert rows to make sure the directors are on rows
5&6, the writers on 9&10, and the actors on rows 13-27. Once I've
done that I run the next macro that places the info I want in the main
worksheet. I'm sorry I'm so dense, I've tried figuring out VBA
through trial and error. Most of my "tutoring" come from recording
macros and trying to make sense of the result and then try to
customize it. I figured out how to get around making a new sheet each
time by creating a named sheet for the imported data.

The two macros:

Sub IMDb_Consolidated()
Sheets("Import").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://imdb.com/title/[""titlenumber"",""Enter title
number.""]/fullcredits#cast", Destination:=Range( _
"A1"))
.Name = "fullcredits#cast"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = False
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
Range("A1").Select
End Sub
Sub left2right()
Sheets("MDB").Select
r = Application.WorksheetFunction.CountA(Range("A:A")) + 1
Cells(r, 1) = Worksheets("Import").Range("A2")
Cells(r, 2) = Worksheets("Import").Range("A2")
Cells(r, 10) = Worksheets("Import").Range("A5")
Cells(r, 11) = Worksheets("Import").Range("A6")
Cells(r, 13) = Worksheets("Import").Range("B13")
Cells(r, 14) = Worksheets("Import").Range("B14")
Cells(r, 15) = Worksheets("Import").Range("B15")
Cells(r, 16) = Worksheets("Import").Range("B16")
Cells(r, 17) = Worksheets("Import").Range("B17")
Cells(r, 18) = Worksheets("Import").Range("B18")
Cells(r, 19) = Worksheets("Import").Range("B19")
Cells(r, 20) = Worksheets("Import").Range("B20")
Cells(r, 21) = Worksheets("Import").Range("B21")
Cells(r, 22) = Worksheets("Import").Range("B22")
Cells(r, 23) = Worksheets("Import").Range("B23")
Cells(r, 24) = Worksheets("Import").Range("B24")
Cells(r, 25) = Worksheets("Import").Range("B25")
Cells(r, 26) = Worksheets("Import").Range("B26")
Cells(r, 27) = Worksheets("Import").Range("B27")
Cells(r, 29) = Worksheets("Import").Range("A9")
Cells(r, 30) = Worksheets("Import").Range("A10")
Application.DisplayAlerts = False
Worksheets("Import").Delete
Application.DisplayAlerts = True
End Sub