Hi Ajax,
Thanks for the recent help, by messing about myself with Mid, Len, Trim and Case functions on this project I have learned quite a lot more about VBA - Many Thanks!
Since the last update, I have got the code to run again without generating an error.
Removed the extra Parentheses after 10,1,2 and added to the end of the code (As below)
Although the code runs and fills out the boxes of the frmCodeVariables Var Textboxes (See screenshot)
A single digit is fine but a double integer on the end of the string it ignores the second digit, IE 14 = Enfield, It throws up Bolton (Taking the 1 only)
Sorry to be a pain, can you advise what is going wrong please?
Is it my Lookup table structure maybe? Should I lose the Case 11 Var maybe?
Tried changing a few things around but still no joy
Once again thanks for the help so far which has been brilliant!
Code:Private Sub Decode_Click() 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 End Sub
Attachment 28005
Code Machine HourC Date1 Date2 Company Min1 Min2 MonthC YearC Site1 Site2 SiteName 1
W
1
Bolton 10 2
W
1 0
11 3
W
1 1
12 4
W
1 2 Variety 13 5
W
1 3 Wednesbury 14 6
W
1 4 Enfield 15 7
W
1 5 Tuscany 16 8
W
1 6 Stockton 17 9
W
1 7
18
W
1 8 Bristol 19
W
1 9 Gluten Free 2
W
20
W
3
W
3
Burnley 4
W
5
W
6
W
6
Newburn 7
W
7
Bellshill 8
W
8
Eastwood 9
W
A
0
0
1 2015
B
1
1
2 2016
C
2
2
3 2017
D
3
3
4 2018
E
4
4
5 2019
F
5
5
6 2020
G
6
0 7 2021
H
7
1 8 2022
I
8
2 9 2023
J
9
3 10 2024
K
10
4 11 2025
L
11
5 12 2026
M
12
9
6
2027
N
13
8
7
2028
O
14
7
8
2029
P
15
6
9
2030
Q
16
5
2031
R
17
4
2032
S
18
3
2033
T
19
2
2034
U
20
1
2035
V
21
0
2036
W
22 3
W
2037
X
23 2
2038
Y
24 1
2039
Z
0
2040