none
how to call a function . RRS feed

  • Question

  • Hi,

    I want to run a function only If a cell contains ' or '' along with other data . If no ' or '' then this function not to run. Is it possible .

    regards

    Sunday, November 8, 2015 4:48 PM

Answers

  • you could use the worksheet change event to evaluate every cell every time anything is typed ... but that would not really be a good idea ... and the code would be intense, especially if the value is contained within a cell and not the whole content.


    Warm Regards, Crystal http://www.AccessMVP.com/strive4peace * (: have an awesome day :)

    Thursday, November 12, 2015 7:26 PM

All replies

  • Hi,

    How/when do you want to call the function?

    You could check the value in Worksheet_Change event or you could have a button to trigger an event, then check the value.

    To get the cell value, you could use Range object.

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, November 10, 2015 3:33 AM
    Moderator
  • =IF(AND(ISERROR(FIND("'",B2)),  ISERROR(FIND(char(34),B2))),"",myfunction(B2))


    Warm Regards, Crystal http://www.AccessMVP.com/strive4peace * (: have an awesome day :)

    Tuesday, November 10, 2015 4:23 AM
  • Hi,

    Thank you,

    The problem lies in detecting ' or '' in cells from which values are calculated from . Say A1 has ' or '' , may be AA1 has ' or '' . The calculation is done in B1 or C1 , then how to get it done. May be in whole work only few cells has value like 9'8'' or 5'4'' , when their value is calculated , then only the function should work else normal calculation.

    regards

    Tuesday, November 10, 2015 6:50 AM
  • perhaps this will help:

    Working With Feet And Inches In Excel  
    by Chip Pearson
    http://www.cpearson.com/excel/feetinches.htm



    Warm Regards, Crystal http://www.AccessMVP.com/strive4peace * (: have an awesome day :)


    Tuesday, November 10, 2015 6:54 AM
  • Thank you ,

    Function at http://www.cpearson.com/excel/feetinches.htm

    meets an error.

    regards

    Tuesday, November 10, 2015 1:19 PM
  • Hi,

    I suggest that you need to specify the format, for the unclear data format, you may have the issue now or in the feature.

    For the format of 5’6”, we could use LEFT(A1, FIND(“’”,A1)-1) to get 5 and use MID(A1, FIND(“’”,A1)+1,LEN(A1)-FIND(“’”,A1)-1) to get 6. (As Crystal said)

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Thursday, November 12, 2015 3:23 AM
    Moderator
  • Hi,

    Thank you,

    If we use formula and help of another cell then it is better to go for function formula . That is working fine . I want it to run automatically where ever and whenever in workbook it is needed . And no need to call function . But it seems to not possible .

    regards

    Thursday, November 12, 2015 1:11 PM
  • you could use the worksheet change event to evaluate every cell every time anything is typed ... but that would not really be a good idea ... and the code would be intense, especially if the value is contained within a cell and not the whole content.


    Warm Regards, Crystal http://www.AccessMVP.com/strive4peace * (: have an awesome day :)

    Thursday, November 12, 2015 7:26 PM