Working with a range

Can someone suggest visual basic code for a macro to check for the occurance
of a single digit (1-9) in a range such as A1:C3. Each cell in the range
must contain a digit once, ie. no repeats, and no blanks.

Dan

Have a look in VBA help for Find,then findnext

--
Don Guillett
SalesAid Software
[email protected]
"Dan Chupinsky" <[email protected]> wrote in message
news:[email protected]...
> Can someone suggest visual basic code for a macro to check for the
occurance
> of a single digit (1-9) in a range such as A1:C3. Each cell in the range
> must contain a digit once, ie. no repeats, and no blanks.
>
> Dan
>
>

You could use a worksheet function that counts unique values in that range:

=SUMPRODUCT((A1:C3<>"")/COUNTIF(A1:C3,A1:C3&""))

You can use the equivalent worksheet function in code like:

Option Explicit
Sub testme()

Dim myRng As Range
Dim wks As Worksheet
Dim UniqueCount As String

Set wks = Worksheets("Sheet1")
Set myRng = wks.Range("a1:c3")

UniqueCount = Application.Evaluate _
("SUMPRODUCT((" & myRng.Address(external:=True) _
& "<>"""")/COUNTIF(" & myRng.Address(external:=True) _
& "," & myRng.Address(external:=True) & "&""""))")

If UniqueCount <> 9 Then
MsgBox UniqueCount
Else
MsgBox "All there!"
End If

End Sub

This previous routine will look for 9 distinct entries. It won't care if you
type 1.5, 2.5, 3.3, ASDF, etc as long as you have 9 of them.

If you want to check to see if each of the numbers 1-9 are there, you could be
more careful with something like this:

Sub testme2()

Dim myRng As Range
Dim wks As Worksheet
Dim iCtr As Long
Dim AllThere As Boolean

Set wks = Worksheets("Sheet1")
Set myRng = wks.Range("a1:c3")

AllThere = True
For iCtr = 1 To 9
If Application.CountIf(myRng, iCtr) <> 1 Then
AllThere = False
Exit For
End If
Next iCtr

If AllThere = True Then
MsgBox "all there"
Else
MsgBox "not all there"
End If

End Sub

Dan Chupinsky wrote:
>
> Can someone suggest visual basic code for a macro to check for the occurance
> of a single digit (1-9) in a range such as A1:C3. Each cell in the range
> must contain a digit once, ie. no repeats, and no blanks.
>
> Dan

--

Dave Peterson

Thanks to Don who gave me direction that I can use elsewhere in my project
and to Dave who gave me the exact code [testme2()] that I wanted to create.

This forum is terrific.

Dan
"Dan Chupinsky" <[email protected]> wrote in message
news:[email protected]...
> Can someone suggest visual basic code for a macro to check for the
> occurance of a single digit (1-9) in a range such as A1:C3. Each cell in
> the range must contain a digit once, ie. no repeats, and no blanks.
>
> Dan
>