MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Scanning through a Range

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

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?



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.