Hello,
I’m working on a storage problem.
I am looking to automatically count the number of different locations where equipment is stored.
Example: A bicycle factory stores the bicycle elements in different racks
Bike 1 stored at location A
Bike 1 stored at location A
Bike 1 stored at location B
Bike 1 stored at location C
Bike 2 stored at location A
Bike 2 stored at location D
Bike 2 stored at location D
The code should therefore return "3" for the number of different locations occupied by bike 1, and return "2" for bike 2.
I tried this :
Sub test2()
Dim Val1 As String
Dim Val2 As String
Dim Nb As Integer
Dim a As Variant
Dim i As Variant
Nb = 1
a = i - Nb
'Comparison of two successive cells to verify that the same type of bicycle is being treated'
Range("A1").Select
For i = 1 To Range("A65536").End(xlUp).Row
Val1 = ActiveCell.Value
Val2 = Selection.Offset(1, 0).Value
If Val1 = Val2 Then
Nb = Nb + 1
Selection.Offset(1, 0).Select
i = i + 1
Else
'ActiveCell = Nb'
Selection.Offset(-Nb, 2).Select
'To count different racks number'
' ActiveCell.FormulaR1C1 = _
"=SUMPRODUCT(1/COUNTIF(R[0]C[-1]:R[& Nb &]C[-1],R[0]C[-1]:R[& Nb &]C[-1]))"'
ActiveCell.FormulaR1C1 = _
"=SUMPRODUCT(1/COUNTIF(B" & a & ":B" & i & ",B" & a & ":B" & i & "))"
Selection.Offset(Nb + 1, -2).Select
End If
Nb = 1
Next
End Sub
Would you like to help me please?
