Sub TestSub()
Dim a As Range, x As Range
Application.ScreenUpdating = False
For i = 1 To Range("A65536").End(xlUp).Row
If Application.WorksheetFunction.CountIf(Range("B:B"), Range("A" & i)) = 0 Then
Range("D" & 1 + Range("D65536").End(xlUp).Row) = Range("A" & i)
End If
Next i
Set a = Range("D1", Range("D65536").End(xlUp))
Set x = Range("D65536").End(xlUp)(3, 1)
x.Offset(-1).Value = "Total"
x.Value = WorksheetFunction.Sum(a)
Application.ScreenUpdating = True
End Sub
daveyy said:I have a column of data (numbers) from this colum I want to count the numbers that do not appear in the second column. I then need to sum the value in the adjecent column.
...
Book4 | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | |||
1 | NetAmount | fee | GrossRefund | NLC | #NotInE | 2 | ||||
2 | 11 | 14.3 | 12 | 1749 | 1749 | SumNetAmount | 44.2 | |||
3 | 22.2 | 22.6 | 15.9 | 2222 | 1752 | SumFee | 35.5 | |||
4 | 25.1 | 11.4 | 13.4 | 4701 | 4701 | SumGrossRefund | 36.2 | |||
5 | 22 | 12.9 | 20.3 | 1746 | 4807 | |||||
6 | 3660 | |||||||||
7 | 8187 | |||||||||
8 | 1751 | |||||||||
9 | 8856 | |||||||||
10 | 8881 | |||||||||
11 | 1750 | |||||||||
12 | 8176 | |||||||||
13 | 4808 | |||||||||
14 | 3659 | |||||||||
Sheet1 |
Sub test()
Dim a, lastR As Long, lastR2 As Long, x, i
Dim dic As Object
Set dic = CreateObject("scripting.dictionary")
With ActiveSheet
.Range("f2:f65536").Clear
lastR = .Range("d65536").End(xlUp).Row
lastR2 = .Range("e65536").End(xlUp).Row
ReDim a(1 To lastR2 - 1, 1 To 5)
a = .Range("a2:e" & lastR2).Value
For Each x In .Range("d2:d" & lastR)
dic.Add x.Value, Nothing
Next
For i = LBound(a) To UBound(a)
If Not IsEmpty(a(i, 5)) And Not dic.exists(a(i, 5)) Then
.Cells(i + 1, "f") = a(i, 1) + a(i, 2) + a(i, 3)
End If
Next
End With
Set dic = Nothing
Erase a
End Sub
daveyy said:Many thanks for this further help.
Your solution Aladin work just fine as long as I dont add further data to columns A, B, C & D. If I extent the formula it will count empty cells. The user will need to add data to these columns as they use the workbook and totals will need to be calculated as further data is entered.
This is a great help and has certainly improved on the situation I was in.
Many thanks again.
Dave