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?

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)

"Chris" wrote in message
...
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?

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" wrote:

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)

"Chris" wrote in message
...
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?

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)

"Chris" wrote in message
...
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" wrote:

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)

"Chris" wrote in message
...
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?

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

"Bob Phillips" wrote:

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)

"Chris" wrote in message
...
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" wrote:

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)

"Chris" wrote in message
...
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?

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)

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

"Bob Phillips" wrote:

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)

"Chris" wrote in message
...
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" wrote:

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)

"Chris" wrote in message
...
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?