VB Scrip
- Thread starter bloubul
- Start date
Frank
Member
Bloubul, you have specifically asked for a vb script to use on an Excel sheet. I'm wondering why you havent asked for formulas to do the same job? The golden rule is never use a script when Excels functions can do it anyway. This is a classic example of using VLOOKUP to get the vtracs for each digit.
You do not specify where your data is, nor do you specify whether it is a 3 digit number in each single cell, or whether each of the three digits is in 3 separate cells ???
Well here is a lesson on how to do it yourself, you can do this on a new blank sheet first to learn it. If you need it on an existing sheet, it is easily portable once you know what is going on.
I do not do vtracs and in the UK we dont have your type of pick 3 game, so I had to read v-tracs for dummies to get the vtracs for pick 3.
Assuming your pick 3 number is 3 digits in a SINGLE cell..
1. on a blank spreadsheet ...create a 2 column vtrac table ( 0 to 9) in cells I3:j12
I3 has zero in it, I4 has 1 in it, I5 has 2 in it ............>>> down to I12 has 9 in it. The second column V3:V12 has 1,2,3,4,5,1,2,3,4,5 respectively in each cell, so j3 has a 1 in it, ....>....>.... J12 has a 5 in it.
2. Select the table and whilst selected Type a name in the NAME BOX top left corner, name it vtracTable press <enter>. ( or you can use the Name Manager to do the same thing).
3. enter any 3 digit number 0 to 999 in cell A3
(splitting the digits into 3 cells and converting...)
first digit
4. in cell B3 enter the formula =VLOOKUP(INT(A3/100),vtracTable,2,1) press enter. this gives your first vtrac digit.
second digit
5. In cell C3 enter the formula =VLOOKUP(INT(MOD(A3,100)/10),vtracTable,2,1) press enter. this gives your second vtrac digit.
third digit
6. In cell D3 enter the formula =VLOOKUP(MOD(A3,10),vtracTable,2,1)
You now have the 3 digit vtrac for your first number in cells B3 to D3.
Combining the vtracs into a single cell
7. In cell F3 enter the formula =B3*100+C3*10+D3
you now have your first vtrac as a 3 digit number in a single cell.
8. if you want to be slick and avoid splitting the digits as in 4,5,6 above you can bypass that and go direct to the the 3 digit vtrac in a single cell.
e.g in cell G3 enter the formula =100*VLOOKUP(INT(A3/100),vtracTable,2,1)+10*VLOOKUP(INT(MOD(A3,100)/10),vtracTable,2,1)+VLOOKUP(MOD(A3,10),vtracTable,2,1). You now do not need the values in B3 to D3.
If you copy formulas B3:G3 down the spreadsheet, then 3 digit numbers entered in column A will be converted to vtracs .
OR If your original 3 digit result numbers were separated into 3 cells A25,B25,C25
e.g
and you want a 3 digit conversion ,with the answer in a single cell, then in cell F25 enter the formula =VLOOKUP(A25,vtracTable,2,1)*100+VLOOKUP(B25,vtracTable,2,1)*10+VLOOKUP(C25,vtracTable,2,1)
Again you can copy this fromula down the sheet.
Note the vtrac loookup table can be anywhere you like once it has been named.
As usual this took a lot longer to describe than it takes to actually do it! Have a go - its more versatile than a macro, which needs to know where everything is, how much there is, where you want to put it etc. etc.
You do not specify where your data is, nor do you specify whether it is a 3 digit number in each single cell, or whether each of the three digits is in 3 separate cells ???
Well here is a lesson on how to do it yourself, you can do this on a new blank sheet first to learn it. If you need it on an existing sheet, it is easily portable once you know what is going on.
I do not do vtracs and in the UK we dont have your type of pick 3 game, so I had to read v-tracs for dummies to get the vtracs for pick 3.
Assuming your pick 3 number is 3 digits in a SINGLE cell..
1. on a blank spreadsheet ...create a 2 column vtrac table ( 0 to 9) in cells I3:j12
I3 has zero in it, I4 has 1 in it, I5 has 2 in it ............>>> down to I12 has 9 in it. The second column V3:V12 has 1,2,3,4,5,1,2,3,4,5 respectively in each cell, so j3 has a 1 in it, ....>....>.... J12 has a 5 in it.
2. Select the table and whilst selected Type a name in the NAME BOX top left corner, name it vtracTable press <enter>. ( or you can use the Name Manager to do the same thing).
3. enter any 3 digit number 0 to 999 in cell A3
(splitting the digits into 3 cells and converting...)
first digit
4. in cell B3 enter the formula =VLOOKUP(INT(A3/100),vtracTable,2,1) press enter. this gives your first vtrac digit.
second digit
5. In cell C3 enter the formula =VLOOKUP(INT(MOD(A3,100)/10),vtracTable,2,1) press enter. this gives your second vtrac digit.
third digit
6. In cell D3 enter the formula =VLOOKUP(MOD(A3,10),vtracTable,2,1)
You now have the 3 digit vtrac for your first number in cells B3 to D3.
Combining the vtracs into a single cell
7. In cell F3 enter the formula =B3*100+C3*10+D3
you now have your first vtrac as a 3 digit number in a single cell.
8. if you want to be slick and avoid splitting the digits as in 4,5,6 above you can bypass that and go direct to the the 3 digit vtrac in a single cell.
e.g in cell G3 enter the formula =100*VLOOKUP(INT(A3/100),vtracTable,2,1)+10*VLOOKUP(INT(MOD(A3,100)/10),vtracTable,2,1)+VLOOKUP(MOD(A3,10),vtracTable,2,1). You now do not need the values in B3 to D3.
If you copy formulas B3:G3 down the spreadsheet, then 3 digit numbers entered in column A will be converted to vtracs .
OR If your original 3 digit result numbers were separated into 3 cells A25,B25,C25
e.g
| 0 | 1 | 9 |
and you want a 3 digit conversion ,with the answer in a single cell, then in cell F25 enter the formula =VLOOKUP(A25,vtracTable,2,1)*100+VLOOKUP(B25,vtracTable,2,1)*10+VLOOKUP(C25,vtracTable,2,1)
Again you can copy this fromula down the sheet.
Note the vtrac loookup table can be anywhere you like once it has been named.
As usual this took a lot longer to describe than it takes to actually do it! Have a go - its more versatile than a macro, which needs to know where everything is, how much there is, where you want to put it etc. etc.
Frank
Member
For a tidier spreadsheet you could also use a custom created function called GetVtracs().
to use it you need this pasted in a VB module.
Function getVtracs(num As Variant)
Dim dig1, dig2, dig3, vtrac1, vtrac2, vtrac3 As Integer
Dim vtracnum As Variant
If num <> "" Then
dig1 = Int(num / 100)
dig2 = Int((num Mod 100) / 10)
dig3 = num Mod 10
Select Case dig1
Case 0
vtrac1 = 1
Case 1
vtrac1 = 2
Case 2
vtrac1 = 3
Case 3
vtrac1 = 4
Case 4
vtrac1 = 5
Case 5
vtrac1 = 1
Case 6
vtrac1 = 2
Case 7
vtrac1 = 3
Case 8
vtrac1 = 4
Case 9
vtrac1 = 5
End Select
Select Case dig2
Case 0
vtrac2 = 1
Case 1
vtrac2 = 2
Case 2
vtrac2 = 3
Case 3
vtrac2 = 4
Case 4
vtrac2 = 5
Case 5
vtrac2 = 1
Case 6
vtrac2 = 2
Case 7
vtrac2 = 3
Case 8
vtrac2 = 4
Case 9
vtrac2 = 5
End Select
Select Case dig3
Case 0
vtrac3 = 1
Case 1
vtrac3 = 2
Case 2
vtrac3 = 3
Case 3
vtrac3 = 4
Case 4
vtrac3 = 5
Case 5
vtrac3 = 1
Case 6
vtrac3 = 2
Case 7
vtrac3 = 3
Case 8
vtrac3 = 4
Case 9
vtrac3 = 5
End Select
vtracnum = 100 * vtrac1 + 10 * vtrac2 + vtrac3
Else: vtracnum = ""
End If
getVtracs = vtracnum
End Function
So if you had 019 in cell A3 then in any other cell you could use =getvtracs(A3) to get a 3 digit vtrac. in this case you'd get 125 as the vtrac.
to use it you need this pasted in a VB module.
Function getVtracs(num As Variant)
Dim dig1, dig2, dig3, vtrac1, vtrac2, vtrac3 As Integer
Dim vtracnum As Variant
If num <> "" Then
dig1 = Int(num / 100)
dig2 = Int((num Mod 100) / 10)
dig3 = num Mod 10
Select Case dig1
Case 0
vtrac1 = 1
Case 1
vtrac1 = 2
Case 2
vtrac1 = 3
Case 3
vtrac1 = 4
Case 4
vtrac1 = 5
Case 5
vtrac1 = 1
Case 6
vtrac1 = 2
Case 7
vtrac1 = 3
Case 8
vtrac1 = 4
Case 9
vtrac1 = 5
End Select
Select Case dig2
Case 0
vtrac2 = 1
Case 1
vtrac2 = 2
Case 2
vtrac2 = 3
Case 3
vtrac2 = 4
Case 4
vtrac2 = 5
Case 5
vtrac2 = 1
Case 6
vtrac2 = 2
Case 7
vtrac2 = 3
Case 8
vtrac2 = 4
Case 9
vtrac2 = 5
End Select
Select Case dig3
Case 0
vtrac3 = 1
Case 1
vtrac3 = 2
Case 2
vtrac3 = 3
Case 3
vtrac3 = 4
Case 4
vtrac3 = 5
Case 5
vtrac3 = 1
Case 6
vtrac3 = 2
Case 7
vtrac3 = 3
Case 8
vtrac3 = 4
Case 9
vtrac3 = 5
End Select
vtracnum = 100 * vtrac1 + 10 * vtrac2 + vtrac3
Else: vtracnum = ""
End If
getVtracs = vtracnum
End Function
So if you had 019 in cell A3 then in any other cell you could use =getvtracs(A3) to get a 3 digit vtrac. in this case you'd get 125 as the vtrac.
Frank
Member
It works perfectly for me in Excel 2016. There is nothing wrong with the function.
1. You may have to save your spreadsheet as an .xlsm to allow the macro format to be accepted.
2. have you had problems with custom functions before ?
2. what is the FULL error message? It has to be more than compile error ?
3. Make sure you have all the code between Function getVtracs(num As Variant) and End function pasted on a module.
4. are you using it as an excel FUNCTION on the spreadsheet as illustrated e.g =getvtracs(A3) ?
if your version of Excel is not up to date with the latest service pack where necessary, or your operating system is old there may be system configuration problems causing this error, you might have to google the exact wording, as I don't have this problem.
1. You may have to save your spreadsheet as an .xlsm to allow the macro format to be accepted.
2. have you had problems with custom functions before ?
2. what is the FULL error message? It has to be more than compile error ?
3. Make sure you have all the code between Function getVtracs(num As Variant) and End function pasted on a module.
4. are you using it as an excel FUNCTION on the spreadsheet as illustrated e.g =getvtracs(A3) ?
if your version of Excel is not up to date with the latest service pack where necessary, or your operating system is old there may be system configuration problems causing this error, you might have to google the exact wording, as I don't have this problem.
time*treat
Member
Works in '03, too.
I made some cosmetic edits and uploaded an html version for readability.
Copy-paste into a module.
http://www.mediafire.com/file/gzghvum5n4cwnvr/getVtracs.htm
I made some cosmetic edits and uploaded an html version for readability.
Copy-paste into a module.
http://www.mediafire.com/file/gzghvum5n4cwnvr/getVtracs.htm
Frank
Member
Well Bloubul, I see you thanked us for your help, however you didn't use it did you? You either found or developed a short cut which avoided the need for a lookup table, using MOD 5 instead of 10 which I have to admit is a simpler solution to the vtracs. I wonder why you omitted to mention this ??
Having worked out what is going on, I suggest you use this formula:-
=10000*MOD(INT(A1/10000),5)+1000*MOD(INT(A1/1000),5)+100*MOD(INT(A1/100),5)+10*MOD(INT(A1/10),5)+MOD(A1,5)+11111
Good luck!
PS I have tested the solutions against my lookup table method and they do agree.
Having worked out what is going on, I suggest you use this formula:-
=10000*MOD(INT(A1/10000),5)+1000*MOD(INT(A1/1000),5)+100*MOD(INT(A1/100),5)+10*MOD(INT(A1/10),5)+MOD(A1,5)+11111
Good luck!
PS I have tested the solutions against my lookup table method and they do agree.
time*treat
Member
Frank
To be honest with you an excel formula has never cross my mind, But I do thank you for correcting me and with the formula that you have given me.
Thank Sir..
BlouBul
Can't promise this will fix your issue, but it won't hurt.
From the Tools menu (in the VBA Editor, not Excel), select References,
and check "Microsoft Visual Basic For Application Extensibility".
Frank
Member
You may wish to try this custom function getvtracs5(), which uses your way of calculating the vtracs without need to refer to a table.
Paste this into a module:-
Function getvtracs5(num As Variant)
Dim vtracnum As Variant
If num <> "" Then
vtracnum = 10000 * (Int(num / 10000) Mod 5) + 1000 * (Int(num / 1000) Mod 5) + 100 * (Int(num / 100) Mod 5) + 10 * (Int(num / 10) Mod 5) + num Mod 5 + 11111
Else: vtracnum = ""
End If
getvtracs5 = vtracnum
End Function
If your 5 digit pick 5 is in a single cell say (A1)
then in any other cell enter =getVtracs5(A1)
so if 10023 is in A1 your answer in that cell would be 21134
Notice there are no apostrophes or quotes on the line with Function in it.
Frank
Paste this into a module:-
Function getvtracs5(num As Variant)
Dim vtracnum As Variant
If num <> "" Then
vtracnum = 10000 * (Int(num / 10000) Mod 5) + 1000 * (Int(num / 1000) Mod 5) + 100 * (Int(num / 100) Mod 5) + 10 * (Int(num / 10) Mod 5) + num Mod 5 + 11111
Else: vtracnum = ""
End If
getvtracs5 = vtracnum
End Function
If your 5 digit pick 5 is in a single cell say (A1)
then in any other cell enter =getVtracs5(A1)
so if 10023 is in A1 your answer in that cell would be 21134
Notice there are no apostrophes or quotes on the line with Function in it.
Frank
time*treat
Member
Gentle Man
Here are the "Error" and VB Reference.
http://www.mediafire.com/file/13m6scbvj54vs4f/Error.docx
BlouBul
You have "Function getVtracs(num As Variant)
You SHOULD have Function getVtracs(num As Variant)
pebkac. Remove the leading ".
