Y
yankeefan62
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
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