Macro question

[Guest]

Is there a way to make a macro remember my keystrokes? When I record a macro
it only remembers the cell number of the box that I clicked and not how I got
there. What I would like to do is use ctrl+F to find what I'm looking for and
then use the keyboard (2 keystrokes down and 6 keystrokes to the right for
example) to arrive at a cell. However when I arrive at a cell, the macro
just uses the cell number lets say H20 and doesn't remember how I got there
from using the keyboard. What I'm trying to do is create a macro which
graphs data from several different worksheets. The problem is the data from
the different worksheets isn't the size of the data from which the macro was
created. When I run the macro on different worksheets it doesn't graph the
data from the same starting points or ending points. If I can't use a use a
macro to do this, is there any advice for what I'm trying to accomplish?

 

[Bob Phillips]

In a word, there is no way to get the recorder to do that.

The macro can do what you want though, you just have to modify it to do so.
For instance, to find a value, use Find

Set cell = a_specified_range.Find("value")

and then to offset it use

If Not cell Is Nothing Then

cell.Offset(2,6).Select

etc.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

Thanks Bob. It's good news that I can get the macro to do what I'm looking
for except the only bad news is I've never done anything with visual basic
before.
I recorded the macro, so what code should I change exactly to implement the
offset?

Here's a section of code from the macro.

Cells.Find(What:="1", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Range("M39").Select
ActiveCell.FormulaR1C1 = "=(RC[-11]*R10C2)-R10C2"
Range("M39").Select
Selection.AutoFill Destination:=Range("M39:M201"), Type:=xlFillDefault
Range("M39:M201").Select
Range("A1").Select

Do I just change the Range values to the formula that you listed? Thanks.

 

[Bob Phillips]

No tested this, but this should do it

Dim oCell As Range
Dim iRow As Long

Set oCell = Cells.Find(What:="1", _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
If Not oCell Is Nothing Then
With oCell.Offset(2, 6)
iRow = Cells(Rows.Count, .Column).End(xlUp).Row
.FormulaR1C1 = "=(RC[-11]*R10C2)-R10C2"
.AutoFill Destination:=Cells(iRow.Column), Type:=xlFillDefault
End With
End If


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Thanks Bob. It's good news that I can get the macro to do what I'm looking
for except the only bad news is I've never done anything with visual basic
before.
I recorded the macro, so what code should I change exactly to implement the
offset?

Here's a section of code from the macro.

Cells.Find(What:="1", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Range("M39").Select
ActiveCell.FormulaR1C1 = "=(RC[-11]*R10C2)-R10C2"
Range("M39").Select
Selection.AutoFill Destination:=Range("M39:M201"), Type:=xlFillDefault
Range("M39:M201").Select
Range("A1").Select

Do I just change the Range values to the formula that you listed? Thanks.

In a word, there is no way to get the recorder to do that.

The macro can do what you want though, you just have to modify it to do so.
For instance, to find a value, use Find

Set cell = a_specified_range.Find("value")

and then to offset it use

If Not cell Is Nothing Then

cell.Offset(2,6).Select

etc.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

how I
got for
and macro
was use
a accomplish?

 

[Guest]

It says "invalid qualifier" and points to iRow in the line right before the
End With.

No tested this, but this should do it

Dim oCell As Range
Dim iRow As Long

Set oCell = Cells.Find(What:="1", _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
If Not oCell Is Nothing Then
With oCell.Offset(2, 6)
iRow = Cells(Rows.Count, .Column).End(xlUp).Row
.FormulaR1C1 = "=(RC[-11]*R10C2)-R10C2"
.AutoFill Destination:=Cells(iRow.Column), Type:=xlFillDefault
End With
End If


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Thanks Bob. It's good news that I can get the macro to do what I'm looking
for except the only bad news is I've never done anything with visual basic
before.
I recorded the macro, so what code should I change exactly to implement the
offset?

Here's a section of code from the macro.

Cells.Find(What:="1", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Range("M39").Select
ActiveCell.FormulaR1C1 = "=(RC[-11]*R10C2)-R10C2"
Range("M39").Select
Selection.AutoFill Destination:=Range("M39:M201"), Type:=xlFillDefault
Range("M39:M201").Select
Range("A1").Select

Do I just change the Range values to the formula that you listed? Thanks.

In a word, there is no way to get the recorder to do that.

The macro can do what you want though, you just have to modify it to do so.
For instance, to find a value, use Find

Set cell = a_specified_range.Find("value")

and then to offset it use

If Not cell Is Nothing Then

cell.Offset(2,6).Select

etc.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Is there a way to make a macro remember my keystrokes? When I record a
macro
it only remembers the cell number of the box that I clicked and not how I
got
there. What I would like to do is use ctrl+F to find what I'm looking for
and
then use the keyboard (2 keystrokes down and 6 keystrokes to the right for
example) to arrive at a cell. However when I arrive at a cell, the macro
just uses the cell number lets say H20 and doesn't remember how I got
there
from using the keyboard. What I'm trying to do is create a macro which
graphs data from several different worksheets. The problem is the data
from
the different worksheets isn't the size of the data from which the macro
was
created. When I run the macro on different worksheets it doesn't graph the
data from the same starting points or ending points. If I can't use a use
a
macro to do this, is there any advice for what I'm trying to accomplish?

 

[Bob Phillips]

That will teach me to test it.

Amended code below, but how do you determine where the last row is, M201 in
your recorded macro? I think the code might need amending to manage that

Dim oCell As Range
Dim iRow As Long

Set oCell = Cells.Find(What:="1", _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
If Not oCell Is Nothing Then
With oCell.Offset(2, 6)
iRow = Cells(Rows.Count, oCell.Column).End(xlUp).Row
.FormulaR1C1 = "=(RC[-11]*R10C2)-R10C2"
.AutoFill Destination:=Range(Cells(.Row, .Column), Cells(iRow,
..Column)), _
Type:=xlFillDefault
End With
End If

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

It says "invalid qualifier" and points to iRow in the line right before the
End With.

No tested this, but this should do it

Dim oCell As Range
Dim iRow As Long

Set oCell = Cells.Find(What:="1", _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
If Not oCell Is Nothing Then
With oCell.Offset(2, 6)
iRow = Cells(Rows.Count, .Column).End(xlUp).Row
.FormulaR1C1 = "=(RC[-11]*R10C2)-R10C2"
.AutoFill Destination:=Cells(iRow.Column), Type:=xlFillDefault
End With
End If


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Thanks Bob. It's good news that I can get the macro to do what I'm looking
for except the only bad news is I've never done anything with visual basic
before.
I recorded the macro, so what code should I change exactly to

implement
the

offset?

Here's a section of code from the macro.

Cells.Find(What:="1", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Range("M39").Select
ActiveCell.FormulaR1C1 = "=(RC[-11]*R10C2)-R10C2"
Range("M39").Select
Selection.AutoFill Destination:=Range("M39:M201"), Type:=xlFillDefault
Range("M39:M201").Select
Range("A1").Select

Do I just change the Range values to the formula that you listed? Thanks.

:

In a word, there is no way to get the recorder to do that.

The macro can do what you want though, you just have to modify it to

do
so.

For instance, to find a value, use Find

Set cell = a_specified_range.Find("value")

and then to offset it use

If Not cell Is Nothing Then

cell.Offset(2,6).Select

etc.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Is there a way to make a macro remember my keystrokes? When I record a
macro
it only remembers the cell number of the box that I clicked and

not
how I

got
there. What I would like to do is use ctrl+F to find what I'm

looking
for

and
then use the keyboard (2 keystrokes down and 6 keystrokes to the

right
for

example) to arrive at a cell. However when I arrive at a cell,

the
macro

just uses the cell number lets say H20 and doesn't remember how I got
there
from using the keyboard. What I'm trying to do is create a macro which
graphs data from several different worksheets. The problem is the data
from
the different worksheets isn't the size of the data from which

the
macro

was
created. When I run the macro on different worksheets it doesn't

graph
the

data from the same starting points or ending points. If I can't

use a
use

a
macro to do this, is there any advice for what I'm trying to accomplish?

 

[Guest]

I think it would be a lot harder if I had to determine the last row, so what
I do is just go down 200 or so cells after my starting point. The graph
doesn't include empty cells so I'm okay. I just see a lot of problems with
this because I'm using a lot of offsetting. I'll test that code tomorrow at
work but I have a question though. My macro includes a lot of steps and
offsetting. Should I go by your code to complete the rest of my macro, or are
there are special steps for me to to do to complete the rest of my macro with
an offset?

That will teach me to test it.

Amended code below, but how do you determine where the last row is, M201 in
your recorded macro? I think the code might need amending to manage that

Dim oCell As Range
Dim iRow As Long

Set oCell = Cells.Find(What:="1", _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
If Not oCell Is Nothing Then
With oCell.Offset(2, 6)
iRow = Cells(Rows.Count, oCell.Column).End(xlUp).Row
.FormulaR1C1 = "=(RC[-11]*R10C2)-R10C2"
.AutoFill Destination:=Range(Cells(.Row, .Column), Cells(iRow,
..Column)), _
Type:=xlFillDefault
End With
End If

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

It says "invalid qualifier" and points to iRow in the line right before the
End With.

No tested this, but this should do it

Dim oCell As Range
Dim iRow As Long

Set oCell = Cells.Find(What:="1", _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
If Not oCell Is Nothing Then
With oCell.Offset(2, 6)
iRow = Cells(Rows.Count, .Column).End(xlUp).Row
.FormulaR1C1 = "=(RC[-11]*R10C2)-R10C2"
.AutoFill Destination:=Cells(iRow.Column), Type:=xlFillDefault
End With
End If


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Thanks Bob. It's good news that I can get the macro to do what I'm looking
for except the only bad news is I've never done anything with visual basic
before.
I recorded the macro, so what code should I change exactly to implement
the
offset?

Here's a section of code from the macro.

Cells.Find(What:="1", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Range("M39").Select
ActiveCell.FormulaR1C1 = "=(RC[-11]*R10C2)-R10C2"
Range("M39").Select
Selection.AutoFill Destination:=Range("M39:M201"), Type:=xlFillDefault
Range("M39:M201").Select
Range("A1").Select

Do I just change the Range values to the formula that you listed? Thanks.

:

In a word, there is no way to get the recorder to do that.

The macro can do what you want though, you just have to modify it to do
so.
For instance, to find a value, use Find

Set cell = a_specified_range.Find("value")

and then to offset it use

If Not cell Is Nothing Then

cell.Offset(2,6).Select

etc.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Is there a way to make a macro remember my keystrokes? When I record a
macro
it only remembers the cell number of the box that I clicked and not
how I
got
there. What I would like to do is use ctrl+F to find what I'm looking
for
and
then use the keyboard (2 keystrokes down and 6 keystrokes to the right
for
example) to arrive at a cell. However when I arrive at a cell, the
macro
just uses the cell number lets say H20 and doesn't remember how I got
there
from using the keyboard. What I'm trying to do is create a macro
which
graphs data from several different worksheets. The problem is the data
from
the different worksheets isn't the size of the data from which the
macro
was
created. When I run the macro on different worksheets it doesn't graph
the
data from the same starting points or ending points. If I can't use a
use
a
macro to do this, is there any advice for what I'm trying to
accomplish?

 

[Bob Phillips]

Getting the last row is quite simple as long as you know where you judge it
against. So for instance if column A holds the id, check it back against
there.

The code I gave creates a range object oCell where the found data resides.
You just offset against this.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

I think it would be a lot harder if I had to determine the last row, so what
I do is just go down 200 or so cells after my starting point. The graph
doesn't include empty cells so I'm okay. I just see a lot of problems with
this because I'm using a lot of offsetting. I'll test that code tomorrow at
work but I have a question though. My macro includes a lot of steps and
offsetting. Should I go by your code to complete the rest of my macro, or are
there are special steps for me to to do to complete the rest of my macro with
an offset?

That will teach me to test it.

Amended code below, but how do you determine where the last row is, M201 in
your recorded macro? I think the code might need amending to manage that

Dim oCell As Range
Dim iRow As Long

Set oCell = Cells.Find(What:="1", _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
If Not oCell Is Nothing Then
With oCell.Offset(2, 6)
iRow = Cells(Rows.Count, oCell.Column).End(xlUp).Row
.FormulaR1C1 = "=(RC[-11]*R10C2)-R10C2"
.AutoFill Destination:=Range(Cells(.Row, .Column), Cells(iRow,
..Column)), _
Type:=xlFillDefault
End With
End If

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

It says "invalid qualifier" and points to iRow in the line right

before
the

End With.

:

No tested this, but this should do it

Dim oCell As Range
Dim iRow As Long

Set oCell = Cells.Find(What:="1", _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
If Not oCell Is Nothing Then
With oCell.Offset(2, 6)
iRow = Cells(Rows.Count, .Column).End(xlUp).Row
.FormulaR1C1 = "=(RC[-11]*R10C2)-R10C2"
.AutoFill Destination:=Cells(iRow.Column), Type:=xlFillDefault
End With
End If


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Thanks Bob. It's good news that I can get the macro to do what I'm looking
for except the only bad news is I've never done anything with

visual
basic

before.
I recorded the macro, so what code should I change exactly to implement
the
offset?

Here's a section of code from the macro.

Cells.Find(What:="1", After:=ActiveCell, LookIn:=xlFormulas,

LookAt:=
_

xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Range("M39").Select
ActiveCell.FormulaR1C1 = "=(RC[-11]*R10C2)-R10C2"
Range("M39").Select
Selection.AutoFill Destination:=Range("M39:M201"), Type:=xlFillDefault
Range("M39:M201").Select
Range("A1").Select

Do I just change the Range values to the formula that you listed? Thanks.

:

In a word, there is no way to get the recorder to do that.

The macro can do what you want though, you just have to modify

it to
do

so.
For instance, to find a value, use Find

Set cell = a_specified_range.Find("value")

and then to offset it use

If Not cell Is Nothing Then

cell.Offset(2,6).Select

etc.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Is there a way to make a macro remember my keystrokes? When I record a
macro
it only remembers the cell number of the box that I clicked

and
not

how I
got
there. What I would like to do is use ctrl+F to find what I'm looking
for
and
then use the keyboard (2 keystrokes down and 6 keystrokes to

the
right

for
example) to arrive at a cell. However when I arrive at a

cell,
the

macro
just uses the cell number lets say H20 and doesn't remember

how I
got

there
from using the keyboard. What I'm trying to do is create a macro
which
graphs data from several different worksheets. The problem is

the
data

from
the different worksheets isn't the size of the data from

which
the

macro
was
created. When I run the macro on different worksheets it

doesn't
graph

the
data from the same starting points or ending points. If I

can't
use a

use
a
macro to do this, is there any advice for what I'm trying to
accomplish?

 

[Guest]

Bob, I deleted my section of code and replaced it with yours. I still got an
error. To be honest, it took me a half hour to get the correct syntax. There
was an extra "." in the code. I've never coded with Visual Basic before so
you can tell I have no idea how to fix the rest of my code. I don't mind
learning new things, but for what I'm trying to accomplish it may take me 10
hours to figure this out. If you could adjust my code here I would greatly
appreciate it. Here's the whole macro. I added comments with the ' sign.
Thanks Bob.

Sub CreateData()

Range("A1").Select
Cells.Find(What:="dev", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Cells.Find(What:="1", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Range("M39").Select
' This range needs to be offset (0,11)
ActiveCell.FormulaR1C1 = "=(RC[-11]*R10C2)-R10C2"
Range("M39").Select
' Range needs to be offset (0,11)
Selection.AutoFill Destination:=Range("M39:M201"), Type:=xlFillDefault
' M39:M201 needs to be offset. (M39 would be 11 boxes to the
' right. M201 would 11 boxes to the right and 200 down)

Range("M39:M201").Select
' offset again
Range("A1").Select
Cells.Find(What:="dev", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Cells.Find(What:="1", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Range("C39:C201").Select
' Don't need offset
Selection.Copy
Range("A1").Select
ActiveWindow.SmallScroll Down:=24
Cells.Find(What:="dev", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Cells.Find(What:="1", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Range("N39").Select
' offset (0,12)
ActiveSheet.Paste
Range("A1").Select
Cells.Find(What:="dev", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Cells.Find(What:="2", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Range("M100:M208").Select
' I have no idea how this would work. My data has 2 trials in column A
' The problem is I need to seperate them to get two seperate graphs
' In column A, my data is 1,1,1,1....etc. then trial 2 starts
' 2,2,2,2,2,2...etc. How would you offset it so that the first number 2 is
' selected everytime. Then from there offset (0,12)
Application.CutCopyMode = False
Selection.Copy
Range("A1").Select
Cells.Find(What:="dev", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Cells.Find(What:="1", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Range("L39").Select
' offset (0,9)
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Cells.Find(What:="dev", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Cells.Find(What:="2", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Range("C100:C208").Select
' again offset with the first number 2
Application.CutCopyMode = False
Selection.Copy
Range("A1").Select
Cells.Find(What:="dev", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Cells.Find(What:="1", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Range("O39").Select
' offset (0,13)
ActiveSheet.Paste
Range("A1").Select
Cells.Find(What:="dev", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Cells.Find(What:="2", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Range("L100:O208").Select
' offset with first 2
Application.CutCopyMode = False
Selection.ClearContents
Range("A1").Select
End Sub

Getting the last row is quite simple as long as you know where you judge it
against. So for instance if column A holds the id, check it back against
there.

The code I gave creates a range object oCell where the found data resides.
You just offset against this.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

I think it would be a lot harder if I had to determine the last row, so what
I do is just go down 200 or so cells after my starting point. The graph
doesn't include empty cells so I'm okay. I just see a lot of problems with
this because I'm using a lot of offsetting. I'll test that code tomorrow at
work but I have a question though. My macro includes a lot of steps and
offsetting. Should I go by your code to complete the rest of my macro, or are
there are special steps for me to to do to complete the rest of my macro with
an offset?

That will teach me to test it.

Amended code below, but how do you determine where the last row is, M201 in
your recorded macro? I think the code might need amending to manage that

Dim oCell As Range
Dim iRow As Long

Set oCell = Cells.Find(What:="1", _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
If Not oCell Is Nothing Then
With oCell.Offset(2, 6)
iRow = Cells(Rows.Count, oCell.Column).End(xlUp).Row
.FormulaR1C1 = "=(RC[-11]*R10C2)-R10C2"
.AutoFill Destination:=Range(Cells(.Row, .Column), Cells(iRow,
..Column)), _
Type:=xlFillDefault
End With
End If

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

It says "invalid qualifier" and points to iRow in the line right before
the
End With.

:

No tested this, but this should do it

Dim oCell As Range
Dim iRow As Long

Set oCell = Cells.Find(What:="1", _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
If Not oCell Is Nothing Then
With oCell.Offset(2, 6)
iRow = Cells(Rows.Count, .Column).End(xlUp).Row
.FormulaR1C1 = "=(RC[-11]*R10C2)-R10C2"
.AutoFill Destination:=Cells(iRow.Column),
Type:=xlFillDefault
End With
End If


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Thanks Bob. It's good news that I can get the macro to do what I'm
looking
for except the only bad news is I've never done anything with visual
basic
before.
I recorded the macro, so what code should I change exactly to
implement
the
offset?

Here's a section of code from the macro.

Cells.Find(What:="1", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=
_
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Range("M39").Select
ActiveCell.FormulaR1C1 = "=(RC[-11]*R10C2)-R10C2"
Range("M39").Select
Selection.AutoFill Destination:=Range("M39:M201"),
Type:=xlFillDefault
Range("M39:M201").Select
Range("A1").Select

Do I just change the Range values to the formula that you listed?
Thanks.

:

In a word, there is no way to get the recorder to do that.

The macro can do what you want though, you just have to modify it to
do
so.
For instance, to find a value, use Find

Set cell = a_specified_range.Find("value")

and then to offset it use

If Not cell Is Nothing Then

cell.Offset(2,6).Select

etc.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Is there a way to make a macro remember my keystrokes? When I
record a
macro
it only remembers the cell number of the box that I clicked and
not
how I
got
there. What I would like to do is use ctrl+F to find what I'm
looking
for
and
then use the keyboard (2 keystrokes down and 6 keystrokes to the
right
for
example) to arrive at a cell. However when I arrive at a cell,
the
macro
just uses the cell number lets say H20 and doesn't remember how I
got
there
from using the keyboard. What I'm trying to do is create a macro
which
graphs data from several different worksheets. The problem is the
data
from
the different worksheets isn't the size of the data from which
the
macro
was
created. When I run the macro on different worksheets it doesn't
graph
the
data from the same starting points or ending points. If I can't
use a
use
a
macro to do this, is there any advice for what I'm trying to
accomplish?

 

[Bob Phillips]

Hopefully, this is close to what you want. There are a couple of points
where I didn't know what to offset by, they are marked

Sub CreateData()
Dim oCell As Range

'''' First find
Set oCell = GetCell("1")

' This range needs to be offset (0,11)
oCell.Offset(0, 11).FormulaR1C1 = "=(RC[-11]*R10C2)-R10C2"
' M39:M201 needs to be offset. (M39 would be 11 boxes to the
' right. M201 would 11 boxes to the right and 200 down)
oCell.Offset(0, 11).AutoFill Destination:= _
oCell.Offset(0, 11).Resize(200), Type:=xlFillDefault

' Don't need offset
Range("C39:C201").Copy


'''' Second find
Set oCell = GetCell("1")

' offset (0,12)
oCell.Offset(0, 12).Paste


'''' Third find
Set oCell = GetCell("2")

' I have no idea how this would work.
' My data has 2 trials in column A
' The problem is I need to seperate them to get two
' seperate graphs
' In column A, my data is 1,1,1,1....etc. then trial 2
' starts 2,2,2,2,2,2...etc.
' How would you offset it so that the first number 2 is
' selected everytime. Then from there offset (0,12)
oCell.Offset(0, 12).Copy


'''' Fourth find
Set oCell = GetCell("2")

' offset (0,9)
oCell.Offset(0, 9).PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False


'''' Fifth find
Set oCell = GetCell("2")

' again offset with the first number 2
'????????????? offset by what <<<<<<<<<<<<<<<<<<<<<<<
Range("C100:C208").Copy


'''' Sixth find
Set oCell = GetCell("1")

' offset (0,13)
oCell.Offset(0, 13).Paste


'''' Seventh find
Set oCell = GetCell("2")

' offset with first 2
'????????????? offset by what <<<<<<<<<<<<<<<<<<<<<<<
Range("L100:O208").ClearContents

Range("A1").Select
Application.CutCopyMode = False
End Sub

Function GetCell(pValue) As Range
Dim oCell As Range
Set oCell = Cells.Find(What:="dev", _
After:=Range("A1"), _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

Set oCell = Cells.Find(What:=pValue, _
After:=oCell, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
Set GetCell = oCell
End Function


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Bob, I deleted my section of code and replaced it with yours. I still got an
error. To be honest, it took me a half hour to get the correct syntax. There
was an extra "." in the code. I've never coded with Visual Basic before so
you can tell I have no idea how to fix the rest of my code. I don't mind
learning new things, but for what I'm trying to accomplish it may take me 10
hours to figure this out. If you could adjust my code here I would greatly
appreciate it. Here's the whole macro. I added comments with the ' sign.
Thanks Bob.

Sub CreateData()

Range("A1").Select
Cells.Find(What:="dev", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Cells.Find(What:="1", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Range("M39").Select
' This range needs to be offset (0,11)
ActiveCell.FormulaR1C1 = "=(RC[-11]*R10C2)-R10C2"
Range("M39").Select
' Range needs to be offset (0,11)
Selection.AutoFill Destination:=Range("M39:M201"), Type:=xlFillDefault
' M39:M201 needs to be offset. (M39 would be 11 boxes to the
' right. M201 would 11 boxes to the right and 200 down)

Range("M39:M201").Select
' offset again
Range("A1").Select
Cells.Find(What:="dev", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Cells.Find(What:="1", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Range("C39:C201").Select
' Don't need offset
Selection.Copy
Range("A1").Select
ActiveWindow.SmallScroll Down:=24
Cells.Find(What:="dev", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Cells.Find(What:="1", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Range("N39").Select
' offset (0,12)
ActiveSheet.Paste
Range("A1").Select
Cells.Find(What:="dev", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Cells.Find(What:="2", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Range("M100:M208").Select
' I have no idea how this would work. My data has 2 trials in column A
' The problem is I need to seperate them to get two seperate graphs
' In column A, my data is 1,1,1,1....etc. then trial 2 starts
' 2,2,2,2,2,2...etc. How would you offset it so that the first number 2 is
' selected everytime. Then from there offset (0,12)
Application.CutCopyMode = False
Selection.Copy
Range("A1").Select
Cells.Find(What:="dev", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Cells.Find(What:="1", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Range("L39").Select
' offset (0,9)
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Cells.Find(What:="dev", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Cells.Find(What:="2", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Range("C100:C208").Select
' again offset with the first number 2
Application.CutCopyMode = False
Selection.Copy
Range("A1").Select
Cells.Find(What:="dev", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Cells.Find(What:="1", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Range("O39").Select
' offset (0,13)
ActiveSheet.Paste
Range("A1").Select
Cells.Find(What:="dev", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Cells.Find(What:="2", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Range("L100:O208").Select
' offset with first 2
Application.CutCopyMode = False
Selection.ClearContents
Range("A1").Select
End Sub

Getting the last row is quite simple as long as you know where you judge it
against. So for instance if column A holds the id, check it back against
there.

The code I gave creates a range object oCell where the found data resides.
You just offset against this.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

I think it would be a lot harder if I had to determine the last row,

so
what

I do is just go down 200 or so cells after my starting point. The graph
doesn't include empty cells so I'm okay. I just see a lot of problems with
this because I'm using a lot of offsetting. I'll test that code

tomorrow
at

work but I have a question though. My macro includes a lot of steps and
offsetting. Should I go by your code to complete the rest of my macro,

or
are

there are special steps for me to to do to complete the rest of my

macro
with

an offset?

:

That will teach me to test it.

Amended code below, but how do you determine where the last row is,

M201
in

your recorded macro? I think the code might need amending to manage that

Dim oCell As Range
Dim iRow As Long

Set oCell = Cells.Find(What:="1", _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
If Not oCell Is Nothing Then
With oCell.Offset(2, 6)
iRow = Cells(Rows.Count, oCell.Column).End(xlUp).Row
.FormulaR1C1 = "=(RC[-11]*R10C2)-R10C2"
.AutoFill Destination:=Range(Cells(.Row, .Column), Cells(iRow,
..Column)), _
Type:=xlFillDefault
End With
End If

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

It says "invalid qualifier" and points to iRow in the line right before
the
End With.

:

No tested this, but this should do it

Dim oCell As Range
Dim iRow As Long

Set oCell = Cells.Find(What:="1", _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
If Not oCell Is Nothing Then
With oCell.Offset(2, 6)
iRow = Cells(Rows.Count, .Column).End(xlUp).Row
.FormulaR1C1 = "=(RC[-11]*R10C2)-R10C2"
.AutoFill Destination:=Cells(iRow.Column),
Type:=xlFillDefault
End With
End If


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Thanks Bob. It's good news that I can get the macro to do what I'm
looking
for except the only bad news is I've never done anything with visual
basic
before.
I recorded the macro, so what code should I change exactly to
implement
the
offset?

Here's a section of code from the macro.

Cells.Find(What:="1", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=
_
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Range("M39").Select
ActiveCell.FormulaR1C1 = "=(RC[-11]*R10C2)-R10C2"
Range("M39").Select
Selection.AutoFill Destination:=Range("M39:M201"),
Type:=xlFillDefault
Range("M39:M201").Select
Range("A1").Select

Do I just change the Range values to the formula that you listed?
Thanks.

:

In a word, there is no way to get the recorder to do that.

The macro can do what you want though, you just have to

modify
it to

do
so.
For instance, to find a value, use Find

Set cell = a_specified_range.Find("value")

and then to offset it use

If Not cell Is Nothing Then

cell.Offset(2,6).Select

etc.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Is there a way to make a macro remember my keystrokes? When I
record a
macro
it only remembers the cell number of the box that I

clicked
and

not
how I
got
there. What I would like to do is use ctrl+F to find what I'm
looking
for
and
then use the keyboard (2 keystrokes down and 6 keystrokes

to
the

right
for
example) to arrive at a cell. However when I arrive at a cell,
the
macro
just uses the cell number lets say H20 and doesn't

remember
how I

got
there
from using the keyboard. What I'm trying to do is create

a
macro

which
graphs data from several different worksheets. The problem

is
the

data
from
the different worksheets isn't the size of the data from which
the
macro
was
created. When I run the macro on different worksheets it doesn't
graph
the
data from the same starting points or ending points. If I can't
use a
use
a
macro to do this, is there any advice for what I'm trying to
accomplish?

 

[Guest]

I'm getting a runtime error 438, 'Ojbect doesn't support this property or
method' for this line "oCell.Offset(0, 12).Paste". Bob, just curious are you
working for Microsoft? If you aren't, I feel bad asking you to do all this
work for me. I feel almost as if I want to buy a book and learn all this
material. Is there anything you recommend for me to learn what I'm trying to
accomplish? Unless of course the easist way if for you to help me finish the
code. Only if you don't mind though.

Hopefully, this is close to what you want. There are a couple of points
where I didn't know what to offset by, they are marked

Sub CreateData()
Dim oCell As Range

'''' First find
Set oCell = GetCell("1")

' This range needs to be offset (0,11)
oCell.Offset(0, 11).FormulaR1C1 = "=(RC[-11]*R10C2)-R10C2"
' M39:M201 needs to be offset. (M39 would be 11 boxes to the
' right. M201 would 11 boxes to the right and 200 down)
oCell.Offset(0, 11).AutoFill Destination:= _
oCell.Offset(0, 11).Resize(200), Type:=xlFillDefault

' Don't need offset
Range("C39:C201").Copy


'''' Second find
Set oCell = GetCell("1")

' offset (0,12)
oCell.Offset(0, 12).Paste


'''' Third find
Set oCell = GetCell("2")

' I have no idea how this would work.
' My data has 2 trials in column A
' The problem is I need to seperate them to get two
' seperate graphs
' In column A, my data is 1,1,1,1....etc. then trial 2
' starts 2,2,2,2,2,2...etc.
' How would you offset it so that the first number 2 is
' selected everytime. Then from there offset (0,12)
oCell.Offset(0, 12).Copy


'''' Fourth find
Set oCell = GetCell("2")

' offset (0,9)
oCell.Offset(0, 9).PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False


'''' Fifth find
Set oCell = GetCell("2")

' again offset with the first number 2
'????????????? offset by what <<<<<<<<<<<<<<<<<<<<<<<
Range("C100:C208").Copy


'''' Sixth find
Set oCell = GetCell("1")

' offset (0,13)
oCell.Offset(0, 13).Paste


'''' Seventh find
Set oCell = GetCell("2")

' offset with first 2
'????????????? offset by what <<<<<<<<<<<<<<<<<<<<<<<
Range("L100:O208").ClearContents

Range("A1").Select
Application.CutCopyMode = False
End Sub

Function GetCell(pValue) As Range
Dim oCell As Range
Set oCell = Cells.Find(What:="dev", _
After:=Range("A1"), _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

Set oCell = Cells.Find(What:=pValue, _
After:=oCell, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
Set GetCell = oCell
End Function


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Bob, I deleted my section of code and replaced it with yours. I still got an
error. To be honest, it took me a half hour to get the correct syntax. There
was an extra "." in the code. I've never coded with Visual Basic before so
you can tell I have no idea how to fix the rest of my code. I don't mind
learning new things, but for what I'm trying to accomplish it may take me 10
hours to figure this out. If you could adjust my code here I would greatly
appreciate it. Here's the whole macro. I added comments with the ' sign.
Thanks Bob.

Sub CreateData()

Range("A1").Select
Cells.Find(What:="dev", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Cells.Find(What:="1", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Range("M39").Select
' This range needs to be offset (0,11)
ActiveCell.FormulaR1C1 = "=(RC[-11]*R10C2)-R10C2"
Range("M39").Select
' Range needs to be offset (0,11)
Selection.AutoFill Destination:=Range("M39:M201"), Type:=xlFillDefault
' M39:M201 needs to be offset. (M39 would be 11 boxes to the
' right. M201 would 11 boxes to the right and 200 down)

Range("M39:M201").Select
' offset again
Range("A1").Select
Cells.Find(What:="dev", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Cells.Find(What:="1", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Range("C39:C201").Select
' Don't need offset
Selection.Copy
Range("A1").Select
ActiveWindow.SmallScroll Down:=24
Cells.Find(What:="dev", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Cells.Find(What:="1", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Range("N39").Select
' offset (0,12)
ActiveSheet.Paste
Range("A1").Select
Cells.Find(What:="dev", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Cells.Find(What:="2", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Range("M100:M208").Select
' I have no idea how this would work. My data has 2 trials in column A
' The problem is I need to seperate them to get two seperate graphs
' In column A, my data is 1,1,1,1....etc. then trial 2 starts
' 2,2,2,2,2,2...etc. How would you offset it so that the first number 2 is
' selected everytime. Then from there offset (0,12)
Application.CutCopyMode = False
Selection.Copy
Range("A1").Select
Cells.Find(What:="dev", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Cells.Find(What:="1", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Range("L39").Select
' offset (0,9)
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Cells.Find(What:="dev", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Cells.Find(What:="2", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Range("C100:C208").Select
' again offset with the first number 2
Application.CutCopyMode = False
Selection.Copy
Range("A1").Select
Cells.Find(What:="dev", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Cells.Find(What:="1", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Range("O39").Select
' offset (0,13)
ActiveSheet.Paste
Range("A1").Select
Cells.Find(What:="dev", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Cells.Find(What:="2", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Range("L100:O208").Select
' offset with first 2
Application.CutCopyMode = False
Selection.ClearContents
Range("A1").Select
End Sub

Getting the last row is quite simple as long as you know where you judge it
against. So for instance if column A holds the id, check it back against
there.

The code I gave creates a range object oCell where the found data resides.
You just offset against this.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

I think it would be a lot harder if I had to determine the last row, so
what
I do is just go down 200 or so cells after my starting point. The graph
doesn't include empty cells so I'm okay. I just see a lot of problems with
this because I'm using a lot of offsetting. I'll test that code tomorrow
at
work but I have a question though. My macro includes a lot of steps and
offsetting. Should I go by your code to complete the rest of my macro, or
are
there are special steps for me to to do to complete the rest of my macro
with
an offset?

:

That will teach me to test it.

Amended code below, but how do you determine where the last row is,

M201

 

[Bob Phillips]

Chris,

One of the difficulties I am having is in trying to understand what you are
trying to achieve.

I know you look for dev, then the 1 after that (sometimes 2), but why dev,
why 1/2, and why offset afterwards. Personally I don't like hard-coding, as
it always fails at some point, I much prefer event/rule driven code.

If you explain that to me I can actually test the code, without it it is
difficult to work out the data.

And no, I don't work for MS, this is voluntary. We'll talk books after
solving the problem.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

I'm getting a runtime error 438, 'Ojbect doesn't support this property or
method' for this line "oCell.Offset(0, 12).Paste". Bob, just curious are you
working for Microsoft? If you aren't, I feel bad asking you to do all this
work for me. I feel almost as if I want to buy a book and learn all this
material. Is there anything you recommend for me to learn what I'm trying to
accomplish? Unless of course the easist way if for you to help me finish the
code. Only if you don't mind though.

Hopefully, this is close to what you want. There are a couple of points
where I didn't know what to offset by, they are marked

Sub CreateData()
Dim oCell As Range

'''' First find
Set oCell = GetCell("1")

' This range needs to be offset (0,11)
oCell.Offset(0, 11).FormulaR1C1 = "=(RC[-11]*R10C2)-R10C2"
' M39:M201 needs to be offset. (M39 would be 11 boxes to the
' right. M201 would 11 boxes to the right and 200 down)
oCell.Offset(0, 11).AutoFill Destination:= _
oCell.Offset(0, 11).Resize(200), Type:=xlFillDefault

' Don't need offset
Range("C39:C201").Copy


'''' Second find
Set oCell = GetCell("1")

' offset (0,12)
oCell.Offset(0, 12).Paste


'''' Third find
Set oCell = GetCell("2")

' I have no idea how this would work.
' My data has 2 trials in column A
' The problem is I need to seperate them to get two
' seperate graphs
' In column A, my data is 1,1,1,1....etc. then trial 2
' starts 2,2,2,2,2,2...etc.
' How would you offset it so that the first number 2 is
' selected everytime. Then from there offset (0,12)
oCell.Offset(0, 12).Copy


'''' Fourth find
Set oCell = GetCell("2")

' offset (0,9)
oCell.Offset(0, 9).PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False


'''' Fifth find
Set oCell = GetCell("2")

' again offset with the first number 2
'????????????? offset by what <<<<<<<<<<<<<<<<<<<<<<<
Range("C100:C208").Copy


'''' Sixth find
Set oCell = GetCell("1")

' offset (0,13)
oCell.Offset(0, 13).Paste


'''' Seventh find
Set oCell = GetCell("2")

' offset with first 2
'????????????? offset by what <<<<<<<<<<<<<<<<<<<<<<<
Range("L100:O208").ClearContents

Range("A1").Select
Application.CutCopyMode = False
End Sub

Function GetCell(pValue) As Range
Dim oCell As Range
Set oCell = Cells.Find(What:="dev", _
After:=Range("A1"), _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

Set oCell = Cells.Find(What:=pValue, _
After:=oCell, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
Set GetCell = oCell
End Function


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Bob, I deleted my section of code and replaced it with yours. I still

got
an

error. To be honest, it took me a half hour to get the correct syntax. There
was an extra "." in the code. I've never coded with Visual Basic before so
you can tell I have no idea how to fix the rest of my code. I don't mind
learning new things, but for what I'm trying to accomplish it may take

me
10

hours to figure this out. If you could adjust my code here I would greatly
appreciate it. Here's the whole macro. I added comments with the ' sign.
Thanks Bob.

Sub CreateData()

Range("A1").Select
Cells.Find(What:="dev", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Cells.Find(What:="1", After:=ActiveCell, LookIn:=xlFormulas,

LookAt:=
_

xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Range("M39").Select
' This range needs to be offset (0,11)
ActiveCell.FormulaR1C1 = "=(RC[-11]*R10C2)-R10C2"
Range("M39").Select
' Range needs to be offset (0,11)
Selection.AutoFill Destination:=Range("M39:M201"), Type:=xlFillDefault
' M39:M201 needs to be offset. (M39 would be 11 boxes to the
' right. M201 would 11 boxes to the right and 200 down)

Range("M39:M201").Select
' offset again
Range("A1").Select
Cells.Find(What:="dev", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Cells.Find(What:="1", After:=ActiveCell, LookIn:=xlFormulas,

LookAt:=
_

xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Range("C39:C201").Select
' Don't need offset
Selection.Copy
Range("A1").Select
ActiveWindow.SmallScroll Down:=24
Cells.Find(What:="dev", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Cells.Find(What:="1", After:=ActiveCell, LookIn:=xlFormulas,

LookAt:=
_

xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Range("N39").Select
' offset (0,12)
ActiveSheet.Paste
Range("A1").Select
Cells.Find(What:="dev", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Cells.Find(What:="2", After:=ActiveCell, LookIn:=xlFormulas,

LookAt:=
_

xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Range("M100:M208").Select
' I have no idea how this would work. My data has 2 trials in column A
' The problem is I need to seperate them to get two seperate graphs
' In column A, my data is 1,1,1,1....etc. then trial 2 starts
' 2,2,2,2,2,2...etc. How would you offset it so that the first number 2 is
' selected everytime. Then from there offset (0,12)
Application.CutCopyMode = False
Selection.Copy
Range("A1").Select
Cells.Find(What:="dev", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Cells.Find(What:="1", After:=ActiveCell, LookIn:=xlFormulas,

LookAt:=
_

xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Range("L39").Select
' offset (0,9)
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Cells.Find(What:="dev", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Cells.Find(What:="2", After:=ActiveCell, LookIn:=xlFormulas,

LookAt:=
_

xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Range("C100:C208").Select
' again offset with the first number 2
Application.CutCopyMode = False
Selection.Copy
Range("A1").Select
Cells.Find(What:="dev", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Cells.Find(What:="1", After:=ActiveCell, LookIn:=xlFormulas,

LookAt:=
_

xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Range("O39").Select
' offset (0,13)
ActiveSheet.Paste
Range("A1").Select
Cells.Find(What:="dev", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Cells.Find(What:="2", After:=ActiveCell, LookIn:=xlFormulas,

LookAt:=
_

xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Range("L100:O208").Select
' offset with first 2
Application.CutCopyMode = False
Selection.ClearContents
Range("A1").Select
End Sub

:

Getting the last row is quite simple as long as you know where you

judge
it

against. So for instance if column A holds the id, check it back against
there.

The code I gave creates a range object oCell where the found data resides.
You just offset against this.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

I think it would be a lot harder if I had to determine the last

row,
so

what
I do is just go down 200 or so cells after my starting point. The graph
doesn't include empty cells so I'm okay. I just see a lot of

problems
with

this because I'm using a lot of offsetting. I'll test that code tomorrow
at
work but I have a question though. My macro includes a lot of

steps
and

offsetting. Should I go by your code to complete the rest of my

macro,
or

are
there are special steps for me to to do to complete the rest of my macro
with
an offset?

:

That will teach me to test it.

Amended code below, but how do you determine where the last row

is,
M201

 

[Guest]

Ok Bob I'll try to explain exactly what I'm trying to do. Hopefully I'll get
it all right in one shot. I have about a hundred data sheets all with
different data and I'm trying to create two macros which basically plot all
the data with a click of a button which makes things very simple for me. One
macro creates data and another macro does the graphing. The reason why I need
a macro to create data is because the data from the datasheets are not in
graph form. However, all the data from the sheets can be converted to graph
form with a few simple cell references. Also, all the data from the
worksheets start at the same starting locations points which is was why I
thought I would be able to use a macro. However, the number of cells of each
data sheet is different, which is why I must use offsetting and not absolute
cells. Here's an example of a data sheet that I have and what exactly I'm
trying to do. Each data sheet will have a different number of cells
containing data. I only kept the main data and took out the misc.

HEADER
Target-count 61
Targets
0 25 50 75
125 150 175 200
250 275 300 325
375 400 425 450
500 525 550 575
625 650 675 700
750 775 800 825
875 900 925 950
1000 1025 1050 1075
1125 1150 1175 1200
1250 1275 1300 1325
1375 1400 1425 1450
1500

DEVIATIONS
Run Target Data
1 1 0
1 2 4.2
1 3 4.4
1 4 3.7
2 4 2.5
2 3 3.2
2 2 2.8
2 1 -0.1

The list is about another 50 data points long for Runs 1 and 2 so I
shortened it for simplicity. The numbers up top {0,25,50.....1500} right
under the word "Targets" are the distances in mm that I need to correlate to
Runs 1 and 2. A simple solution would to just turn the numbers up
top{0,25,50.....1500} right into one new column but I couldn't figure out how
to do that so I took the long route. The first "1" below "Run" should really
be 0 , the next "1" should be 25, etc. all the way to up to 1500. The first
"2" right after the last "1" starts at 1500 and works its way back down to 0
in decrements of 25. Unfortunately, All the data sheets are in this format.
However, the cells starting points are all the same for all the sheets. E.g.
The numbers up top {0,25,50,....1500} always begin at cell A10, and the first
"1" of "Run" always begins two cells down from Deviation. So basically what's
changing from data sheet to data sheet is the length of the cells up top
(sometimes its 0-500, 0-700, etc.) and the length of data for runs 1 and 2
(50 data points each, 75 data points each, etc). A major issue was graphing
the data. After I cell referenced Runs 1 and 2 to be in increments and and
decrements, repsectively, and after I copied all the data points under the
"Data" column, when I tried to graph these data points Excel does not create
two seperate series from the data(I need two seperate series because it it's
two seperate runs). The reason why I'm cell referencing is because sometimes
the numbers up top{0,25,50,....1500} go in increments of 20,100, or 50. So my
formula = (B39*$B$10)-$B$10. To be honest I have no idea how it turned out to
work perfectly but It basically perfectly maps the numbers up top
{0,25,50....1500} to a new column using Target(below, right next to Run)
(1,2,3,4, etc) as B39 * Targets(up top, {0,25,50,....1500}) as $B$10, and
then subtracts Targets ($B$10) to create the successful formula. I then cell
dragged all the way down and it successfully incremented up 1500 by 25's and
then back down to 0 by decrementing by 25's. For example this is what my
data will look like in a few columns to the right after I cell reference:

0 0
25 4.2
50 4.4
75 3.7
75 2.5
50 3.2
25 2.8
0 -.1

If I graph the two columns Excel puts them in 1 series even though it's
really two series.
So what I have to do is use Find to find the the first "2" in the Runs
column. Go a certain amount of cells over to the right and copy all the
points down and use paste special (values only) them in a seperate column.
Then use Find "dev" then Find "2" and delete all the the values I just used
to make a copy. Then I do the same thing for the Y data points. Now I have 4
columns looking like this:

0 0 75 2.5
25 4.2 50 3.2
50 4.4 25 2.8
75 3.7 0 -.1

Now I'm able to create 2 series in a chart, which is my final goal.

Hopefully That explained the jist of what I'm trying to do. It wouldn't be
so much of a pain if the data was in an easier format to begin with but of
course things are never that easy. If this wasn't clear enough let me know
I'll try to explain it better. I really appreciate everything that you're
trying for me Bob.

Chris,

One of the difficulties I am having is in trying to understand what you are
trying to achieve.

I know you look for dev, then the 1 after that (sometimes 2), but why dev,
why 1/2, and why offset afterwards. Personally I don't like hard-coding, as
it always fails at some point, I much prefer event/rule driven code.

If you explain that to me I can actually test the code, without it it is
difficult to work out the data.

And no, I don't work for MS, this is voluntary. We'll talk books after
solving the problem.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

I'm getting a runtime error 438, 'Ojbect doesn't support this property or
method' for this line "oCell.Offset(0, 12).Paste". Bob, just curious are you
working for Microsoft? If you aren't, I feel bad asking you to do all this
work for me. I feel almost as if I want to buy a book and learn all this
material. Is there anything you recommend for me to learn what I'm trying to
accomplish? Unless of course the easist way if for you to help me finish the
code. Only if you don't mind though.

Hopefully, this is close to what you want. There are a couple of points
where I didn't know what to offset by, they are marked

Sub CreateData()
Dim oCell As Range

'''' First find
Set oCell = GetCell("1")

' This range needs to be offset (0,11)
oCell.Offset(0, 11).FormulaR1C1 = "=(RC[-11]*R10C2)-R10C2"
' M39:M201 needs to be offset. (M39 would be 11 boxes to the
' right. M201 would 11 boxes to the right and 200 down)
oCell.Offset(0, 11).AutoFill Destination:= _
oCell.Offset(0, 11).Resize(200), Type:=xlFillDefault

' Don't need offset
Range("C39:C201").Copy


'''' Second find
Set oCell = GetCell("1")

' offset (0,12)
oCell.Offset(0, 12).Paste


'''' Third find
Set oCell = GetCell("2")

' I have no idea how this would work.
' My data has 2 trials in column A
' The problem is I need to seperate them to get two
' seperate graphs
' In column A, my data is 1,1,1,1....etc. then trial 2
' starts 2,2,2,2,2,2...etc.
' How would you offset it so that the first number 2 is
' selected everytime. Then from there offset (0,12)
oCell.Offset(0, 12).Copy


'''' Fourth find
Set oCell = GetCell("2")

' offset (0,9)
oCell.Offset(0, 9).PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False


'''' Fifth find
Set oCell = GetCell("2")

' again offset with the first number 2
'????????????? offset by what <<<<<<<<<<<<<<<<<<<<<<<
Range("C100:C208").Copy


'''' Sixth find
Set oCell = GetCell("1")

' offset (0,13)
oCell.Offset(0, 13).Paste


'''' Seventh find
Set oCell = GetCell("2")

' offset with first 2
'????????????? offset by what <<<<<<<<<<<<<<<<<<<<<<<
Range("L100:O208").ClearContents

Range("A1").Select
Application.CutCopyMode = False
End Sub

Function GetCell(pValue) As Range
Dim oCell As Range
Set oCell = Cells.Find(What:="dev", _
After:=Range("A1"), _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

Set oCell = Cells.Find(What:=pValue, _
After:=oCell, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
Set GetCell = oCell
End Function


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Bob, I deleted my section of code and replaced it with yours. I still got
an
error. To be honest, it took me a half hour to get the correct syntax.
There
was an extra "." in the code. I've never coded with Visual Basic before so
you can tell I have no idea how to fix the rest of my code. I don't mind
learning new things, but for what I'm trying to accomplish it may take me
10
hours to figure this out. If you could adjust my code here I would greatly
appreciate it. Here's the whole macro. I added comments with the ' sign.
Thanks Bob.

Sub CreateData()

Range("A1").Select
Cells.Find(What:="dev", After:=ActiveCell, LookIn:=xlFormulas,
LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Cells.Find(What:="1", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=
_
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Range("M39").Select
' This range needs to be offset (0,11)
ActiveCell.FormulaR1C1 = "=(RC[-11]*R10C2)-R10C2"
Range("M39").Select
' Range needs to be offset (0,11)
Selection.AutoFill Destination:=Range("M39:M201"), Type:=xlFillDefault
' M39:M201 needs to be offset. (M39 would be 11 boxes to the
' right. M201 would 11 boxes to the right and 200 down)

Range("M39:M201").Select
' offset again
Range("A1").Select
Cells.Find(What:="dev", After:=ActiveCell, LookIn:=xlFormulas,
LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Cells.Find(What:="1", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=
_
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Range("C39:C201").Select
' Don't need offset
Selection.Copy
Range("A1").Select
ActiveWindow.SmallScroll Down:=24
Cells.Find(What:="dev", After:=ActiveCell, LookIn:=xlFormulas,
LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Cells.Find(What:="1", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=
_
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Range("N39").Select
' offset (0,12)
ActiveSheet.Paste
Range("A1").Select
Cells.Find(What:="dev", After:=ActiveCell, LookIn:=xlFormulas,
LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Cells.Find(What:="2", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=
_
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Range("M100:M208").Select
' I have no idea how this would work. My data has 2 trials in column A
' The problem is I need to seperate them to get two seperate graphs
' In column A, my data is 1,1,1,1....etc. then trial 2 starts
' 2,2,2,2,2,2...etc. How would you offset it so that the first number 2 is
' selected everytime. Then from there offset (0,12)
Application.CutCopyMode = False
Selection.Copy
Range("A1").Select
Cells.Find(What:="dev", After:=ActiveCell, LookIn:=xlFormulas,
LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Cells.Find(What:="1", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=
_
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Range("L39").Select
' offset (0,9)
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Cells.Find(What:="dev", After:=ActiveCell, LookIn:=xlFormulas,
LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Cells.Find(What:="2", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=
_
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Range("C100:C208").Select
' again offset with the first number 2
Application.CutCopyMode = False
Selection.Copy
Range("A1").Select
Cells.Find(What:="dev", After:=ActiveCell, LookIn:=xlFormulas,
LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Cells.Find(What:="1", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=
_
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Range("O39").Select
' offset (0,13)
ActiveSheet.Paste
Range("A1").Select
Cells.Find(What:="dev", After:=ActiveCell, LookIn:=xlFormulas,
LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Cells.Find(What:="2", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=
_
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate