show numbers not used

[dgraham62]

Hi,

I have a list of dynamic numbers in "sheet2" in column a,b,c,d,e. I will enter most of these numbers in "sheet1" and would like the remaining numbers automaticly entered into cells located on "sheet1" as well. Any ideas?

Regards

David

[Bob Phillips]

Do you want some mop up code to check which are not used and list those?

Will the numbers be mapped regarding cell positions or could they be anywhere?

dO you want them list in one column, or map the positions of the original?

As you can see from my questions, an example would be helpful.

[dgraham62]

Hi XLD,

Thanks for your quick response. I have attached the workbook.

The dynamic numbers are in "buses" worksheet.

I will be entering these numbers into cells in "Shed" worksheet in the blue cells and would like the remaining numbers that have not been used put into the yellow cells in range A23 to B36.

Hope this is clear enough.

[dgraham62]

sorry didn't check the file size

[Bob Phillips]

[vba]

Public Sub Test()
Dim aryNums
Dim i As Long
Dim cell As Range
Dim target As Range

With Worksheets("Buses")

ReDim aryNums(1 To .UsedRange.Cells.Count)

For Each cell In .UsedRange.Cells

If IsNumeric(cell.Value) Then

Set target = Nothing
'On Error Resume Next
Set target = Worksheets("Shed").Cells.Find(cell.Value)
On Error GoTo 0
If target Is Nothing Then

i = i + 1
aryNums(i) = cell.Value
End If
End If
Next cell
ReDim Preserve aryNums(1 To i)
Worksheets("Shed").Range("A23").End(xlDown).ClearContents
Worksheets("Shed").Range("A23").Resize(i) = Application.Transpose(aryNums)
End With
[/vba]

[dgraham62]

thanks xld,

I sort of works now, but not quite. it puts the numbers only in column A instead of A23:B36. also if the code runs more than once it comes up with an error. otherwise it's what I was looking to do.

Regards

David