Search:

# Scanning through a Range

Asked By: Daryl    Date: Feb 16    Category: MS Office    Views: 4462

I am trying to build up a function in VBA This is what I want to do:

I have a range from A1:A10 and I have whole numbers in each cell.I want to
know if any number is repeated in that given range.If yes then give me that
repeated number.Can anyone help me on this?

Share:

### 2 Answers Found

Answer #1    Answered By: Allison Stanley     Answered On: Feb 16

Sounds like homework. What have you written so far?

Answer #2    Answered By: Xander Thompson     Answered On: Feb 16

If you're trying to write a function, then you must be expecting
to use the function  either in a Subroutine, (or another Function) or
in a cell...

either way, Excel already has a function that does what you're
Let's say you have:
A1 = 9
A2 = 13
A3 = 30
A4 = 89
A5 = 16
A6 = 58
A7 = 9
A8 = 63
A9 = 58
A10 = 12

in B1, you can put: =IF(COUNTIF(\$A\$1:\$A\$10,A1)>1,A1,"")
The countif will look in the range  A1-A10 for a match to A1,
if more than one match is found, it will display the value of A1.
you can then copy this formula...

if you're trying to use it in a function or Subroutine, you can use
something like:

Sub Check_Dup()
Dim I
For I = 1 To 10
If (Application.WorksheetFunction.CountIf(Range("A1:A10"),
Cells(I, 1)) > 1) Then
MsgBox Cells(I, 1) & " is Duplicated"
End If
Next I
End Sub

Didn't find what you were looking for? Find more on Scanning through a Range Or get search suggestion and latest updates.