1. Registered User
Join Date
May 2014
Posts
1

Hi guy's

i have the following working vba code below, it's listing the missing numbers in a sequence in a new column, but i don't now how the code is working

i don't understand the for loops and the scripting.dictionary , is someone who can help me?

Kind regards,

Tom

Code:
```Const rng As String = "\$a\$2:\$a\$218"
Dim d As Object, a, c()
Dim i As Long, mx As Long, mn As Long

Set d = CreateObject("scripting.dictionary")
a = Range(rng)

mx = Application.Max(a): mn = Application.Min(a)

ReDim c(1 To mx - mn + 1, 1 To 1)

For i = 1 To UBound(a): d(a(i, 1)) = 1: Next i

For i = mn To mx
If d(i) <> 1 Then k = k + 1: c(k, 1) = i
Next i```

Join Date
Nov 2004
Location
out on a limb
Posts
13,692

3. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445
Originally Posted by tj.waarsenburg
Code:
```Const rng As String = "\$a\$2:\$a\$218"
Dim d As Object, a, c()
Dim i As Long, mx As Long, mn As Long

Set d = CreateObject("scripting.dictionary")

' Create a variable that references the data in cell range \$a\$2:\$a\$218
a = Range(rng)

Work out how big this range is (minimum and maximum boundaries)
mx = Application.Max(a)
mn = Application.Min(a)

Now we know our min and max "reset" our array variable to the appropriate size
ReDim c(1 To mx - mn + 1, 1 To 1)

Loop over our new array
For i = 1 To UBound(a)
Umm?
d(a(i, 1)) = 1
Next i

Then loop from min to max
For i = mn To mx
If d(i) <> 1 Then
Increment k
k = k + 1
End If
Umm?
c(k, 1) = i
Next i```
This is why you should always use proper variable names. How bloody confusing is that code!

Note I've added in a few extra line breaks for readability. What is gained by leaving them out?

Short code <> good code.

Good code is easy to read, easy to maintain and commented where appropriate.

4. Registered User
Join Date
Apr 2014
Location
Kentucky
Posts
585