Results 1 to 10 of 10
  1. #1
    Lounger
    Join Date
    Apr 2002
    Location
    Los Angeles, California, USA
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Offset Variable (97sr2)

    I have a series of one sheet XLS files that are inventory detail sheets. The data in each sheet needs to be copied to a new sheet for consolidation. The data fields exist in every sheet in coherent groups but due to 'tweaking", some sheets have extra rows. This plays havoc with a straight Range-Select-Copy-NewSheet Paste process. I can grab all of the data with a Find of the data labels and do an Offset(0,1) to Select the data fields but would like to speed the process. There are 1300 files, each with 43 data fields that need extracting. The data occurs in groups. Once I Find a particular DataLabel, I can use Offset to get to 3-12 data fields.
    A data group looks like this:

    Model# MDR103C Serial# 234-4565 Mfr. Date 9/03/99
    Desc. Mens Ring Size 11

    How do I turn an address that is the result of a Find process into an Offset variable reference point?

    It could look something like this:

    Find "Model#".... Activate ' GoTo Model# DataLabel
    xy1 = Selection 'Capture this address as a reference point
    Offet(0,1).Select 'GoTo to Model Number data field
    Selection.Copy
    .code
    .code
    .code
    Range(xy1).Select 'GoTo original reference point
    Offset(0,3).Select 'GoTo Serial Number data field based on reference point
    Selection.copy
    .code
    .code
    .code

    I am so close but have run out of iterations....

    Any suggestions?

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts

    Re: Offset Variable (97sr2)

    Re:
    Find "Model#".... Activate ' GoTo Model# DataLabel
    xy1 = Selection 'Capture this address as a reference point
    -->try activeCell.Offet(0,1).Select 'GoTo to Model Number data field
    Selection.Copy

    zeddy

  3. #3
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Offset Variable (97sr2)

    The best way to speed up the process is to not do any selecting of cells. I was a little unclear about the details, but I think that the macro below is what you want. I've attached my test workbook so that you can see my assumptions. HTH --Sam
    <pre>Option Explicit

    Sub Consolidate()
    Dim wsSummary As Worksheet
    Dim c As Range ' Single cell
    Dim strFirst As String
    Dim i As Long
    Set wsSummary = Worksheets("Summary")
    i = 1
    With ActiveSheet.UsedRange
    Set c = .Find(What:="Model#", LookIn:=xlValues)
    If Not c Is Nothing Then
    strFirst = c.Address
    Do
    wsSummary.Cells(i, 1) = c.Offset(0, 1)
    wsSummary.Cells(i, 2) = c.Offset(0, 3)
    i = i + 1
    Set c = .FindNextŠ
    Loop While Not c Is Nothing And c.Address <> strFirst
    End If
    End With
    End Sub</pre>

    Attached Files Attached Files
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  4. #4
    Lounger
    Join Date
    Apr 2002
    Location
    Los Angeles, California, USA
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Offset Variable (97sr2)

    Active cell is changed after I grab the first data field because I copy and paste the data field into a new row. I need some way of getting back to the "marching ants" cell to Offset to the next data field.

    ActiveCell.Offset(0, 1).Select Offsets from pasted cell, not cell with "marching ants"

    There are 1,290 Sheets, each with five general groups of information that loosely follow this format.
    .row
    .row
    Label1 Data1 Label2 Data2 Label3 Data3
    Label4 Data4 Label5 Data5
    .row
    .row 'Inconsistant number of rows between groups
    .row
    Next Data Group
    .row
    etc... for five groups

    The consolidation result is a new row that looks like this:

    Data1 Data2 Data3 Data4 Data5....Data34

    which gets copied to a consolidating worksheet.

    Some other way to do this?

  5. #5
    Lounger
    Join Date
    Apr 2002
    Location
    Los Angeles, California, USA
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Offset Variable (97sr2)

    Thanks for the beautiful piece of code but my description of the data wasn't very good.

    Sample sheet attached.

    After looking at your code <img src=/S/compute.gif border=0 alt=compute width=40 height=20> the answer jumped out <img src=/S/joy.gif border=0 alt=joy width=23 height=23>

    Dim xy1 as Range
    Find Label1
    Set xy1 = Label1
    xy1.Offset(0,1) Do work on Data1
    xy1.Offset(0,3) Do work on Data2
    etc.


    Thanks for the inspiration.
    Sometimes the simplest solutions are the hardest to see.
    Attached Files Attached Files
    • File Type: zip a.zip (91.6 KB, 1 views)

  6. #6
    Lounger
    Join Date
    Apr 2002
    Location
    Los Angeles, California, USA
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Offset Variable (97sr2)

    Here is a fragment of the final code.

    <img src=/S/joy.gif border=0 alt=joy width=23 height=23> Thanks to all. <img src=/S/joy.gif border=0 alt=joy width=23 height=23>

    'GoTo Diamonds Section
    Range("A7").Select <font color=blue>

  7. #7
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Offset Variable (97sr2)

    All of that selecting of cell is killing your processing time and is unnecessary. Here's a rewrite of the first half of your code:
    <pre> 'Go To Diamonds Section (A7 is close to the target cell)
    Set c = Cells.Find(What:="dia", After:=Range("A7"), LookIn:=xlValues)
    'Get Diamond Source
    Cells(100, i).Value = c.Offset(0, 3) ' build a consolidated data row starting at B100
    i = i + 1 'Steps to next column in Data Row
    'Get Metal Combination Line1
    Cells(100, i).Value = c.Offset(0, 6)
    i = i + 1</pre>

    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  8. #8
    Lounger
    Join Date
    Apr 2002
    Location
    Los Angeles, California, USA
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Offset Variable (97sr2)

    Thanks for the tip. <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

    The well is so deep and my dipper so small. <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

    Do you have a favorite resource for Excel VBA programming? <img src=/S/read.gif border=0 alt=read width=19 height=33>

  9. #9
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Offset Variable (97sr2)

    <img src=/w3timages/blackline.gif width=33% height=2>
    > a favorite resource for Excel VBA programming
    <img src=/w3timages/blackline.gif width=33% height=2>
    Steven Roman Writing Excel Macros, cheap, easy to read and thin! --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  10. #10
    Lounger
    Join Date
    Apr 2002
    Location
    Los Angeles, California, USA
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Offset Variable (97sr2)

    <hr>Steven Roman Writing Excel Macros, cheap, easy to read and thin! --Sam<hr>

    Thanks,

    Amazon actually has a used copy <img src=/S/yikes.gif border=0 alt=yikes width=15 height=15>

Posting Permissions

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