Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2005
    Posts
    3

    Unhappy Unanswered: Extracting data within Excel

    I have a 67 line record with heading data in the 2nd and 3rd rows, then I need to extract columns 4 & 5 from the next 65 lines skiping over 4 that contain headers. I have written the routine, and the code executes correctly for the first record, and then stops. I am using the following code:
    Sub excelextract()
    Application.ScreenUpdating = False
    Sheets("Assessments").Select
    Range("a1").Select
    ActiveCell.SpecialCells(xlLastCell).Select
    ans = ActiveCell.Row: nitems = ans / 67


    clssheet1

    For i = 2 To ans Step 67
    Sheets("Assessments").Select
    'Collecting Offering Information

    oname = Cells(i, 1).Value
    otname = Cells(i, 2).Value
    ccode = Cells(i, 3).Value
    ddname = Cells(i, 4).Value
    urlname = Cells(i, 5).Value
    aname = Cells(i + 1, 2).Value
    aid = Cells(i + 1, 3).Value
    pname = Cells(i + 1, 4).Value
    extra = Cells(i + 1, 5).Value

    125 variables later, I have a Next, but it gets ignored. When I debug, the value of ans =2210 and the correct values are in all variables, but it does not increment.
    Any suggestions?

  2. #2
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Quote Originally Posted by Glenn799
    I have a 67 line record with heading data in the 2nd and 3rd rows, then I need to extract columns 4 & 5 from the next 65 lines skiping over 4 that contain headers. I have written the routine, and the code executes correctly for the first record, and then stops. I am using the following code:
    Sub excelextract()
    Application.ScreenUpdating = False
    Sheets("Assessments").Select
    Range("a1").Select
    ActiveCell.SpecialCells(xlLastCell).Select
    ans = ActiveCell.Row: nitems = ans / 67


    clssheet1

    For i = 2 To ans Step 67
    Sheets("Assessments").Select
    'Collecting Offering Information

    oname = Cells(i, 1).Value
    otname = Cells(i, 2).Value
    ccode = Cells(i, 3).Value
    ddname = Cells(i, 4).Value
    urlname = Cells(i, 5).Value
    aname = Cells(i + 1, 2).Value
    aid = Cells(i + 1, 3).Value
    pname = Cells(i + 1, 4).Value
    extra = Cells(i + 1, 5).Value

    125 variables later, I have a Next, but it gets ignored. When I debug, the value of ans =2210 and the correct values are in all variables, but it does not increment.
    Any suggestions?
    Hmm no sign of a break statement there,
    can you post a sample workbook with ananimised data and we will see if there is anything you can do to help.

    Just a quick suggestion for your coding though.
    I never use SpecialCells(xlLastCell). i always prefer to check for the lastcell by finding the last cell that has anything in it as xlLastCell stops providing the last cell if you have deleted any rows

    lets think of problems you might be facing do you have another loop somewhere in your system that could be causing problems, or is it purley variables.

    what happens when you step through. does the code even reach the next statement.

    do you have any error handling that could be skipping problems as i cant see anything that would cause an upset.

    more incoherent rambling later (by me)

    Dave

    if you put your code in [ code] wrappers itll be easier to read

  3. #3
    Join Date
    Nov 2005
    Posts
    3

    Talking Problem Resolved

    David, I appreciate your comments, and yes the code that I write is very, very sloppy. I am a newbee, but I guess that that does not matter.

    In the worksheet that I was writing to (Sheet1), I hit [ctlr] & [end] and ended up in row 2562 and column bn. I cleaned up the sheet, highlighted the area from a2:bn2562 hit [delete] did an [Edit][clear][all] saved the sheet and reopened it. I was using the same variable to read location and write location (i). I created variable (rn) and started it at 2 and incremented it at the end of each write, and the routine worked correctly.

    Thanks again
    Glenn

Posting Permissions

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