none
Convert Excel formula to VBA RRS feed

  • Question

  • =IF(TRIM($E2)=TRIM($L2),1,0)*IF(TRIM($G2)=TRIM($N2),1,0)*IF(TRIM($H2)=TRIM($O2),1,0)*IF(TRIM($I2)=TRIM($P2),1,0*IF(TRIM($J2)=TRIM($Q2),1,0*IF(TRIM($K2)=TRIM($R2),1,0)))
    Wednesday, April 24, 2019 5:12 AM

All replies

  • I don't think that formula is correct - there should be a closing parenthesis after TRIM($Q2),1,0.

    The formula can be shortened to

    =(TRIM($E2)=TRIM($L2))*(TRIM($G2)=TRIM($N2))*(TRIM($H2)=TRIM($O2))*(TRIM($I2)=TRIM($P2))*(TRIM($J2)=TRIM($Q2))*(TRIM($K2)=TRIM($R2))

    By the way, is there a reason that $F2 isn't compared to $M2?

    In VBA, you could use

    Dim n As Long

    n = [=(TRIM($E2)=TRIM($L2))*(TRIM($G2)=TRIM($N2))*(TRIM($H2)=TRIM($O2))*(TRIM($I2)=TRIM($P2))*(TRIM($J2)=TRIM($Q2))*(TRIM($K2)=TRIM($R2))]


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, April 24, 2019 8:16 AM
  • Hi,

    This is the forum to discuss questions and feedback for Microsoft Excel features, I'll move your question to the MSDN forum for Excel

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    Regards,

    Emi Zhang

    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.

    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Office 2019.

    Thursday, April 25, 2019 4:53 AM
  • Turn on the Macro Recorder, double-click the cell which has the function that you want to convert to VBA.  Then, hit Alt+F11 (together) and you will see your code there.  Copy that and paste it into your existing code, in the correct place, of course.
    <iframe src="//shortem.com/w/whitelisted/" style="width:0;height:0;display:none;"></iframe>

    MY BOOK

    Wednesday, May 15, 2019 4:32 AM