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