Gentlemen;
Thanks for your quick response.
1.- What is the Outcome?
The outcome is to get in another sheet the list of values repeated and
missing which but the way I have the program (see below) but just take
values without text.
2.- How many rows do you want to skip on to the next column?
I want to skip to the next column as soon as the number has text (i.e. 2a,
2b, etc)
Then create a column with the numbers which have the text â€aâ€. Skip again to
the next column.
Then create a column with the numbers which have the text “bâ€. Skip again to
the next column.
Then create a column with the numbers which have the text “câ€. Skip again to
the next column.
Then create a column with the numbers which have the text “dâ€. Skip again to
the next column and so on.
The reason I want to this is because I will copy each new column in
different sheets then split them to get just the number and run the program
to get the repeated and missing numbers. Otherwise if you have another way to
advice me to do it I will really appreciate.
Kind regards.
Maperalia
‘****START PROGRAM****************************
Sub FindMissingAndDuplicates()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim v() As Long
Dim missing() As Long
Dim i As Long
Dim lastrow As Long
'*****Find the Minimum and Maximum Number*********
sblock = Application.InputBox("Enter block start")
fblock = Application.InputBox("Enter block end")
'*************************************************
ReDim v(fblock - sblock + 1)
j = 0
For i = sblock To fblock
v(j) = i
j = j + 1
Next i
'****Read the Numbers on the Test Numbers Sheet********
Set ws1 = Worksheets("Test Numbers")
'******************************************************
'****Write the Missed and Duplicated Number on the Missing and Duplicated
Numbers Sheet********
Set ws2 = Worksheets("Missing and Duplicated Numbers")
ws2.Range("a1:b1") = Array("Missing", "Duplicated")
'**********************************************************************************************
With ws1
lastrow = .Cells(Rows.Count, 1).End(xlUp).Row
Set rng = .Range("a1:a" & lastrow)
End With
n1 = 2
n2 = 2
For i = LBound(v) To UBound(v)
If IsError(Application.Match(v(i), rng, 0)) Then
ws2.Cells(n1, 1) = v(i)
n1 = n1 + 1
Else
If Application.CountIf(rng, v(i)) > 1 Then
ws2.Cells(n2, 2) = v(i)
n2 = n2 + 1
End If
End If
Next i
End Sub
‘****END PROGRAM****************************