I am using a macro and it cancels my formula

[Nelly]

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

 

[Don Guillett]

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.

 

[Nelly]

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

Thx
Nelly

 

[Don Guillett]

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)