Consulting

Results 1 to 3 of 3

Thread: Problem Between Test and Physical Run

  1. #1

    Problem Between Test and Physical Run

    Hey guys, I have some pretty sophomoric code and I'm looking for help as to why I can run it with no problem on a test page with select data, but not a full range.

    The test page has exact data copied from the full page for 100 rows, and runs without problem. The full page is upward of 43,000 lines and a couple seconds after I run the code, VBA gives me an error and stops.


    Here's the code:[vba]Sub DeleteErrors()
    Dim i As Long
    Dim iLastRow As Long
    Dim x As Long
    Dim xLastRow As Long

    With ActiveSheet

    Application.ScreenUpdating = False

    Columns("B:B").SpecialCells(xlCellTypeFormulas, 16).EntireRow.Delete

    iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
    For i = iLastRow To 2 Step -1
    .Rows(i + 1).Insert
    .Cells(i + 1, 1).Value = "item_id"
    Next i

    xLastRow = Cells(Rows.Count, "A").End(xlUp).Row
    For x = 1 To xLastRow
    If Cells(x, "A").Text = "item_id" Then
    Cells(x, "A").EntireRow.Hidden = True
    End If
    Next x

    Application.ScreenUpdating = True

    End With
    End Sub
    [/vba]
    The code itself is probably pretty shoddy (I have no idea what I'm doing to be quite honest ), but it works fine on the test and not on the actual page.

    And I know that I'm going about this the completely wrong way but this is the code I've been able to figure out so far. What I'm trying to do is match a list from Sheet1 ColumnA to Sheet2 ColumnA, both columns of which have some cells that don't have matching values. I need to narrow down the list to only the matching items, and then pull the value from Sheet2 ColumnB into the corresponding cell in Sheet1 ColumnB for the matching item in ColumnA.

    I don't know if this is making any sense, but my lack of experience in programming is making this pretty difficult for me. Any help I could get is greatly appreciated!

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    [vba]

    Sub DeleteErrors()
    Dim i As Long
    Dim iLastRow As Long
    Dim x As Long
    Dim xLastRow As Long

    With ActiveSheet

    Application.ScreenUpdating = False

    .Columns("B:B").SpecialCells(xlCellTypeFormulas, 16).EntireRow.Delete

    iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    For i = iLastRow To 2 Step -1
    .Rows(i + 1).Insert
    .Cells(i + 1, 1).Value = "item_id"
    Next i

    xLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    For x = 1 To xLastRow
    .Rows(x).Hidden = .Cells(x, "A").Text = "item_id"
    Next x

    Application.ScreenUpdating = True

    End With
    End Sub
    [/vba]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    VBAX Master Norie's Avatar
    Joined
    Jan 2005
    Location
    Stirling, Scotland
    Posts
    1,831
    Location
    Well the main problem I can see is that you haven't actually qualified all your references.

    You've done it here.
    [vba]
    .Rows(i + 1).Insert
    [/vba]
    But not here.
    [vba]
    Columns("B:B").SpecialCells(xlCellTypeFormulas, 16).EntireRow.Delete[/vba]
    That little dot (qualifier) highlighted can be all important.

    Without it VBA will assume you are referring to the active sheet.

Posting Permissions

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