X Cell Problem

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Michael Conroy - 11 Apr 2008 16:50 GMT

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

Signature

Michael Conroy
Stamford, CT

Reply to this Message

Steve - 11 Apr 2008 16:59 GMT

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

Steve
rlaird@penn.com

>I am trying to speed up the repetitive tasks of editing a cell. My formula
>is
[quoted text clipped - 14 lines]
> in
> Excel. Thanks for your help

Reply to this Message

Jim Thomlinson - 11 Apr 2008 17:11 GMT

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)

Signature

HTH...

Jim Thomlinson

> 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
[quoted text clipped - 7 lines]
> commands. My Access skills don't translate and I want to learn a little VB in
> Excel. Thanks for your help

Reply to this Message

Rick Rothstein (MVP - VB) - 11 Apr 2008 17:16 GMT

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

>I am trying to speed up the repetitive tasks of editing a cell. My formula
>is
[quoted text clipped - 14 lines]
> in
> Excel. Thanks for your help

Reply to this Message

Michael Conroy - 11 Apr 2008 17:57 GMT

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.

Signature

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
[quoted text clipped - 20 lines]
> > in
> > Excel. Thanks for your help

Reply to this Message

Rick Rothstein (MVP - VB) - 11 Apr 2008 18:22 GMT

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

> That's the issue, I don't want the contents of the cell to be in the
> macro,
[quoted text clipped - 60 lines]
>> > in
>> > Excel. Thanks for your help

Reply to this Message

Michael Conroy - 11 Apr 2008 18:43 GMT

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.

Signature

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
[quoted text clipped - 79 lines]
> >> > in
> >> > Excel. Thanks for your help

Reply to this Message

Rick Rothstein (MVP - VB) - 11 Apr 2008 19:03 GMT

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

> Rick,
> That did it. Replaced the $ and added the trim text, all in one formula,
[quoted text clipped - 111 lines]
>> >> > in
>> >> > Excel. Thanks for your help

Reply to this Message

Michael Conroy - 11 Apr 2008 19:40 GMT

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?

Signature

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
[quoted text clipped - 137 lines]
> >> >> > in
> >> >> > Excel. Thanks for your help

Reply to this Message

Rick Rothstein (MVP - VB) - 11 Apr 2008 20:25 GMT

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

> Well, I know I am only copying over seven columns because the source file
> has
[quoted text clipped - 180 lines]
>> >> >> > in
>> >> >> > Excel. Thanks for your help

Reply to this Message

Michael Conroy - 11 Apr 2008 21:36 GMT

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

Signature

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
[quoted text clipped - 203 lines]
> >> >> >> > in
> >> >> >> > Excel. Thanks for your help

Reply to this Message