Calculating referensnumber? 1

[Ersbygubben]

We use referensnumbers in invoices etc. and one is calculated so that you multiply the ref.numbers last digit with 7, the second last with 3, the third last with 1 and the fourth again with 7, the fifth with 3 and so on. The results you get you add and get a number. This number is rounded up to the nearest 10:th and your original sum is deduced from this and the differensce is the controlnumber:
123456
137137
1+6+21+4+15+42=89
90
-89
_____
1 so the referencenumber will be 123561
Now I need an automatic formula to calculate this when i fill in a referencenumber in a cell (since the invoice is created in Excel). Can it be done? Can an entering of a number act as a starter of a macro? Anyone have an idea?

 

[DarkSun]

Yes this can be done. The best way is to write a user defined function in VB that will calculate the number. Then use that function in a cell.

Was the Reference number supposed to be 1234561 not 123561??

Is the Original number always going to be 6 digits?

After you answer my questions I can write the function for you if you like.

 

[Mats]

I'm also interested in knowing how to use user defined functions. The code itself is easy to write, but I can't figure out how to use my functions in a cell.

Currently I just run the code with appropriate events.

Mats

 

[Ersbygubben]

Well the original number is the 123456 and the digit 1 is the checknumber. But the referensnumber can be any amount of digits from three up to 20 or more.

 

[DarkSun]

You need to do something like this.

Put this function in a Module on the workbook.

Public Function RefNbr(OrigNbr As Variant) As Variant
Dim AryNumbers(6) As Integer
Dim MySum As Integer
Dim RoundedSum As Integer

RunningTotal = 0

For i = 1 To 6
AryNumbers(i) = Int(Mid(OrigNbr, i, 1))
Select Case i
Case 1, 4
MySum = MySum + AryNumbers(i) * 1
Case 2, 5
MySum = MySum + AryNumbers(i) * 3
Case 3, 6
MySum = MySum + AryNumbers(i) * 7
End Select
Next i

RoundedSum = WorksheetFunction.RoundUp(MySum / 10, 0) * 10
RefNbr = OrigNbr & RoundedSum - MySum
End Function

Then you can type this in the cell where you want it to display the reference number.

=RefNbr(A1)

where the value in A1 is something like 123456.

This will only work for numbers with 6 digits.

If this isn't what you wanted let me know and I will change it. Also If you have any problems getting it to work let me know.

 

[DarkSun]

Ersbygubben,
Sorry what do you want it to disply, the check number???

Mats,
You need to place the Function in a Module either in an Addin or in the Workbook you are using the Function.

You then just type "=FunctionName(Arguements,,,,)"
This then displays the result.

 

[DarkSun]

This new function can handle different size ref numbers.

Public Function RefNbr(OrigNbr As Variant) As Variant
Dim AryNumbers() As Integer
Dim MySum As Integer
Dim RoundedSum As Integer
Dim Digits As Integer

RunningTotal = 0
Digits = Len(OrigNbr)
For i = 1 To Digits
ReDim Preserve AryNumbers(i)
AryNumbers(i) = Int(Mid(OrigNbr, i, 1))
If Multiplier = 1 Then
Multiplier = 3
Else
If Multiplier = 3 Then
Multiplier = 7
Else
Multiplier = 1
End If
End If

MySum = MySum + AryNumbers(i) * Multiplier
Next i

RoundedSum = WorksheetFunction.RoundUp(MySum / 10, 0) * 10
RefNbr = OrigNbr & RoundedSum - MySum
End Function

At present it returns "Refnumber & CheckNumber"

 

[Mats]

Thanks,

I've been trying to create a function at the sheet level - no wonder it didn't work. Now it works just fine. I mostly use Access so coding in Excel is a bit slow for me...

Mats