+ Reply to Thread
Results 1 to 6 of 6

Replace within formulas

  1. #1
    Registered User
    Join Date
    05-28-2021
    Location
    Lebanon
    MS-Off Ver
    Office 365
    Posts
    3

    Replace within formulas

    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

  2. #2
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,449

    Re: Replace within formulas

    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.

  3. #3
    Registered User
    Join Date
    05-28-2021
    Location
    Lebanon
    MS-Off Ver
    Office 365
    Posts
    3

    Re: Replace within formulas

    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

  4. #4
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,449

    Re: Replace within formulas

    Not really sure, about "overwriting" thingy though.
    Please Login or Register  to view this content.
    Adjust bold part to your need.

  5. #5
    Registered User
    Join Date
    05-28-2021
    Location
    Lebanon
    MS-Off Ver
    Office 365
    Posts
    3

    Re: Replace within formulas

    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

  6. #6
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,449

    Re: Replace within formulas

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Find and Replace within Formulas
    By adamgrier in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-09-2009, 10:43 PM
  2. Find and replace in formulas
    By naquer in forum Excel General
    Replies: 5
    Last Post: 07-20-2009, 04:40 PM
  3. Replace values in formulas
    By Joe.lel in forum Excel General
    Replies: 3
    Last Post: 03-20-2008, 09:25 AM
  4. Find/Replace in formulas
    By robbert in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-15-2008, 03:31 PM
  5. Replies: 1
    Last Post: 07-19-2006, 05:45 PM
  6. Replace Formulas with Values
    By mazo in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-10-2006, 12:10 PM
  7. [SOLVED] Search - replace NOT in formulas
    By Michael Preminger in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-18-2005, 05:06 PM
  8. Find & replace in formulas
    By itsmetisa in forum Excel General
    Replies: 2
    Last Post: 01-28-2005, 03:02 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1