I am trying to speed up the repetitive tasks of editing a cell. My formula is
simply pulling the value from another file, (copy and paste value won't work
and is not dynamic). I need to copy this formula across eight columns but the
original has absolute valued to the first column and row. Here is what I want.

I want to have a macro that edits the cell, hits F4 three times, types a
close paren, jumps to the beginning of the cell, moves right one space, types
Trim(, then hits enter. I know I can do this to the first cell myself and
then copy it across, but this activity happens often and I want to learn the
commands. My Access skills don't translate and I want to learn a little VB in
Excel. Thanks for your help
--
Michael Conroy
Stamford, CT

If you want to do it in Access, I can help you.

Steve



"Michael Conroy" wrote in message
...
I am trying to speed up the repetitive tasks of editing a cell. My formula
is
simply pulling the value from another file, (copy and paste value won't
work
and is not dynamic). I need to copy this formula across eight columns but
the
original has absolute valued to the first column and row. Here is what I
want.

I want to have a macro that edits the cell, hits F4 three times, types a
close paren, jumps to the beginning of the cell, moves right one space,
types
Trim(, then hits enter. I know I can do this to the first cell myself and
then copy it across, but this activity happens often and I want to learn
the
commands. My Access skills don't translate and I want to learn a little VB
in
Excel. Thanks for your help
--
Michael Conroy
Stamford, CT

What you have described is not really the way that macro's work. While you
can edit formulas you need to kind of build it more from scratch than have it
do an F4 3 times. Is your existing formula simply a referece to another cell?
If so then something like this will work

Sub EditFormula
activecell.formula = "=Trim($" & mid(activecell.formula, 2, 255) & ")"
End Sub
Changes =A1 to =Trim($A1)
--
HTH...

Jim Thomlinson


"Michael Conroy" wrote:

I am trying to speed up the repetitive tasks of editing a cell. My formula is
simply pulling the value from another file, (copy and paste value won't work
and is not dynamic). I need to copy this formula across eight columns but the
original has absolute valued to the first column and row. Here is what I want.

I want to have a macro that edits the cell, hits F4 three times, types a
close paren, jumps to the beginning of the cell, moves right one space, types
Trim(, then hits enter. I know I can do this to the first cell myself and
then copy it across, but this activity happens often and I want to learn the
commands. My Access skills don't translate and I want to learn a little VB in
Excel. Thanks for your help
--
Michael Conroy
Stamford, CT

A macro won't need to implement manual keystrokes so, instead of telling us
the keystroke sequences (what are the three F4s for?), show us a sample of
what is in the cell and what you want it to look like after editing.

Rick


"Michael Conroy" wrote in message
...
I am trying to speed up the repetitive tasks of editing a cell. My formula
is
simply pulling the value from another file, (copy and paste value won't
work
and is not dynamic). I need to copy this formula across eight columns but
the
original has absolute valued to the first column and row. Here is what I
want.

I want to have a macro that edits the cell, hits F4 three times, types a
close paren, jumps to the beginning of the cell, moves right one space,
types
Trim(, then hits enter. I know I can do this to the first cell myself and
then copy it across, but this activity happens often and I want to learn
the
commands. My Access skills don't translate and I want to learn a little VB
in
Excel. Thanks for your help
--
Michael Conroy
Stamford, CT

That's the issue, I don't want the contents of the cell to be in the macro,
just the key strokes. When I run the macro on cell A500, the formula
references a cell in another file, in Tab Austria at cell A8. I copy A500 to
G500, then that row gets copied down maybe thirty rows. Now in Cell A531, I
am pulling Cell A8 from tab Belgium in the same file as before. Now Cell A531
needs the absolute reference removed and the trim stuff added. Then it gets
copied over seven columns (G531) then down maybe 50 rows this time. So the
formula is irrelevant, editing it and adding trim or upper and removing the
absolute reference is my goal.

You have to understand that I am doing this to be able to get the data into
Access and this is the only way I can clean up a spreadsheet someone else
made, with over thirty countries in seperate tabs and different lengths of
information (rows). Once I trim, and upper these cell references, which needs
to be dynamic so that next month I can import them faster, I will import into
or link to this data from Access for a comparrison with other data. It's a
lot of steps. I hope this helps. I gave the keystrokes because I could do
this in Lotus 123 twenty years ago and I was hoping Excel could do the same
thing. No slight on Microsoft, I just thought that would be an easy way to
obtain my objective.
--
Michael Conroy
Stamford, CT


"Rick Rothstein (MVP - VB)" wrote:

A macro won't need to implement manual keystrokes so, instead of telling us
the keystroke sequences (what are the three F4s for?), show us a sample of
what is in the cell and what you want it to look like after editing.

Rick


"Michael Conroy" wrote in message
...
I am trying to speed up the repetitive tasks of editing a cell. My formula
is
simply pulling the value from another file, (copy and paste value won't
work
and is not dynamic). I need to copy this formula across eight columns but
the
original has absolute valued to the first column and row. Here is what I
want.

I want to have a macro that edits the cell, hits F4 three times, types a
close paren, jumps to the beginning of the cell, moves right one space,
types
Trim(, then hits enter. I know I can do this to the first cell myself and
then copy it across, but this activity happens often and I want to learn
the
commands. My Access skills don't translate and I want to learn a little VB
in
Excel. Thanks for your help
--
Michael Conroy
Stamford, CT

I understood that you do not want the cell contents hard coded into the
macro; but you need to understand that macro can do far, far more than
simply duplicate keystrokes. I think you added enough information in your
response for me to guess at what you want. With the cell you want to apply
the macro to selected, run this macro (Alt+F8, select MakeRelativeAddTrim
and Run)...

Sub MakeRelativeAddTrim()
With ActiveCell
.Formula = "=TRIM(" & Mid(Replace(.Formula, "$", ""), 2) & ")"
End With
End Sub

Did that do what you wanted? If so, can you describe what is controlling how
far across and down the converted cell is being copied? If you can do that,
I can incorporate the copying process into the macro as well.

Rick


"Michael Conroy" wrote in message
...
That's the issue, I don't want the contents of the cell to be in the
macro,
just the key strokes. When I run the macro on cell A500, the formula
references a cell in another file, in Tab Austria at cell A8. I copy A500
to
G500, then that row gets copied down maybe thirty rows. Now in Cell A531,
I
am pulling Cell A8 from tab Belgium in the same file as before. Now Cell
A531
needs the absolute reference removed and the trim stuff added. Then it
gets
copied over seven columns (G531) then down maybe 50 rows this time. So the
formula is irrelevant, editing it and adding trim or upper and removing
the
absolute reference is my goal.

You have to understand that I am doing this to be able to get the data
into
Access and this is the only way I can clean up a spreadsheet someone else
made, with over thirty countries in seperate tabs and different lengths of
information (rows). Once I trim, and upper these cell references, which
needs
to be dynamic so that next month I can import them faster, I will import
into
or link to this data from Access for a comparrison with other data. It's a
lot of steps. I hope this helps. I gave the keystrokes because I could do
this in Lotus 123 twenty years ago and I was hoping Excel could do the
same
thing. No slight on Microsoft, I just thought that would be an easy way to
obtain my objective.
--
Michael Conroy
Stamford, CT


"Rick Rothstein (MVP - VB)" wrote:

A macro won't need to implement manual keystrokes so, instead of telling
us
the keystroke sequences (what are the three F4s for?), show us a sample
of
what is in the cell and what you want it to look like after editing.

Rick


"Michael Conroy" wrote in
message
...
I am trying to speed up the repetitive tasks of editing a cell. My
formula
is
simply pulling the value from another file, (copy and paste value won't
work
and is not dynamic). I need to copy this formula across eight columns
but
the
original has absolute valued to the first column and row. Here is what
I
want.

I want to have a macro that edits the cell, hits F4 three times, types
a
close paren, jumps to the beginning of the cell, moves right one space,
types
Trim(, then hits enter. I know I can do this to the first cell myself
and
then copy it across, but this activity happens often and I want to
learn
the
commands. My Access skills don't translate and I want to learn a little
VB
in
Excel. Thanks for your help
--
Michael Conroy
Stamford, CT

Rick,
That did it. Replaced the $ and added the trim text, all in one formula,
great, thanks. The copying is not really important, I can do that manually,
particularly since I don't know how many rows each tab has. However,
educating me with the commands might prevent me from bothering you in the
future. Its wierd that I have been using spreadsheet for twenty years and
access for only four and I know way more code in Access. I kept trying to
type Me. and getting nothing. I'll remember ActiveCell now. Thanks again.
--
Michael Conroy
Stamford, CT


"Rick Rothstein (MVP - VB)" wrote:

I understood that you do not want the cell contents hard coded into the
macro; but you need to understand that macro can do far, far more than
simply duplicate keystrokes. I think you added enough information in your
response for me to guess at what you want. With the cell you want to apply
the macro to selected, run this macro (Alt+F8, select MakeRelativeAddTrim
and Run)...

Sub MakeRelativeAddTrim()
With ActiveCell
.Formula = "=TRIM(" & Mid(Replace(.Formula, "$", ""), 2) & ")"
End With
End Sub

Did that do what you wanted? If so, can you describe what is controlling how
far across and down the converted cell is being copied? If you can do that,
I can incorporate the copying process into the macro as well.

Rick


"Michael Conroy" wrote in message
...
That's the issue, I don't want the contents of the cell to be in the
macro,
just the key strokes. When I run the macro on cell A500, the formula
references a cell in another file, in Tab Austria at cell A8. I copy A500
to
G500, then that row gets copied down maybe thirty rows. Now in Cell A531,
I
am pulling Cell A8 from tab Belgium in the same file as before. Now Cell
A531
needs the absolute reference removed and the trim stuff added. Then it
gets
copied over seven columns (G531) then down maybe 50 rows this time. So the
formula is irrelevant, editing it and adding trim or upper and removing
the
absolute reference is my goal.

You have to understand that I am doing this to be able to get the data
into
Access and this is the only way I can clean up a spreadsheet someone else
made, with over thirty countries in seperate tabs and different lengths of
information (rows). Once I trim, and upper these cell references, which
needs
to be dynamic so that next month I can import them faster, I will import
into
or link to this data from Access for a comparrison with other data. It's a
lot of steps. I hope this helps. I gave the keystrokes because I could do
this in Lotus 123 twenty years ago and I was hoping Excel could do the
same
thing. No slight on Microsoft, I just thought that would be an easy way to
obtain my objective.
--
Michael Conroy
Stamford, CT


"Rick Rothstein (MVP - VB)" wrote:

A macro won't need to implement manual keystrokes so, instead of telling
us
the keystroke sequences (what are the three F4s for?), show us a sample
of
what is in the cell and what you want it to look like after editing.

Rick


"Michael Conroy" wrote in
message
...
I am trying to speed up the repetitive tasks of editing a cell. My
formula
is
simply pulling the value from another file, (copy and paste value won't
work
and is not dynamic). I need to copy this formula across eight columns
but
the
original has absolute valued to the first column and row. Here is what
I
want.

I want to have a macro that edits the cell, hits F4 three times, types
a
close paren, jumps to the beginning of the cell, moves right one space,
types
Trim(, then hits enter. I know I can do this to the first cell myself
and
then copy it across, but this activity happens often and I want to
learn
the
commands. My Access skills don't translate and I want to learn a little
VB
in
Excel. Thanks for your help
--
Michael Conroy
Stamford, CT

Me refers to the Workbook or UserForm (maybe others depending on context,
I'm not sure about that). However, if you are ever in doubt, just put this
code in the active code window...

Sub test()
End Sub

and put a break point on the End Sub statement and Run the code. When it
stops at the break point, execute this in the Immediate window...

? Me.Name

That will tell you what object Me refers to within the context its running
in.

As for telling you the commands to use to copy the formula across and
down... how to implement that would depend on what is controlling the width
and length of the copying. For example, are you copying down to the last
filled in row in another column? Down to a certain value is reached in
another column? To a specific row number? Etc. If you define a control
mechanism as to when to stop copying, it can be implemented in code. So, my
question to you is... how do you know when to stop copying across and down?
What are you locking into that tells you to stop?

Rick


"Michael Conroy" wrote in message
...
Rick,
That did it. Replaced the $ and added the trim text, all in one formula,
great, thanks. The copying is not really important, I can do that
manually,
particularly since I don't know how many rows each tab has. However,
educating me with the commands might prevent me from bothering you in the
future. Its wierd that I have been using spreadsheet for twenty years and
access for only four and I know way more code in Access. I kept trying to
type Me. and getting nothing. I'll remember ActiveCell now. Thanks again.
--
Michael Conroy
Stamford, CT


"Rick Rothstein (MVP - VB)" wrote:

I understood that you do not want the cell contents hard coded into the
macro; but you need to understand that macro can do far, far more than
simply duplicate keystrokes. I think you added enough information in your
response for me to guess at what you want. With the cell you want to
apply
the macro to selected, run this macro (Alt+F8, select MakeRelativeAddTrim
and Run)...

Sub MakeRelativeAddTrim()
With ActiveCell
.Formula = "=TRIM(" & Mid(Replace(.Formula, "$", ""), 2) & ")"
End With
End Sub

Did that do what you wanted? If so, can you describe what is controlling
how
far across and down the converted cell is being copied? If you can do
that,
I can incorporate the copying process into the macro as well.

Rick


"Michael Conroy" wrote in
message
...
That's the issue, I don't want the contents of the cell to be in the
macro,
just the key strokes. When I run the macro on cell A500, the formula
references a cell in another file, in Tab Austria at cell A8. I copy
A500
to
G500, then that row gets copied down maybe thirty rows. Now in Cell
A531,
I
am pulling Cell A8 from tab Belgium in the same file as before. Now
Cell
A531
needs the absolute reference removed and the trim stuff added. Then it
gets
copied over seven columns (G531) then down maybe 50 rows this time. So
the
formula is irrelevant, editing it and adding trim or upper and removing
the
absolute reference is my goal.

You have to understand that I am doing this to be able to get the data
into
Access and this is the only way I can clean up a spreadsheet someone
else
made, with over thirty countries in seperate tabs and different lengths
of
information (rows). Once I trim, and upper these cell references, which
needs
to be dynamic so that next month I can import them faster, I will
import
into
or link to this data from Access for a comparrison with other data.
It's a
lot of steps. I hope this helps. I gave the keystrokes because I could
do
this in Lotus 123 twenty years ago and I was hoping Excel could do the
same
thing. No slight on Microsoft, I just thought that would be an easy way
to
obtain my objective.
--
Michael Conroy
Stamford, CT


"Rick Rothstein (MVP - VB)" wrote:

A macro won't need to implement manual keystrokes so, instead of
telling
us
the keystroke sequences (what are the three F4s for?), show us a
sample
of
what is in the cell and what you want it to look like after editing.

Rick


"Michael Conroy" wrote in
message
...
I am trying to speed up the repetitive tasks of editing a cell. My
formula
is
simply pulling the value from another file, (copy and paste value
won't
work
and is not dynamic). I need to copy this formula across eight
columns
but
the
original has absolute valued to the first column and row. Here is
what
I
want.

I want to have a macro that edits the cell, hits F4 three times,
types
a
close paren, jumps to the beginning of the cell, moves right one
space,
types
Trim(, then hits enter. I know I can do this to the first cell
myself
and
then copy it across, but this activity happens often and I want to
learn
the
commands. My Access skills don't translate and I want to learn a
little
VB
in
Excel. Thanks for your help
--
Michael Conroy
Stamford, CT

This macro will ask you how many rows you will want the modified formula
copied down for, check to make sure the value you entered can be interpreted
as a number, make sure the number entered won't take the copy process past
the bottom of the worksheet, modify the formula in the same way my first
macro did, then copy that modified formula over 6 more columns and down the
number of rows you entered...

Sub MakeRelativeAddTrimCopyAcrossAndDown()
Dim Answer As Long
Answer = InputBox("How many rows down?")
If IsNumeric(Answer) Then
With ActiveCell
If Answer < .Parent.Rows.Count - ActiveCell.Row Then
.Formula = "=TRIM(" & Mid(Replace(.Formula, "$", ""), 2) & ")"
.Resize(1, 7).FillRight
.Resize(Answer, 7).FillDown
End If
End With
End If
End Sub


Rick



"Michael Conroy" wrote in message
...
Well, I know I am only copying over seven columns because the source file
has
forty identicaly designed sheets. However, I have to manually look at each
tab in the source file to see how many rows there are and copy my formula
down that many. So, to recap, the formula, minus the absolute formula, is
copies over seven columns, then that row is copied down 20, 50 or 200
times,
depending on the source. I have cleaned up the source such that a
Control-End
keyboard command will take me to the bottom right of the sheet, if that
helps. Let's do this, lets program it for fifty rows and I can look to see
if
the formula starts comming up empty, at which point I can assume we went
down
past any data. Then I can do a visual check and be done with it. Beside, I
think 50 will grab most of them, and I will visually check them all. How's
that?
--
Michael Conroy
Stamford, CT


"Rick Rothstein (MVP - VB)" wrote:

Me refers to the Workbook or UserForm (maybe others depending on context,
I'm not sure about that). However, if you are ever in doubt, just put
this
code in the active code window...

Sub test()
End Sub

and put a break point on the End Sub statement and Run the code. When it
stops at the break point, execute this in the Immediate window...

? Me.Name

That will tell you what object Me refers to within the context its
running
in.

As for telling you the commands to use to copy the formula across and
down... how to implement that would depend on what is controlling the
width
and length of the copying. For example, are you copying down to the last
filled in row in another column? Down to a certain value is reached in
another column? To a specific row number? Etc. If you define a control
mechanism as to when to stop copying, it can be implemented in code. So,
my
question to you is... how do you know when to stop copying across and
down?
What are you locking into that tells you to stop?

Rick


"Michael Conroy" wrote in
message
...
Rick,
That did it. Replaced the $ and added the trim text, all in one
formula,
great, thanks. The copying is not really important, I can do that
manually,
particularly since I don't know how many rows each tab has. However,
educating me with the commands might prevent me from bothering you in
the
future. Its wierd that I have been using spreadsheet for twenty years
and
access for only four and I know way more code in Access. I kept trying
to
type Me. and getting nothing. I'll remember ActiveCell now. Thanks
again.
--
Michael Conroy
Stamford, CT


"Rick Rothstein (MVP - VB)" wrote:

I understood that you do not want the cell contents hard coded into
the
macro; but you need to understand that macro can do far, far more than
simply duplicate keystrokes. I think you added enough information in
your
response for me to guess at what you want. With the cell you want to
apply
the macro to selected, run this macro (Alt+F8, select
MakeRelativeAddTrim
and Run)...

Sub MakeRelativeAddTrim()
With ActiveCell
.Formula = "=TRIM(" & Mid(Replace(.Formula, "$", ""), 2) & ")"
End With
End Sub

Did that do what you wanted? If so, can you describe what is
controlling
how
far across and down the converted cell is being copied? If you can do
that,
I can incorporate the copying process into the macro as well.

Rick


"Michael Conroy" wrote in
message
...
That's the issue, I don't want the contents of the cell to be in the
macro,
just the key strokes. When I run the macro on cell A500, the formula
references a cell in another file, in Tab Austria at cell A8. I copy
A500
to
G500, then that row gets copied down maybe thirty rows. Now in Cell
A531,
I
am pulling Cell A8 from tab Belgium in the same file as before. Now
Cell
A531
needs the absolute reference removed and the trim stuff added. Then
it
gets
copied over seven columns (G531) then down maybe 50 rows this time.
So
the
formula is irrelevant, editing it and adding trim or upper and
removing
the
absolute reference is my goal.

You have to understand that I am doing this to be able to get the
data
into
Access and this is the only way I can clean up a spreadsheet someone
else
made, with over thirty countries in seperate tabs and different
lengths
of
information (rows). Once I trim, and upper these cell references,
which
needs
to be dynamic so that next month I can import them faster, I will
import
into
or link to this data from Access for a comparrison with other data.
It's a
lot of steps. I hope this helps. I gave the keystrokes because I
could
do
this in Lotus 123 twenty years ago and I was hoping Excel could do
the
same
thing. No slight on Microsoft, I just thought that would be an easy
way
to
obtain my objective.
--
Michael Conroy
Stamford, CT


"Rick Rothstein (MVP - VB)" wrote:

A macro won't need to implement manual keystrokes so, instead of
telling
us
the keystroke sequences (what are the three F4s for?), show us a
sample
of
what is in the cell and what you want it to look like after
editing.

Rick


"Michael Conroy" wrote in
message
...
I am trying to speed up the repetitive tasks of editing a cell. My
formula
is
simply pulling the value from another file, (copy and paste value
won't
work
and is not dynamic). I need to copy this formula across eight
columns
but
the
original has absolute valued to the first column and row. Here is
what
I
want.

I want to have a macro that edits the cell, hits F4 three times,
types
a
close paren, jumps to the beginning of the cell, moves right one
space,
types
Trim(, then hits enter. I know I can do this to the first cell
myself
and
then copy it across, but this activity happens often and I want
to
learn
the
commands. My Access skills don't translate and I want to learn a
little
VB
in
Excel. Thanks for your help
--
Michael Conroy
Stamford, CT

Very Cool, worked like a charm. And I learned some terms: resize, fillright
and filldown. Thanks for the effort Rick.
--
Michael Conroy
Stamford, CT


"Rick Rothstein (MVP - VB)" wrote:

This macro will ask you how many rows you will want the modified formula
copied down for, check to make sure the value you entered can be interpreted
as a number, make sure the number entered won't take the copy process past
the bottom of the worksheet, modify the formula in the same way my first
macro did, then copy that modified formula over 6 more columns and down the
number of rows you entered...

Sub MakeRelativeAddTrimCopyAcrossAndDown()
Dim Answer As Long
Answer = InputBox("How many rows down?")
If IsNumeric(Answer) Then
With ActiveCell
If Answer < .Parent.Rows.Count - ActiveCell.Row Then
.Formula = "=TRIM(" & Mid(Replace(.Formula, "$", ""), 2) & ")"
.Resize(1, 7).FillRight
.Resize(Answer, 7).FillDown
End If
End With
End If
End Sub


Rick



"Michael Conroy" wrote in message
...
Well, I know I am only copying over seven columns because the source file
has
forty identicaly designed sheets. However, I have to manually look at each
tab in the source file to see how many rows there are and copy my formula
down that many. So, to recap, the formula, minus the absolute formula, is
copies over seven columns, then that row is copied down 20, 50 or 200
times,
depending on the source. I have cleaned up the source such that a
Control-End
keyboard command will take me to the bottom right of the sheet, if that
helps. Let's do this, lets program it for fifty rows and I can look to see
if
the formula starts comming up empty, at which point I can assume we went
down
past any data. Then I can do a visual check and be done with it. Beside, I
think 50 will grab most of them, and I will visually check them all. How's
that?
--
Michael Conroy
Stamford, CT


"Rick Rothstein (MVP - VB)" wrote:

Me refers to the Workbook or UserForm (maybe others depending on context,
I'm not sure about that). However, if you are ever in doubt, just put
this
code in the active code window...

Sub test()
End Sub

and put a break point on the End Sub statement and Run the code. When it
stops at the break point, execute this in the Immediate window...

? Me.Name

That will tell you what object Me refers to within the context its
running
in.

As for telling you the commands to use to copy the formula across and
down... how to implement that would depend on what is controlling the
width
and length of the copying. For example, are you copying down to the last
filled in row in another column? Down to a certain value is reached in
another column? To a specific row number? Etc. If you define a control
mechanism as to when to stop copying, it can be implemented in code. So,
my
question to you is... how do you know when to stop copying across and
down?
What are you locking into that tells you to stop?

Rick


"Michael Conroy" wrote in
message
...
Rick,
That did it. Replaced the $ and added the trim text, all in one
formula,
great, thanks. The copying is not really important, I can do that
manually,
particularly since I don't know how many rows each tab has. However,
educating me with the commands might prevent me from bothering you in
the
future. Its wierd that I have been using spreadsheet for twenty years
and
access for only four and I know way more code in Access. I kept trying
to
type Me. and getting nothing. I'll remember ActiveCell now. Thanks
again.
--
Michael Conroy
Stamford, CT


"Rick Rothstein (MVP - VB)" wrote:

I understood that you do not want the cell contents hard coded into
the
macro; but you need to understand that macro can do far, far more than
simply duplicate keystrokes. I think you added enough information in
your
response for me to guess at what you want. With the cell you want to
apply
the macro to selected, run this macro (Alt+F8, select
MakeRelativeAddTrim
and Run)...

Sub MakeRelativeAddTrim()
With ActiveCell
.Formula = "=TRIM(" & Mid(Replace(.Formula, "$", ""), 2) & ")"
End With
End Sub

Did that do what you wanted? If so, can you describe what is
controlling
how
far across and down the converted cell is being copied? If you can do
that,
I can incorporate the copying process into the macro as well.

Rick


"Michael Conroy" wrote in
message
...
That's the issue, I don't want the contents of the cell to be in the
macro,
just the key strokes. When I run the macro on cell A500, the formula
references a cell in another file, in Tab Austria at cell A8. I copy
A500
to
G500, then that row gets copied down maybe thirty rows. Now in Cell
A531,
I
am pulling Cell A8 from tab Belgium in the same file as before. Now
Cell
A531
needs the absolute reference removed and the trim stuff added. Then
it
gets
copied over seven columns (G531) then down maybe 50 rows this time.
So
the
formula is irrelevant, editing it and adding trim or upper and
removing
the
absolute reference is my goal.

You have to understand that I am doing this to be able to get the
data
into
Access and this is the only way I can clean up a spreadsheet someone
else
made, with over thirty countries in seperate tabs and different
lengths
of
information (rows). Once I trim, and upper these cell references,
which
needs
to be dynamic so that next month I can import them faster, I will
import
into
or link to this data from Access for a comparrison with other data.
It's a
lot of steps. I hope this helps. I gave the keystrokes because I
could
do
this in Lotus 123 twenty years ago and I was hoping Excel could do
the
same
thing. No slight on Microsoft, I just thought that would be an easy
way
to
obtain my objective.
--
Michael Conroy
Stamford, CT


"Rick Rothstein (MVP - VB)" wrote:

A macro won't need to implement manual keystrokes so, instead of
telling
us
the keystroke sequences (what are the three F4s for?), show us a
sample
of
what is in the cell and what you want it to look like after
editing.

Rick


"Michael Conroy" wrote in
message
...
I am trying to speed up the repetitive tasks of editing a cell. My
formula
is
simply pulling the value from another file, (copy and paste value
won't
work
and is not dynamic). I need to copy this formula across eight
columns
but
the
original has absolute valued to the first column and row. Here is
what
I
want.

I want to have a macro that edits the cell, hits F4 three times,
types
a
close paren, jumps to the beginning of the cell, moves right one
space,
types
Trim(, then hits enter. I know I can do this to the first cell
myself
and
then copy it across, but this activity happens often and I want
to
learn
the
commands. My Access skills don't translate and I want to learn a
little
VB
in
Excel. Thanks for your help
--
Michael Conroy
Stamford, CT