+ Reply to Thread
Results 1 to 16 of 16

Strange errors I think coming from ConcatenateIfs [UDF]

  1. #1
    Registered User
    Join Date
    06-21-2017
    Location
    USA
    MS-Off Ver
    2010
    Posts
    9

    Strange errors I think coming from ConcatenateIfs [UDF]

    Good morning all,

    I am new and not sure I am following proper protocol, but here it goes.

    I made an Excel sheet that in essence uses many sheets to track data then compile, reformat, and then display on a very pretty front sheet according to date entered by user.

    One this front sheet, there are many different ConcatenatesIfs formulae pulling from many different back sheets. On my computer and the computers of the majority of people this file opens, runs, and the date searchable information populates as it should via ConcatenateIfs formulae. (if more of the functionality needs to be explained or the sheet is needed please let me know) [Note that all of these people are running Excel 2010.]

    Here is an example of the ConcatenateIfs formula that is breaking Excel for some people:

    =ConcatenateIfs('sheet1'!$F$4:$F$500,sheet1'!$C$4:$C$500,'frontsheet'!C25,'sheet1'!$A$4:$A$500,'frontsheet'!$N$1,", ")
    Basically this pulls the comments from sheet 1 (column F) that correspond to the title in sheet 1 column C and date in sheet 1 column A that match the title listed in cell front sheet C25 and date listed in front sheet N1.


    However, on a hand full of computers, including that of the client, this sheet displays the following error and them promptly crashes Excel:
    Please Login or Register  to view this content.
    Moderator's note: Please take the time to review our rules. There aren't many, and they are all important. Rule #3 requires code tags. I have added them for you this time because you are a new member. --6StringJazzer

    This gets even weirder as a different file with the same type of functionality and ConcatenateIfs formulae is able to be opened on the very computers that crash when opening mine.

    I have run diagnostic software and the file is not corrupted. I initially thought maybe the file is too big/complex and is just crashing excel, but the other, different file with similar functionality is actually bigger and more complex.

    Could really use some help here as I have no idea what is going on and the type of VBA/coding is very much out of my realm of expertise and understanding.
    Thank you all in advance for your time and help.
    Last edited by 6StringJazzer; 06-21-2017 at 09:53 AM.

  2. #2
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Strange errors I think coming from ConcanateIfs

    Hi,

    I think you have posted the wrong function (you posted ConcatenateIf and not ConcatenateIfs) and you didn't say what error was displayed prior to the crash.
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  3. #3
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Strange errors I think coming from ConcanateIfs

    Quote Originally Posted by dixie12oz View Post
    Here is an example of the ConcatenateIfs formula that is breaking Excel for some people:
    The ConcenateIfs function is not built into Excel, and appears to be implemented as VBA (macro) code. But:

    However, on a hand full of computers, including that of the client, this sheet displays the following error and them promptly crashes Excel:
    The code you showed is for ConcatenateIf not ConcatenateIfs. I suspect that ConcatenateIfs is calling ConcatenateIf, and an error occur in ConcatenateIf. But, what you pasted in here is not an error; it's the code.

    To diagnose this further, we would have to see the error message and all the code. Also, when a VBA error occurs, the user gets a dialog box with an option to End or Debug. If you click Debug, it takes you to the code, and highlights in yellow the line of code where the error occurs. We would have to know what line that is.

    It will be much easier to understand your problem if you provide your file. This allows us to see and experiment with your data, layout, formulas, code, and possibly attach a file with a completed solution. Under the text box where you type your reply click the Go Advanced button. On the next screen scroll down and click on Manage Attachments, which will show a pop-up window to Select and Upload a file. Then close the window.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  4. #4
    Registered User
    Join Date
    06-21-2017
    Location
    USA
    MS-Off Ver
    2010
    Posts
    9

    Re: Strange errors I think coming from ConcanateIfs

    Quote Originally Posted by xlnitwit View Post
    Hi,

    I think you have posted the wrong function (you posted ConcatenateIf and not ConcatenateIfs) and you didn't say what error was displayed prior to the crash.

    Hey, thank you for the response.
    function is as series of =ConcatenateIfs

    the error that comes up is as follows:
    'Compile error:

    Can't find project or library'

  5. #5
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Strange errors I think coming from ConcatenateIfs [UDF]

    What references are checked (Tools- References in the VB Editor with that workbook open and active) for that project? I believe one of them will be labelled with MISSING: at the start.

  6. #6
    Registered User
    Join Date
    06-21-2017
    Location
    USA
    MS-Off Ver
    2010
    Posts
    9

    Re: Strange errors I think coming from ConcatenateIfs [UDF]

    Visual Basic for Applications, Microsoft Excel 14.0 object library, OLE Automation, Microsoft Office 14.0 Object Library, Microsoft Forms 2.0 Object library, Microsoft windows common controls-2.6.0 (SP6), Solver

    Looked through whole list, none with MISSING.

    Also, I have no problem posting file, however worried about confidentiality.

  7. #7
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Strange errors I think coming from ConcatenateIfs [UDF]

    I should have stated that you need to check the references on a computer where it doesn't work. I would suspect that the Windows Common controls reference is the problem- are you actually using any in your workbook?

  8. #8
    Registered User
    Join Date
    06-21-2017
    Location
    USA
    MS-Off Ver
    2010
    Posts
    9

    Re: Strange errors I think coming from ConcatenateIfs [UDF]

    Okay, thank you. I tried this work around and I believe it is failing due to multiple cell formats:

    =VLOOKUP($N$1,'Sheet 1'!$A$4:$F$500,MATCH(C25,'sheet 1!$C$4:$C$500,0),FALSE)

    Instead of giving me a text display, it is yielding numbers. This formula is intended to be displaying text comments so long date and title match.

  9. #9
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Strange errors I think coming from ConcatenateIfs [UDF]

    That formula doesn't make sense, I'm afraid. The third argument to VLOOKUP is the column number from which you want to return data. Given that your lookup range is 6 columns, the only valid inputs are the numbers 1-6 but you are using MATCH against almost 500 rows, so your return value could be anything from 1 to 497 (or an error).

    I would suggest that it is simpler to fix the code.

  10. #10
    Registered User
    Join Date
    06-21-2017
    Location
    USA
    MS-Off Ver
    2010
    Posts
    9

    Re: Strange errors I think coming from ConcanateIfs

    Hey, sorry for the delay time. I have been trying to come up with Index & Match/ VLOOKUP to return multiple values in same cell to no avail.

    Here is all code in file (error list will be under code tag):
    Module 1:
    Please Login or Register  to view this content.
    Module 2:
    Please Login or Register  to view this content.
    Error occurs at Module 1:
    Please Login or Register  to view this content.
    error that occurs is 'Compile error: Can't find project or library'

    The strange part is that this error only occurs on certain computers, yet all machines are running excel 2010, all have the same settings.
    Last edited by dixie12oz; 06-22-2017 at 02:13 PM. Reason: added information

  11. #11
    Registered User
    Join Date
    06-21-2017
    Location
    USA
    MS-Off Ver
    2010
    Posts
    9

    Re: Strange errors I think coming from ConcatenateIfs [UDF]

    Hey, Thank you for response. I have posted all of the code in file in this thread. If there is any other info you need please let me know. Any and all bread crumbs in right direction would be greatly appreciated.

  12. #12
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Strange errors I think coming from ConcatenateIfs [UDF]

    That is extremely peculiar. All function calls are fully qualified in that line so there should not be an issue with references. However, on a machine where the code fails are any references listed as missing?

  13. #13
    Registered User
    Join Date
    06-21-2017
    Location
    USA
    MS-Off Ver
    2010
    Posts
    9

    Re: Strange errors I think coming from ConcatenateIfs [UDF]

    I am checking this morning and will report back. An additional situation with this is that the form will be used in 9 geographic locations not by where I am as well as by the client. Can these references be checked before the form is received by them?

  14. #14
    Registered User
    Join Date
    06-21-2017
    Location
    USA
    MS-Off Ver
    2010
    Posts
    9

    Re: Strange errors I think coming from ConcatenateIfs [UDF]

    Sorry for the delay, the item starting with MISSING: on problematic machines is:

    IMAGEF8E847FD50A406.jpg

  15. #15
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Strange errors I think coming from ConcatenateIfs [UDF]

    As I suspected. Are you actually using any of those controls in the workbook?

  16. #16
    Registered User
    Join Date
    06-21-2017
    Location
    USA
    MS-Off Ver
    2010
    Posts
    9

    Re: Strange errors I think coming from ConcatenateIfs [UDF]

    These were the default settings that were selected when I created the sheet; no additional References were added by me. Are you aware of a fix to this issue?

+ 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. DataObject/Clipboard coming out with strange characters
    By quekbc in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-25-2015, 06:48 PM
  2. Extended a sumproduct formula that is coming up with errors
    By Beh162 in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 08-03-2015, 07:05 PM
  3. Concatenateifs
    By ZmeY in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-29-2015, 12:34 PM
  4. 2 strange errors?
    By Kosmosis in forum Excel General
    Replies: 8
    Last Post: 09-09-2010, 02:37 AM
  5. hiding the column...errors coming
    By ss_bb_24 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-01-2007, 07:30 AM
  6. Strange Errors in calculating Week-No
    By dvdung in forum Excel General
    Replies: 7
    Last Post: 09-11-2007, 03:56 AM
  7. strange error calculations provides errors until i add any new one
    By Subzizo in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-08-2005, 07:45 PM

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