# Find The Number

#### G-fer

##### Board Regular
Hi all ....

I have a problem that I can't seem to solve without asking for help.

I have a cell that contains a bunch of days of the month separated by commas, as in 1,3,7,8,9,13,15,16,17,18,19,21 etc, and the cell in question has a defined name, in this case "NotRon". These are the days of the month that Ron won't be available for work.

The formula below, used in a rostering routine, allows me to determine that Ron can work on the 6th. of the month.

=IF(NOT(ISNUMBER(FIND(","&6&",",","&NotRon&","))),"Yes","")

This is all pretty straight forward, but being a bit of a perfectionist I simply have to make it more difficult.

I would like to enter the data in the "NotRon" cell in the form 1,3,7-9,13,15-19,21 rather than have to enter each day individually (it's a space problem). Furthermore, if Ron isn't available at all that month, I just want to type "N/A".

Obviously the formula above will be able to locate 1,3,13 and 21 as unavailable days, but what about 7,8,9 15,16,17,18,19 and N/A?

Any ideas folks?

Regards ... G-fer

### Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

#### Peter_SSs

##### MrExcel MVP, Moderator
Perhaps you could use this Worksheet_Change code to place a full list of the unavailable days in the column next to where you have typed. This column could be hidden. The named ranges could then apply to this new column and your formula reference the new column as well.

Also, because the code places a comma at the start and end of the string in the new column, your formula could be shortened slightly to:

=IF(NOT(ISNUMBER(FIND(","&6&",",NotRon))),"Yes","")

My assumption is that the values are typed into column A. Adjust code if this is not correct. The new values will be put into the next column to the right. This could also be changed by altering the number in the "c.Offset" line near the end.

Note also that your input column may need to be formatted as text in caswe you type 4-6 and Excel may try to interpret this as a date.

Anyway, give it a try (backup your file first) and see what happens.

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#00007F">Dim</SPAN> NotAvail <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> myRange <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> fNo <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> lNo <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> Comma1 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> Comma2 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> Dash <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    <SPAN style="color:#00007F">Set</SPAN> myRange = Intersect(Target, Columns("A"))<br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> myRange <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> myRange<br>            <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> c.Value<br>                <SPAN style="color:#00007F">Case</SPAN> ""<br>                    NotAvail = ""<br>                <SPAN style="color:#00007F">Case</SPAN> "N/A"<br>                    <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> 31<br>                        NotAvail = NotAvail & "," & i<br>                    <SPAN style="color:#00007F">Next</SPAN> i<br>                        NotAvail = NotAvail & ","<br>                <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Else</SPAN><br>                    NotAvail = "," & c.Value & ","<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN><br>            <SPAN style="color:#00007F">Do</SPAN> <SPAN style="color:#00007F">While</SPAN> InStr(NotAvail, "-") <> 0<br>                Dash = InStr(NotAvail, "-")<br>                Comma1 = InStrRev(NotAvail, ",", Dash)<br>                Comma2 = InStr(Dash, NotAvail, ",")<br>                fNo = Mid(NotAvail, Comma1 + 1, Dash - Comma1 - 1)<br>                lNo = Mid(NotAvail, Dash + 1, Comma2 - Dash - 1)<br>                <SPAN style="color:#00007F">For</SPAN> i = lNo - 1 <SPAN style="color:#00007F">To</SPAN> fNo + 1 <SPAN style="color:#00007F">Step</SPAN> -1<br>                    NotAvail = Replace(NotAvail, "-", "-" & i & ",", , 1)<br>                <SPAN style="color:#00007F">Next</SPAN> i<br>                NotAvail = Replace(NotAvail, "-", ",", , 1)<br>            <SPAN style="color:#00007F">Loop</SPAN><br>            c.Offset(, 1).Value = NotAvail<br>        <SPAN style="color:#00007F">Next</SPAN> c<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

#### G-fer

##### Board Regular
Hi Peter ...

Thanks for the tips ... it's going to take me a little while to work this out, but I'll give it a whirl ...

Regards ... G-fer.

#### Peter_SSs

##### MrExcel MVP, Moderator
I meant to post a screenshot of a sheet that has had the code running.

Also for the N/A entry, it will put 1 to 31 even if the month has less than 31 days, but that shouldn't affect your subsequent formula results.

Excel Workbook
AB
11,3,7-9,13,15-19,21,1,3,7,8,9,13,15,16,17,18,19,21,
26,6,
34-6,4,5,6,
4N/A,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,
54,6-11,21-22, 26-30,4,6,7,8,9,10,11,21,22, 26,27,28,29,30,
Not Available

#### G-fer

##### Board Regular
Hi Peter ...

Thanks a million for the assistance.

It took me a while, but I eventually managed to get the whole thing working perfectly well. My main problems concerned the original layout of the worksheet, which I didn't really want to change. Anyhow, in the end it all works out, even though some of the code that I've had to invent is sort of clunky.

Once again, thanks for your help ...

Graham

#### pgc01

##### MrExcel MVP
Hi Graham

This is a different approach. You can use a UDF to determine whether the day is in the list.

Code:
``````Function IsAvailable(sList As String, sDay As String) as Boolean

IsAvailable = Not Intersect( _
Range(Mid(Replace(Replace("," & sList, ",", ",A"), "-", ":A"), 2)), _
Range("A" & sDay)) Is Nothing

End Function``````

Use it as any other worksheet function, Ex. in C1:

=IF(IsAvailable(\$A\$1,B1),"OK","N/A")
Copy down

#### G-fer

##### Board Regular
Hi Peter ...

Sorry, this one I simply do not understand ... I guess you CAN be too old to learn new tricks, but I do my best.

However, I get the feeling that this sort of routine could be used to validate the data that goes into A1, ensuring that only numbers 1 to 31, commas and dashes can be entered.

Would I be on the right track here/

Regards ... Graham.

#### al_b_cnu

##### Well-known Member
Hi,

Maybe you could adapt this UDF:
Code:
``````Option Explicit

Function GetAvailableNames(ByVal Day As Integer, Optional DefinedNamePrefix As String = "not") As String
Dim bAvailable As Boolean
Dim iNamesCount As Integer, iPtr As Integer, iPtr1 As Integer
Dim iFrom As Integer, iTo As Integer
Dim sAvailableNames As String
Dim sCurName As String, sCurAddress() As String
Dim sCurDays() As String, sCurDays1() As String
Dim rCur As Range
With ActiveWorkbook
For iNamesCount = 1 To .Names.Count
bAvailable = True
sCurName = .Names.Item(iNamesCount).Name
If LCase\$(Left\$(sCurName, Len(DefinedNamePrefix))) = LCase\$(DefinedNamePrefix) Then
sCurDays = Split(CStr(rCur.Value), ",")
If UBound(sCurDays) = 0 And Val(sCurDays(0)) = 0 Then
bAvailable = False
Else
For iPtr = 0 To UBound(sCurDays)
If Len(sCurDays(iPtr)) <> 0 Then
sCurDays1 = Split(sCurDays(iPtr), "-")
ReDim Preserve sCurDays1(0 To 1)
Else
ReDim sCurDays(0 To 1)
End If

If Val(sCurDays1(0)) > Val(sCurDays1(1)) Then sCurDays1(1) = sCurDays1(0)
For iPtr1 = Val(sCurDays1(0)) To Val(sCurDays1(1))
If iPtr1 = Day Then
bAvailable = False
Exit For
End If
Next iPtr1
If bAvailable = False Then Exit For
Next iPtr
End If
If bAvailable Then sAvailableNames = sAvailableNames & ", " & Mid\$(sCurName, Len(DefinedNamePrefix) + 1)
End If

Next iNamesCount
End With
If Len(sAvailableNames) <> 0 Then sAvailableNames = Mid\$(sAvailableNames, 3)
GetAvailableNames = sAvailableNames
End Function``````

... continued

#### al_b_cnu

##### Well-known Member
continued ...
Book1
ABCD
1DayAvailable to work
21,3,5,7,9,11,13,15,176Ron
32,4,6,8,10-12,13
4#N/A
Sheet1

The above s/sheet has the defined names 'NotRon', 'NotBill' and 'NotFred' in cells A2, A3 & A4 respectively. The code looks for defined names prefixed 'Not'

The UDF call in D2 is =GetAvailableNames(C2)

Replies
3
Views
312
Replies
2
Views
134
Replies
2
Views
201
Replies
3
Views
911
Replies
2
Views
812

1,190,783
Messages
5,982,896
Members
439,805
Latest member
IDarkstarX

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back