Offset Variable (97sr2)

Topic

New Reply

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?

Reply | Quote

Replies

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

Reply | Quote

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?

Reply | Quote

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

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

Reply | Quote

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 the answer jumped out

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.

Reply | Quote

Here is a fragment of the final code.

Thanks to all.

‘GoTo Diamonds Section
Range(“A7”).Select

Reply | Quote

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:

        '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

Reply | Quote

Thanks for the tip.

The well is so deep and my dipper so small.

Do you have a favorite resource for Excel VBA programming?

Reply | Quote

> a favorite resource for Excel VBA programming

Steven Roman Writing Excel Macros, cheap, easy to read and thin! –Sam

Reply | Quote

[indent]

---

Steven Roman Writing Excel Macros, cheap, easy to read and thin! –Sam

---

[/indent]

Thanks,

Amazon actually has a used copy

Reply | Quote