Results 1 to 9 of 9

While Using Value from Range D3:D in String, if (D, x) = "" doesnt skip to next row??

Threaded View

  1. #1
    Registered User
    Join Date
    10-14-2011
    Location
    chesapeake, va
    MS-Off Ver
    Excel 2007
    Posts
    59

    While Using Value from Range D3:D in String, if (D, x) = "" doesnt skip to next row??

    In my Workbook Code, Sub Get_MovieData(), its using A:A to discover last row, Using the Number From Each in in column D, it then Pulls Data from a Web XML doc and filles in the rest of the Row with Data. it should then goto the Next row and use the Data from column D, however if column D is Empty, it scips to the Next Row, Makes the querry, but doesn't skip to the next row to add the Data, leaving the Data incorrect

    heres The Code:
    Sub Get_MovieData()
    
    Dim rngCode As Range
    Dim rng As Range
    Dim xml As Object
    Dim nodes As Object
    Dim strURL As String
    Dim arrCat As Variant
    Dim I As Long
    Dim j As Long
    Dim LastRow As Long
    
        LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
        Set rngCode = ActiveSheet.Range("D3")
        Set xml = CreateObject("MSXML2.DOMDocument")
        With xml
    
            Set rng = ActiveSheet.Range("A3")
            For j = 3 To LastRow
                Set rngCode = ActiveSheet.Range("D" & j)
                If rngCode <> "" Then
    
                    strURL = "http://api.themoviedb.org/2.1/Movie.getInfo/en/xml/644c8c7523000ff3de7cf4b0af9d4f8c/" & rngCode.Value
                    .Load strURL
                    Do: DoEvents: Loop Until .readyState = 4
                    Set nodes = .getElementsByTagName("movie")
    
                    For I = 0 To nodes.Length - 1
                        rng.Value = nodes(I).ChildNodes(5).nodeTypedValue                           'Title
                        rng.Offset(0, 4).Value = nodes(I).ChildNodes(15).nodeTypedValue             'Rating
                        rng.Offset(0, 5).Value = Left(nodes(I).ChildNodes(16).nodeTypedValue, 4)    'Release Date
                        rng.Offset(0, 6).Value = nodes(I).ChildNodes(17).nodeTypedValue             'RunTime
                        rng.Offset(0, 7).Value = nodes(I).ChildNodes(11).nodeTypedValue             'Plot
                        'rng.Offset(0, 5).Value = nodes(I).ChildNodes(9).nodetypedvalue              'IMDB ID
                        'rng.Offset(0, 6).Value = nodes(I).ChildNodes(8).nodetypedvalue              'TMDB ID
                        rng.Offset(0, 8).Value = nodes(I).ChildNodes(14).nodeTypedValue             'Tag Line
                        rng.Offset(0, 9).Value = nodes(I).ChildNodes(21).nodeTypedValue             'Trailer
                        rng.Offset(0, 10).Value = nodes(I).ChildNodes(20).nodeTypedValue            'Official WebSite"
                    Next I
    
                    Set nodes = .getElementsByTagName("category")
                    If nodes.Length > 0 Then
                        ReDim arrCat(0 To nodes.Length - 1)
                        For I = 0 To nodes.Length - 1
                            arrCat(I) = nodes(I).Attributes(1).Value
                        Next I
    
                        If UBound(arrCat) > 0 Then
                            rng.Offset(0, 11).Value = Join(arrCat, ",")                              'Genre
                        End If
                    End If
    
                    Set nodes = .getElementsByTagName("image")
                        For Each nd In nodes
                            For Each Attr In nd.Attributes
                            Debug.Print Attr.Name & " - " & Attr.Value
                            Next Attr
                        Next nd
                    rng.Offset(0, 12).Value = nodes(nd + 1).Attributes(1).Value
    
                    Set rng = rng.Offset(1)
                End If
            Next j
        End With
        
    With ActiveSheet
        .Range("C3, D3, F3:G3").EntireColumn.AutoFit
        .Range("A:A").ColumnWidth = 40
        .Range("L:L").ColumnWidth = 30
        .Range("C:C").ColumnWidth = 10
        .Range("E:E").ColumnWidth = 7
        .Range("F:F").ColumnWidth = 5
    End With
    
    End Sub
    WorkBookMovies.xlsm... Working With Sheets("Movies")
    Last edited by Spyderz; 10-28-2012 at 02:00 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1