Posts by Justin Doward

    Hi PR,

    I think I can safely say that your requirement is not clear. I cannot workout whether you intend to have an array in the cell (eg separated by a comma or other) or you mean you want to compare the value in column L against an array and if it matches then put PC in column O.

    The first objective could be achieved using a simple vlookup formula against a table of values.

    You should make your sheet representative of what you are working with, and include a before and after example.



    To answer the posed question, I think this solves the issue:

    1. Sub CaptionRead()
    2. Dim ufs As Object
    3. On Error Resume Next
    4. For Each ufs In ThisWorkbook.VBProject.VBComponents
    5. Debug.Print "Userform name = " & ufs.Name & ", Caption = " & ThisWorkbook.VBProject.VBComponents(ufs.Name).Properties("Caption")
    6. Next
    7. End Sub

    Sorry for the previous post, it was in the AM my time and my eyes were a little crooked. Feel free to remove it.

    To get the form names, control names and caption names output to a sheet try this:

    The on error resume next is needed because the ctrl.caption will fail on controls that do not have a caption, there is probably a better way to catch it.

    You also need to ensure the trust center setting has the "TRUST access to the VBA project object model" ticked.


    This seems to work.

    Hi pvman,

    This leaves a trailing comma to deal with, I will have another look later so let me know how you go.


    Sorry TH, that code works for me to catch the error of a formula returning an error in a cell due to a divide by zero. It would probably result in a relatively quick solution if you could post an example sheet with the values and the code working as it does on your copy. Without being able to replicate the error you have it is difficult to trouble shoot.

    If you cannot provide an example sheet I suggest you request this thread be deleted and start one specifically requesting a solution to error catching a cell returning a divide by zero error. I.e. re-title your thread and be more specific in your requirement.

    Maybe one of the mods can provide better insight.

    It is a bit difficult to work out without being able to reproduce the error.

    I think this code replicates yours but should run a little faster.

    to catch the error occurring in a cell use the iferror function, something along the lines of:

    1. If Application.WorksheetFunction.IfError(Target, True) = True Then i = i + 0.1

    where you replace "Target" with the cell with the error (eg ws.range("A1") etc.)

    Again without being able to test your sheet or an example it is difficult to work out.

    HTH, good luck

    Is the intent to find out if x(i, 5) = the number of numeric in the chosen column?

    It is easy enough, but difficult from you question to ensure that is what you are after.

    if so you would do something like replace:

    1. Const Status As String = "UW"


    1. dim UW as long
    2. UW = Application.WorksheetFunction.Count(Sheet1.Range("B1:B10"))

    Try this in the sheet code.

    It would be a good idea to remove the conditional formatting from your posted example as it causes confusion.

    You can try this macro linked to a button on the page, I think it is working correctly but let me know if it is off anywhere.

    Hi SL,

    Pretty sure that the array formulas are considered native, the problem with your workbook is having around 10000 formulae referencing a few hundred thousand cells so the calculation update takes some time... I imagine it is more than irritating to work with. If you have not already done so turn your calculation to manual and only update (using calculate sheet) when you have made all the required changes and things should run more smoothly. A VBA solution with a button press should be quicker I think, but Roy is probably correct that a pivot table or filter would probably suit your needs better.

    For example, would this do:

    it is a pivot table created with signoff date and training in rows, employee in column and count of signoff date in values. (NB: if you get the date split into months etc, right click on it and select "ungroup")


    What version of excel are you running, and how old is the computer system? Maybe if it is a 32 bit version of excel? That is a big jump in time for a not particularly large jump in size (about a 25% increase considering you had 9 extra columns). so you would expect it to be closer to 20 seconds. Anyway, that is the trouble with humungous data sets. You could try your hand at R.

    HI Trunten,

    I tried your code on the large data set and it returned an out of memory error, I was getting the same when loading the entire sheet into an array so I cut the array into pieces. when I cut it down to 90,000 rows per sheet it ran but I cancelled after about 20 seconds. The highlight is colouring numerous cells which are the same on both sheets ( I only included a couple of differences throughout, but thousands of cells are being highlighted).

    I obtained some large data sets from Here then copied and pasted them a few times to get into the ridiculous range.



    Hi revanth,

    I tested this code comparing two sheets of data each with 41 columns and 520,000 rows, it took 12.29 seconds to complete. It does not do everything you are after yet but if it you think it is in the ballpark of what you are after let me know.

    Currently it finds the first difference in a row then highlights the entire row and moves to the next row.

    Hi FE,

    No you cannot have two functions with the same name, that is causing the ambiguous name error. Just combine the code in the one function or call a second function with a different name from the first.

    That is my understanding anyway.


    Not entirely sure what your question is max? R is the row, C is the column so R1C1 is A1, R2C1 is A2, R1C2 is B1 etc...

    what do you need to adapt? the code works such that if you put another line in your data, then run the code, it will update the chart reference to include the new line. It is possible to do this using formula, so I am not sure why the VBA is required.