Doing it's own thing - Jim R - Check here

  • Thread starter =?iso-8859-1?B?QW5kcuk=?=
  • Start date
?

=?iso-8859-1?B?QW5kcuk=?=

That's what I'm saying, and this is the 3rd time i've
recorded the macro with similar results every time. Any
ideas on why or how this happens or how to fix it?

A.
/sorry for the delay, was a busy weekend.
-----Original Message-----
It's almost as if you're running a different macro than the one you posted.

--
Jim Rech
Excel MVP
Yeah the formula is getting the new names from another
page. But the main thing is that it is not doing anything
that I did when i recorded the macro. Instead it deletes
my title in A1 and all the formating with it as well as
delets all the colum titles including the one in cell B1.

A
-----Original Message-----
The macro is supposed to rename 5 of the columns
Click to expand...

Your macro isn't doing any renaming. It's deleting the original columns F,
H, I and J. And putting a formula in B2 that refers to A1 on sheet
ListOptions, a formula in C2 that refers to B1 on sheet ListOptions. Etc
through F2.
Have a macro that keeps deleting what i have in the
Click to expand...
cell A1

It not doing that for me. A1 is not being deleted when i run your code.

Have you tried debugging the macro? Press F8 in the VBE to walk through the
code line by line.

Btw, while your code seems to work as far as I can tell, it can be boiled
down quite a bit. This does the same thing:

Sub Shorter()
Range("F:F,H:J").Delete
Range("B2:F2").FormulaR1C1 = _
"=IF(ListOptions!R[-1]C[-1]="""","""",ListOptions!R[- 1]C[-1])"
End Sub
Doing it's own thing
Click to expand...

Macros never do their own thing. They do what you tell them to do. You
just have to tell them the right thing;-)

--
Jim Rech
Excel MVP
| Have a macro that keeps deleting what i have in the cell
| A1 which is streched over a range of columns but also
| deleting all the column headings that i have from B2 to J2.
| The macro is supposed to rename 5 of the columns and
| delete 4. What i endup with is an almost empty page
| except for the heading for the last column (f2). Here is
| the code. (now just to note that the if statement that i
| use is actually =if(listoptions!B2="","",listoptions! b2)
| and not what is showing up in the code for some reason)
|
| A.
|
| Range("B2").Select
| ActiveCell.FormulaR1C1 = _
| "=IF(ListOptions!R[-1]C[-1] ="""","""",ListOptions!R
| [-1]C[-1])"
| Range("C2").Select
| ActiveCell.FormulaR1C1 = _
| "=IF(ListOptions!R[-1]C[-1] ="""","""",ListOptions!R
| [-1]C[-1])"
| Range("D2").Select
| ActiveCell.FormulaR1C1 = _
| "=IF(ListOptions!R[-1]C[-1] ="""","""",ListOptions!R
| [-1]C[-1])"
| Range("E2").Select
| ActiveCell.FormulaR1C1 = _
| "=IF(ListOptions!R[-1]C[-1] ="""","""",ListOptions!R
| [-1]C[-1])"
| Columns("F:F").Select
| Range("F2").Activate
| Selection.Delete Shift:=xlToLeft
| Columns("G:I").Select
| Range("G2").Activate
| Selection.Delete Shift:=xlToLeft
| Range("F2").Select
| ActiveCell.FormulaR1C1 = _
| "=IF(ListOptions!R[-1]C[-1] ="""","""",ListOptions!R
| [-1]C[-1])"
| End Sub
|


.
Click to expand...
Click to expand...
 
T

Tom Ogilvy

Better to describe the functionality of what you want to do and then someone
might be show a better way. Did you try Jim's suggested rewrite?

--
Regards,
Tom Ogilvy

That's what I'm saying, and this is the 3rd time i've
recorded the macro with similar results every time. Any
ideas on why or how this happens or how to fix it?

A.
/sorry for the delay, was a busy weekend.
-----Original Message-----
It's almost as if you're running a different macro than the one you posted.

--
Jim Rech
Excel MVP
Yeah the formula is getting the new names from another
page. But the main thing is that it is not doing anything
that I did when i recorded the macro. Instead it deletes
my title in A1 and all the formating with it as well as
delets all the colum titles including the one in cell B1.

A
-----Original Message-----
The macro is supposed to rename 5 of the columns
Click to expand...

Your macro isn't doing any renaming. It's deleting the original columns F,
H, I and J. And putting a formula in B2 that refers to A1 on sheet
ListOptions, a formula in C2 that refers to B1 on sheet ListOptions. Etc
through F2.
Have a macro that keeps deleting what i have in the
Click to expand...
cell A1

It not doing that for me. A1 is not being deleted when i run your code.

Have you tried debugging the macro? Press F8 in the VBE to walk through the
code line by line.

Btw, while your code seems to work as far as I can tell, it can be boiled
down quite a bit. This does the same thing:

Sub Shorter()
Range("F:F,H:J").Delete
Range("B2:F2").FormulaR1C1 = _
"=IF(ListOptions!R[-1]C[-1]="""","""",ListOptions!R[- 1]C[-1])"
End Sub
Doing it's own thing
Click to expand...

Macros never do their own thing. They do what you tell them to do. You
just have to tell them the right thing;-)

--
Jim Rech
Excel MVP
| Have a macro that keeps deleting what i have in the cell
| A1 which is streched over a range of columns but also
| deleting all the column headings that i have from B2 to J2.
| The macro is supposed to rename 5 of the columns and
| delete 4. What i endup with is an almost empty page
| except for the heading for the last column (f2). Here is
| the code. (now just to note that the if statement that i
| use is actually =if(listoptions!B2="","",listoptions! b2)
| and not what is showing up in the code for some reason)
|
| A.
|
| Range("B2").Select
| ActiveCell.FormulaR1C1 = _
| "=IF(ListOptions!R[-1]C[-1] ="""","""",ListOptions!R
| [-1]C[-1])"
| Range("C2").Select
| ActiveCell.FormulaR1C1 = _
| "=IF(ListOptions!R[-1]C[-1] ="""","""",ListOptions!R
| [-1]C[-1])"
| Range("D2").Select
| ActiveCell.FormulaR1C1 = _
| "=IF(ListOptions!R[-1]C[-1] ="""","""",ListOptions!R
| [-1]C[-1])"
| Range("E2").Select
| ActiveCell.FormulaR1C1 = _
| "=IF(ListOptions!R[-1]C[-1] ="""","""",ListOptions!R
| [-1]C[-1])"
| Columns("F:F").Select
| Range("F2").Activate
| Selection.Delete Shift:=xlToLeft
| Columns("G:I").Select
| Range("G2").Activate
| Selection.Delete Shift:=xlToLeft
| Range("F2").Select
| ActiveCell.FormulaR1C1 = _
| "=IF(ListOptions!R[-1]C[-1] ="""","""",ListOptions!R
| [-1]C[-1])"
| End Sub
|


.
Click to expand...
Click to expand...
 
J

Jim Rech

this is the 3rd time i've recorded the macro

I think one recording is as far as you should go. The recorder is okay for
starting a macro if you need a push start but it rarely produces finished
code. After you record it's time to understand what was produced and figure
out how you can fix it or improve it.

You can try my code, as Tom suggested, or maybe better learn how to debug
your code by stepping through it a line at a time and watching what happens
in the worksheet.
 
?

=?iso-8859-1?B?QW5kcuk=?=

I'm not quite advanced or comfortable enough to actually
write out the code, I just record the actions I want done.
I've just tried the code that Jim provided, it seems to do
the trick, I'll be able to look at it more closely this
aft. thanks.

A.
-----Original Message-----
Better to describe the functionality of what you want to do and then someone
might be show a better way. Did you try Jim's suggested rewrite?

--
Regards,
Tom Ogilvy

That's what I'm saying, and this is the 3rd time i've
recorded the macro with similar results every time. Any
ideas on why or how this happens or how to fix it?

A.
/sorry for the delay, was a busy weekend.
-----Original Message-----
It's almost as if you're running a different macro than the one you posted.

--
Jim Rech
Excel MVP
Yeah the formula is getting the new names from another
page. But the main thing is that it is not doing anything
that I did when i recorded the macro. Instead it deletes
my title in A1 and all the formating with it as well as
delets all the colum titles including the one in cell B1.

A
-----Original Message-----
The macro is supposed to rename 5 of the columns

Your macro isn't doing any renaming. It's deleting the original columns F,
H, I and J. And putting a formula in B2 that refers to A1 on sheet
ListOptions, a formula in C2 that refers to B1 on sheet ListOptions. Etc
through F2.

Have a macro that keeps deleting what i have in the cell A1

It not doing that for me. A1 is not being deleted when
Click to expand...
i
run your code.
Have you tried debugging the macro? Press F8 in the VBE to walk through the
code line by line.

Btw, while your code seems to work as far as I can tell, it can be boiled
down quite a bit. This does the same thing:

Sub Shorter()
Range("F:F,H:J").Delete
Range("B2:F2").FormulaR1C1 = _
"=IF(ListOptions!R[-1]C[-1]="""","""",ListOptions!R
Click to expand...
[-
1]C[-1])"
End Sub

Doing it's own thing

Macros never do their own thing. They do what you tell them to do. You
just have to tell them the right thing;-)

--
Jim Rech
Excel MVP
| Have a macro that keeps deleting what i have in the cell
| A1 which is streched over a range of columns but also
| deleting all the column headings that i have from B2
Click to expand...
to
J2.
| The macro is supposed to rename 5 of the columns and
| delete 4. What i endup with is an almost empty page
| except for the heading for the last column (f2). Here is
| the code. (now just to note that the if statement that i
| use is actually =if(listoptions!B2="","",listoptions! b2)
| and not what is showing up in the code for some reason)
|
| A.
|
| Range("B2").Select
| ActiveCell.FormulaR1C1 = _
| "=IF(ListOptions!R[-1]C[-1] ="""","""",ListOptions!R
| [-1]C[-1])"
| Range("C2").Select
| ActiveCell.FormulaR1C1 = _
| "=IF(ListOptions!R[-1]C[-1] ="""","""",ListOptions!R
| [-1]C[-1])"
| Range("D2").Select
| ActiveCell.FormulaR1C1 = _
| "=IF(ListOptions!R[-1]C[-1] ="""","""",ListOptions!R
| [-1]C[-1])"
| Range("E2").Select
| ActiveCell.FormulaR1C1 = _
| "=IF(ListOptions!R[-1]C[-1] ="""","""",ListOptions!R
| [-1]C[-1])"
| Columns("F:F").Select
| Range("F2").Activate
| Selection.Delete Shift:=xlToLeft
| Columns("G:I").Select
| Range("G2").Activate
| Selection.Delete Shift:=xlToLeft
| Range("F2").Select
| ActiveCell.FormulaR1C1 = _
| "=IF(ListOptions!R[-1]C[-1] ="""","""",ListOptions!R
| [-1]C[-1])"
| End Sub
|


.
Click to expand...
Click to expand...


.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Doing it's own thing 3
I must make certain I have a specific range of numbers present. 3
Different Results from the Same Macro 2
Need to tailor my macro code. 9
relatetive reference-to perform a series of cal and return result 2
Help to create a macro 1
R[1]C[1] with a variable 2
Cleaner coding to repeat process 6

Top