Forum Discussion
Nibmus
Aug 06, 2022Copper Contributor
How to have a list of numbers that skip when it gets to certain values?
I want to make a list of numbers. The thing is, some slots have already been filled by other numbers using a formula. 10 4 3 ...
OliverScheurich
Aug 06, 2022Gold Contributor
Sub fill()
Dim start_variant As Variant
Dim bereich As Range
Dim result_variant As Variant
Dim i As Long
Dim k As Long
Dim number As Long
Dim rows_result_variant As Long
Range("C:C").Clear
Set bereich = Range("A1:A7500")
start_variant = bereich
rows_result_variant = Application.WorksheetFunction.CountA(bereich)
ReDim Preserve start_variant(1 To 7500, 1 To 1)
ReDim result_variant(1 To rows_result_variant, 1)
k = 1
For i = 1 To 7500
If Cells(i, 1) <> "" Then
result_variant(k, 1) = Cells(i, 1).Value
k = k + 1
Else
End If
Next i
number = 1
For i = 1 To 7500
If Cells(i, 1) = "" Then
For k = 1 To rows_result_variant
If result_variant(k, 1) = number Then
number = number + 1
k = 0
Else
End If
Next k
start_variant(i, 1) = number
number = number + 1
Else
End If
Next i
Range("C1:C7500") = start_variant
End SubMaybe with this code. In the attached file you can enter unique numbers in range A1:A7500. Then you can click the button in cell E2 to run the code.