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

Many thanks Robert

Great work

Hi Carim

Thats great and as you say, VBA would not hold value.
The UDF is perfect.

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

Hi All,

Any thoughts on the attached sample file.
I basically need an index macth to return the group number from a different worksheet.

I hope I have explained this sufficiently in the worksheet
Thanks

Hi And thank you for all your help.
I have enough here I think and must stop perstering you.

Your have been most informing with your proposals.

Many thanks

No worries, I appreciate the help.

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

[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 and thank you for the repsonse.

I should have closed this as I found a differet approach to a solution.

Sorry if I have wasted any of your time.
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

Hi StephenR

I have just downloaded the sheet and will go through its usage to check all parameters work as expected.

Many thanks for you efforts so far and I will feedback tomorrow.