Tried a few times but suffered a few errors, I get what you mean I think, If the Len is 11 add 10 and 11 together?
I had to remove the parentheses at the end as the code was red for error and amend IIF to capitals as it did not like it, the code now runs but throws up an error as 3075
Code:
Dim I As Integer
Dim coltolookup As String
For I = 1 To Len(Code)
Select Case I
Case 1
coltolookup = "Code"
Case 2
coltolookup = "HourC"
Case 3
coltolookup = "Date1"
Case 4
coltolookup = "Date2"
Case 5
coltolookup = "Company"
Case 6
coltolookup = "Min1"
Case 7
coltolookup = "Min2"
Case 8
coltolookup = "MonthC"
Case 9
coltolookup = "YearC"
Case 10
coltolookup = "SiteName"
Case 11
coltolookup = "Site2" '''Need to lose this and combine 10 and 11 together as 1 digit
End Select
Me("Var" & I) = DLookup(coltolookup, "tblCodeCracker", "Code ='" & Mid(Code, I, IIf(I < 11 Or Len(Code) = 10, 1, 2))) & "'"
Debug.Print coltolookup
Next I
Attachment 27999