Replace within formulas

[fouad.khoury]

Hello

i have the following formula:

=DAY(Project1_In!C2) & "/" & MONTH(Project1_In!C2) & "/" & YEAR(Project1_In!C2)

i want to change it to the following:


=DAY(INDEX(Project1_In!C:C,2,1)) & "/" & MONTH(INDEX(Project1_In!C:C,2,1)) & "/" & YEAR(INDEX(Project1_In!C:C,2,1))

This has to be applied to cells from row 2 to row 100 in the same column E.

so
=DAY(Project1_In!C3) & "/" & MONTH(Project1_In!C3) & "/" & YEAR(Project1_In!C3) to
=DAY(INDEX(Project1_In!C:C,3,1)) & "/" & MONTH(INDEX(Project1_In!C:C,3,1)) & "/" & YEAR(INDEX(Project1_In!C:C,3,1))

and

=DAY(Project1_In!C4) & "/" & MONTH(Project1_In!C4) & "/" & YEAR(Project1_In!C4) to
=DAY(INDEX(Project1_In!C:C,4,1)) & "/" & MONTH(INDEX(Project1_In!C:C,4,1)) & "/" & YEAR(INDEX(Project1_In!C:C,4,1))

and so on until 100.

kindly advise on how to do i t in VB?

Regards,
Fouad

[jindon]

Can I just ask why do you want to change it like that?

They are both doing the same thing and the first formula is clearer and faster.

[fouad.khoury]

you are right that the first formula is clearer and faster, however i have to do the replacement because am running a macro on a worksheet that is overwriting the cells.
Once cells are overwritten, am receiving the #ref error within formula. A friend has suggested to me to replace with index formula.
Hope this is clear.

Regards,
Fouad

[jindon]

Not really sure, about "overwriting" thingy though.

Please Login or Register  to view this content.

Adjust bold part to your need.

[fouad.khoury]

Hello,

The issue is not solved: i think because of the macro, here is detailed explanation about the process:
1- The starting point is a Microsoft project plan file, a macro is run in the file to output an excel file (with selected columns): let's call this file OUTPUT
2- another excel file with 2 worksheets A and B, another macro is run in this file to filter cells from OUTPUT and import into worksheet B.
3- before i import into worksheet B, i am deleting all the data in B.
4- Formula are in A reading from B
5- when i delete the cells in B and run the macro to import, i am getting #ref error in all formulas in A.

Hope this is clear.
kindly advise,
Fouad

[jindon]

What is this related to your original question?

You are asking completely different question now, so open a new thread with DETAILED question and EXACT RESULT that you want.