How to put values instead of formulas into matrix?

[Gijs Breedveld]

Hi, I am trying to reduce the size om my large file. Therefor I want to use a
macro to put values instead of formulas into a matrix.

The datafile is pulled from a database and looks as follows:
Col A : "Date in"
Col B : "Person"
Col C : "Date start"
Col D: "Date end"

I want to calculate
Col E: "week start"
Col D: "week end"
Col F: "Week start" - "week in"

The amount of lines increases each week by an unknown amount. Therefor the
code needs to look how many lines are added. Also the "date start" and "date
end" do change for existing records. Therefor all the lines need to be
recalculated every time.

I have been searching the files and putting pieces of code together but
nothing gave the wanted end result.

I hope someone can help me.

Best regards,

Gijs Breedveld

 

[Gary''s Student]

One approach:

1. store the spreadsheet without formulae
2. have a macro paste the formulae into the appropriate columns and convert
to values.
3. before storing the updated worksheet even the calculated values can be
cleared since the will be re-created at the next update

 

[Joergen Bondesen]

Hi Gijs

What is "week start" and how do i calculate this. Is it the week for "Date
start"

What is "week end" and how do i calculate this. Is it the week for "Date
end"

Is it the difference you want and you mean "week in"?

Are you quit sure aboute the new columns, D?

Date cells are real dates?


Can you give an e.g. please.