ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Doing it's own thing (https://www.excelbanter.com/excel-programming/303674-doing-its-own-thing.html)

André

Doing it's own thing
 
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


Jim Rech

Doing it's own thing
 
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 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


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
"André" wrote in message
...
| 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
|



André

Doing it's own thing
 
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 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


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
"André" wrote in

message
...
| 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
|


.


Jim Rech

Doing it's own thing
 
It's almost as if you're running a different macro than the one you posted.

--
Jim Rech
Excel MVP
"André" wrote in message
...
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 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


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
"André" wrote in

message
...
| 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
|


.





All times are GMT +1. The time now is 11:53 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com