+ Reply to Thread
Results 1 to 12 of 12

Copy Value String text of Formula become Formula but the formula not automatically active

  1. #1
    Registered User
    Join Date
    06-05-2014
    Posts
    8

    Copy Value String text of Formula become Formula but the formula not automatically active

    I make this kind of string formula:
    ="=IF($C14=$CC$5;"&"$CC$5;IFERROR("&"VLOOKUP($C14;"&"'"&REPLACE(CELL("filename");FIND("[";CELL("filename");1);(FIND("]";CELL("filename");1)+1-FIND("[";CELL("filename");1));'SETING AWAL PERIODE'!$Y$10)&"'!"&"$C$15:$CU$215;"&"'"&REPLACE(CELL("filename");FIND("[";CELL("filename");1);(FIND("]";CELL("filename");1)+1-FIND("[";CELL("filename");1));'SETING AWAL PERIODE'!$Y$10)&"'!"&"CM$14;FALSE);$CC$5))"

    Then copy Value to another Cell, it become a kind of active Formula:

    =IF($C14=$CC$5;$CC$5;IFERROR(VLOOKUP($C14;'D:\YOVAN\INDUSTRI\MONTHLY SALARY\[SalaryNopember2014.xlsm]ABSENSI'!$C$15:$CU$215;'D:\YOVAN\INDUSTRI\MONTHLY SALARY\[SalaryNopember2014.xlsm]ABSENSI'!CM$14;FALSE);$CC$5))

    The problem is this formula only active after pressing F2 and Enter

    Please help me how to make this formula automatically active without pressing F2 and Enter
    And the code in VBA if still need using F2 Enter.

    Thanks For any helping hands

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Copy Value String text of Formula become Formula but the formula not automatically act

    Can you post the code you are using to copy the value into the other cell?
    If posting code please use code tags, see here.

  3. #3
    Registered User
    Join Date
    06-05-2014
    Posts
    8

    Re: Copy Value String text of Formula become Formula but the formula not automatically act

    This is the code i made to copy value

    Range("CC13").Select 'Source of the Text string formula to copy value
    Selection.Copy
    Range("CC14").Select 'Destination cell the text string become formula
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Application.CutCopyMode = False
    Dim Msg, Style, Title, Help, Ctxt, Response, MyString
    Msg = "After Click OK, Please do these: 1.Press F2 2. Press Enter 3. Click Copy Link; Thanks!" ' Define message & Instruction.
    Style = vbYes + vbCritical + vbDefaultButton2 ' Define buttons.
    Title = "ATTENTION!!" ' Define title.
    Help = "DEMO.HLP" ' Define Help file.
    Ctxt = 1000 ' Define topic
    ' context.
    ' Display message.
    Response = MsgBox(Msg, Style, Title, Help, Ctxt)
    If Response = vbYes Then ' User chose Yes.
    MyString = "Yes" ' Perform some action.
    Dim myRange As Range
    Set myRange = Worksheets("ABSENSI").Range("CC14")
    answer = (myRange)
    Worksheets("ABSENSI").Range("CC14").Formula = "Activate=[answer]"
    ActiveCell.Select
    Else ' User chose No.
    MyString = "No" ' Perform some action.
    End If
    Range("CC14").Select
    End Sub

    Please help me, Thanks!
    Last edited by Yovan Wisnu; 06-06-2014 at 10:59 AM. Reason: More explaination on the code

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Copy Value String text of Formula become Formula but the formula not automatically act

    Where's the string with the formula?

    Have you tried replacing the ; with ,?

    PS Can you add code tags when posting code?

  5. #5
    Registered User
    Join Date
    06-05-2014
    Posts
    8

    Re: Copy Value String text of Formula become Formula but the formula not automatically act

    -The source is cell CC13 and copy value to cell CC14
    -In my computer excel 2007 the "," must replace to ";" otherwise the formula won't work
    -Is using code Tags just the same to copy from my code to it?
    Thanks for friendly response

  6. #6
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Copy Value String text of Formula become Formula but the formula not automatically act

    As far as I know in VBA you need to use , instead of ;.

  7. #7
    Registered User
    Join Date
    06-05-2014
    Posts
    8

    Re: Copy Value String text of Formula become Formula but the formula not automatically act

    Yup, but my formula is in the worksheet, and ";" is used for formula instead of ","

  8. #8
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Copy Value String text of Formula become Formula but the formula not automatically act

    But when entering a formula using code VBA expects , instead of ;.

    What happens if you do replace the ; with ,?

    By the way, I still don't see where you are entering the formula.

    In the code you have this,
    Please Login or Register  to view this content.
    which will only enter the string 'Activate=[answer] in CC14 on 'ABSENSI.

    I would have expected to see something like this.
    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    06-05-2014
    Posts
    8

    Re: Copy Value String text of Formula become Formula but the formula not automatically act

    The Text string formula in cell CC13 is variable (automatically change the link refers to a cell in worksheet) I am not that clever to write it in VBA, so i made it in Worksheet. I use record macro to copy value it to cell CC14 after pressing F2 Enter the formula become active and copy it to all cell I need the result.
    Sorry, I am just the beginner in VBA, don't understand yet to use "code tags"
    Any formula in my worksheet Excel 2007 must use ";" instead of ","
    Thanks!

  10. #10
    Registered User
    Join Date
    06-05-2014
    Posts
    8

    Re: Copy Value String text of Formula become Formula but the formula not automatically act

    It doesn't work, Using the code:
    With Worksheets("ABSENSI")
    strFormula = .Range("CC13").Formula
    .Range("CC14").Formula = strFormula
    End With
    End Sub

    The result is still as Text String Formula (original source), instead of Formula:

    ="=IF($C14=$CC$5;"&"$CC$5;IFERROR("&"VLOOKUP($C14;"&"'"&REPLACE(CELL("filename");FIND("[";CELL("filename");1);(FIND("]";CELL("filename");1)+1-FIND("[";CELL("filename");1));'SETING AWAL PERIODE'!$Y$10)&"'!"&"$C$15:$CU$215;"&"'"&REPLACE(CELL("filename");FIND("[";CELL("filename");1);(FIND("]";CELL("filename");1)+1-FIND("[";CELL("filename");1));'SETING AWAL PERIODE'!$Y$10)&"'!"&"CM$14;FALSE);$CC$5))"

  11. #11
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643
    Can you upload a sample workbook?

    PS Code tags have nothing to do with Excel/VBA, they are a method that makes any code you post here easier to read and copy

    To learn how to use them click the link in my signature.

  12. #12
    Registered User
    Join Date
    06-05-2014
    Posts
    8

    Re: Copy Value String text of Formula become Formula but the formula not automatically act

    TestSalaryDesember2014.xlsmTestSalaryNopember2014.xlsm
    Please Help Me! Thanks
    To update directory go to "SETING AWAL PRIODE" Click "Click Me" and Click "Save As"
    To make Last month File: Change Month, Click "Click Me", Click "Save As"
    Last edited by Yovan Wisnu; 06-06-2014 at 03:45 PM. Reason: More Information about the file attached

+ 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. [SOLVED] Help with a Formula to build a working formula from a text string
    By Mallycat in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-06-2013, 11:51 PM
  2. [SOLVED] Use a formula to check if another formula contains specific text/string
    By musketeer in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 04-24-2013, 05:09 PM
  3. Replies: 6
    Last Post: 03-13-2013, 12:40 AM
  4. Need to copy a formula that automatically changes selected sheet in formula
    By jenniferhebert in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-06-2012, 03:37 PM
  5. [SOLVED] How do I create a formula in excel to automatically copy text fro.
    By ijgolding in forum Excel General
    Replies: 1
    Last Post: 01-31-2005, 09:06 AM

Tags for this Thread

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