Posts by jl2509

    Hi Carim,


    No worries, I managed to figure it out.
    The range I had set wasnt dynamic so when it changed form the sample range, things were no reported properly. I set a new named range using offset funtion and referenced that.


    Works a treat with this tweak.


    Thanks for the response though.

    Hi Carim


    Once again, many thanks and great work.
    The macro works great right up to the part where I replace the range data from text to using an array formula.
    I assume this is because there is no value in the cell where the array formula is?


    Is ther a way to have the macro view the "Values" in the range rather than the array formula?
    or
    should I just copy and paste the values from the range to the next column (Hidden) using "Selection.PasteSpecial Paste:=xlPasteValues" and reference this new range C3:C14 instead?

    Thanks

    Hi All


    Any help much appreciated here.
    Using VBA, how can I check a range to see if a specific string exist and error if not


    Tried several options myself but cannot tie it down


    i.e.


    Sheet1
    Range B3:B14


    searchvalue= "SampleText"
    msgbox="Error not found"


    Thanks

    Hi Carim


    I figured it out and thats exactly what I added.
    A bit of stumbling but got here.


    Nice to have the follow up from you guys though.


    Thanks

    Hi Robert


    Just one thing, is there a way to say what is not acheived in the message box


    i.e


    "Group A requirements have not been met, selections must be 10 or more"


    or


    "Group B or Broup C requirements have not been met, selections from either one of these Groups must be more than 1"


    Thanks

    Hi Carim,


    Well, if its not too much trouble, I would like to see both options VBA nd UDF so I can maybe learn from them


    Thanks

    Hi All,


    On my worksheet, Range A2:A50 has data then a group number A, B, or C in column B
    using vba, how can I lookup the range A2:A50 to ensure A is shown a minimum of 10 times, and either B or C is shown a minmum of once.


    A message box to say "Selections Complete" if the criteria is met, or "Selections do not meeet requirements" if not.


    Thanks

    Hi Carim


    Many thanks once again, I did not try an array formula.


    Is there a way this could be carried out in a module?


    Thanks

    No worries, I appreciate the help.


    I did have a go with something along the lines of the following to no avail...


    Code
    [align=left][COLOR=#252C2F][FONT=Courier][SIZE=12px]If WorksheetFunction.CountA(r1) = Sheets("Sheet1").Range("C1").Count Then[/SIZE][/FONT][/COLOR][/align]


    Thanks

    Hi


    all options on the modules sheet are the same.
    Sheet 1 will vary from (8) to (n)


    My thought process was to have the 2 values (CountA of column A) checked against the input in cell C1


    So, CountA column A = 14 and C1=15 - "this would throw an error"


    Thanks

    Hi, I thought I would try to make this a liitle more clear.


    Rather than set a validation source range: A2:A12. is it possible to count the number of exams input from A2 downwards then check the cell C2 for an input value i.e 14 and match that to the count of range A2:A?


    If they do not match "Error" or "Valid"if they do match. Once this is completed, move into the code you have already provided to check the validty of selections.


    Thanks

    Hi


    I understand and thank you for the explanation.


    Is it possible to have an input cell, say (C1) which would be the number of exams required (8-15), then make the range (A2:A17) dynamic, so that the code initialy only checks to see if the number of exams input matches the value in cell C1 before checking for the criteria match?


    Otherwise this piece of work is perfect.

    Hi and again, thanks you for the help and response.


    The current range only covers 12 exams as per my first request.
    Further thinking shows a flaw in my request as there may be times when only 6 exams may be needed or upto 15


    I wondered if this could be coded in as a fallback just in case.


    Thanks

    Hi


    I have tested out the proposal in depth and everything seems to works as expected.
    Great work.


    Looking at the code, the exams selected range is fixed. As there may be variations in total exam numbers selected, is it possible to have a pop up box "OnSheetactivate" asking for input of total exams to choose?
    Also, can a message be displayed if there are no errors found? "All selections are valid"
    and finally, can there be a space line between each error in the message box just for clarrity?


    Awsome work on this and very much appreicated.


    Many Many Thanks