Remaining Numbers from a List that are not used
 

Say I have in the following cells

A1 134
A2 567
A3 368

I want C5 to show the remaining numbers from the list of
1,2,3,4,5,6,7,8,9
i.e. 2, 9

This required over approx 25 groups of cells in a sheet


Anyone done this before?

I am not good at VBA, but can manage most Excel functions happily

Lorence

Remaining Numbers from a List that are not used
 

Lorence,

Try:
Sub Mytest()
Dim L As String, TestST As String
Dim X, Y
TestST = "123456789"
L = Range("A1").Text & Range("A2").Text & Range("A3").Text
For X = 1 To 9
Y = 1
Do

If Mid(L, X, 1) = Mid(TestST, Y, 1) Then _
TestST = Left(TestST, Y - 1) & Right(TestST, Len(TestST) - Y)
Y = Y + 1
Loop Until Y Len(TestST)
Next X
Range("C5").Value = TestST
End Sub


Henry


"Lorence" wrote in message
...
Say I have in the following cells

A1 134
A2 567
A3 368

I want C5 to show the remaining numbers from the list of
1,2,3,4,5,6,7,8,9
i.e. 2, 9

This required over approx 25 groups of cells in a sheet


Anyone done this before?

I am not good at VBA, but can manage most Excel functions happily

Lorence

Remaining Numbers from a List that are not used
 

On Tue, 14 Jun 2005 00:35:56 GMT, "Henry"
wrote:

Lorence,

Try:
Sub Mytest()
Dim L As String, TestST As String
Dim X, Y
TestST = "123456789"
L = Range("A1").Text & Range("A2").Text & Range("A3").Text
For X = 1 To 9
Y = 1
Do

If Mid(L, X, 1) = Mid(TestST, Y, 1) Then _
TestST = Left(TestST, Y - 1) & Right(TestST, Len(TestST) - Y)
Y = Y + 1
Loop Until Y Len(TestST)
Next X
Range("C5").Value = TestST
End Sub


Henry


Works brilliantly - thks

Lorence