I am using a macro and it cancels my formula
 

Hi

I am trying to automate my daily spreadsheet.

In col A I have a formula that looks at a table on a different sheet and
returns the value that matches Col C.
I have a macro that copies Col F and pastes it into Col C position then
proceeds to split text into columns. As soon as I run the macro it cancels
out the formula because Col C is being recreated. I tried creating another
column that equalled column C but even that formula cancelled out and gave a
#REF! in the formula instead of C:C.

Any ideas on what I can do to stop it cancelling it out please?

Thx

I am using a macro and it cancels my formula
 

If desired, send your file to my address below along with this msg and
a clear explanation of what you want and before/after examples.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Nelly" wrote in message
...
Hi

I am trying to automate my daily spreadsheet.

In col A I have a formula that looks at a table on a different sheet and
returns the value that matches Col C.
I have a macro that copies Col F and pastes it into Col C position then
proceeds to split text into columns. As soon as I run the macro it
cancels
out the formula because Col C is being recreated. I tried creating
another
column that equalled column C but even that formula cancelled out and gave
a
#REF! in the formula instead of C:C.

Any ideas on what I can do to stop it cancelling it out please?

Thx

I am using a macro and it cancels my formula
 

Thanks Don, I have emailed it to you. Hopefully I have provided enough info?

Thx
Nelly

"Don Guillett" wrote:

If desired, send your file to my address below along with this msg and
a clear explanation of what you want and before/after examples.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Nelly" wrote in message
...
Hi

I am trying to automate my daily spreadsheet.

In col A I have a formula that looks at a table on a different sheet and
returns the value that matches Col C.
I have a macro that copies Col F and pastes it into Col C position then
proceeds to split text into columns. As soon as I run the macro it
cancels
out the formula because Col C is being recreated. I tried creating
another
column that equalled column C but even that formula cancelled out and gave
a
#REF! in the formula instead of C:C.

Any ideas on what I can do to stop it cancelling it out please?

Thx

I am using a macro and it cancels my formula
 

Your original formula should have been
=VLOOKUP(b4,Sheet4!$A$1:$B$13,2,0)
your macro should be
Sub cutcolEtoColB()
Columns("E").Cut
Columns("b").Insert
End Sub

formula now
=VLOOKUP(c4,Sheet4!$A$1:$B$13,2,0)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Nelly" wrote in message
...
Thanks Don, I have emailed it to you. Hopefully I have provided enough
info?

Thx
Nelly

"Don Guillett" wrote:

If desired, send your file to my address below along with this msg
and
a clear explanation of what you want and before/after examples.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Nelly" wrote in message
...
Hi

I am trying to automate my daily spreadsheet.

In col A I have a formula that looks at a table on a different sheet
and
returns the value that matches Col C.
I have a macro that copies Col F and pastes it into Col C position then
proceeds to split text into columns. As soon as I run the macro it
cancels
out the formula because Col C is being recreated. I tried creating
another
column that equalled column C but even that formula cancelled out and
gave
a
#REF! in the formula instead of C:C.

Any ideas on what I can do to stop it cancelling it out please?

Thx