Results 1 to 10 of 10
Thread: Excel
Hybrid View
-
24th March 2008 23:51 #1
-
25th March 2008 00:12 #2
=IF(LEN(A2)=10;(IF((MOD(MOD(INT(MID(A2;1 ;1))*2+INT(MID(A2;2;1))*4+
INT(MID(A2;3;1))*8+INT(MID(A2;4;1))*5+IN T(MID(A2;5;1))*10+
INT(MID(A2;6;1))*9+INT(MID(A2;7;1))*7+IN T(MID(A2;8;1))*3+
INT(MID(A2;9;1))*6;11);10)=INT(MID(A2;10 ;1))); ͔; ͔)); 10 )
VBA , A2 .
-
25th March 2008 00:22 #3
-
25th March 2008 09:46 #4
,

, .Code:Sub EGN() Range("A6").Select ActiveCell.Formula = "=IF(LEN(A6)=10;(IF((MOD(MOD(INT(MID(A6;1;1))*2+INT(MID(A6;2;1))*4+INT(MID(A6;3;1))*8+INT(MID(A6;4;1))*5+INT(MID(A6;5;1))*10+INT(MID(A6;6;1))*9+INT(MID(A6;7;1))*7+INT(MID(A6;8;1))*3+INT(MID(A6;9;1))*6;11);10)=INT(MID(A6;10;1)));"" ;"" ""));"" 10 "")" Selection.AutoFill Destination:=Range("6:16"), Type:=xlFillDefault Range("6:16").Select Range("17").Select End Sub
:
Code:ActiveCell.Formula = "=IF(LEN(A6)=10;(IF((MOD(MOD(INT(MID(A6;1;1))*2+INT(MID(A6;2;1))*4+INT(MID(A6;3;1))*8+INT(MID(A6;4;1))*5+INT(MID(A6;5;1))*10+INT(MID(A6;6;1))*9+INT(MID(A6;7;1))*7+INT(MID(A6;8;1))*3+INT(MID(A6;9;1))*6;11);10)=INT(MID(A6;10;1)));"" ;"" ""));"" 10 "")"

.. 6 16
.. , .Last edited by 0gg1; 25th March 2008 at 09:55.
-
25th March 2008 10:45 #5
. entium . VB:
Sub EGN()
Dim EGN As String
EGN = ActiveCell.Value
If (IsNumeric(EGN) <> True) Then
MsgBox "Please select cell containing EGN", vbCritical
ElseIf (Len(EGN) <> 10) Then
MsgBox "Too short or too long for EGN", vbCritical
ElseIf (((Int(Mid(EGN, 1, 1)) * 2 + Int(Mid(EGN, 2, 1)) * 4 + Int(Mid(EGN, 3, 1)) * 8 + Int(Mid(EGN, 4, 1)) * 5 + Int(Mid(EGN, 5, 1)) * 10 + Int(Mid(EGN, 6, 1)) * 9 + Int(Mid(EGN, 7, 1)) * 7 + Int(Mid(EGN, 8, 1)) * 3 + Int(Mid(EGN, 9, 1)) * 6) Mod 11) Mod 10) = Int(Mid(EGN, 10, 1)) Then
MsgBox "Valid EGN"
Else
MsgBox "Invalid EGN", vbExclamation
End If
End Sub
( 6 16) MSgBox - Sheet4
Last edited by dj_simo_boy; 25th March 2008 at 10:51.
-
25th March 2008 15:03 #6
, .
A6 16. . , 4545445 , - , .
Code:Sub EGN() Range("A6").Select Dim EGN As String EGN = ActiveCell.Value If (Len(EGN) <> 10) Then MsgBox "Too short or too long for EGN", vbCritical ElseIf (((Int(Mid(EGN, 1, 1)) * 2 + Int(Mid(EGN, 2, 1)) * 4 + Int(Mid(EGN, 3, 1)) * 8 + Int(Mid(EGN, 4, 1)) * 5 + Int(Mid(EGN, 5, 1)) * 10 + Int(Mid(EGN, 6, 1)) * 9 + Int(Mid(EGN, 7, 1)) * 7 + Int(Mid(EGN, 8, 1)) * 3 + Int(Mid(EGN, 9, 1)) * 6) Mod 11) Mod 10) = Int(Mid(EGN, 10, 1)) Then Else MsgBox "Invalid EGN", vbExclamation End If Selection.AutoFill Destination:=Range("A6:A15"), Type:=xlFillDefault Range("A6:A16").Select Range("A17").Select End Sub
-
26th March 2008 15:00 #7
.... - - 6-16 - Sheet4 ...
- VB ! . / .
...
Function CheckEGN(EGN) As String
If (IsNumeric(EGN) <> True) Then
CheckEGN = " " & EGN & " is not numeric value"
ElseIf (Len(EGN) <> 10) Then
CheckEGN = " " & EGN & " - Too short or too long for EGN"
ElseIf (((Int(Mid(EGN, 1, 1)) * 2 + Int(Mid(EGN, 2, 1)) * 4 + Int(Mid(EGN, 3, 1)) * 8 + Int(Mid(EGN, 4, 1)) * 5 + Int(Mid(EGN, 5, 1)) * 10 + Int(Mid(EGN, 6, 1)) * 9 + Int(Mid(EGN, 7, 1)) * 7 + Int(Mid(EGN, 8, 1)) * 3 + Int(Mid(EGN, 9, 1)) * 6) Mod 11) Mod 10) = Int(Mid(EGN, 10, 1)) Then
CheckEGN = ""
Else
CheckEGN = " " & EGN & " - Invalid EGN"
End If
End Function
Sub CopyRow(SourceRow As Integer)
Sheets("Sheet4").Select
Range("A6").Select
Range(Selection, Selection.End(xlDown)).Select
FirstFreeRow = Selection.Rows.Count + 6
Sheets("Sheet1").Select
Rows("" & SourceRow & ":" & SourceRow & "").Select
Selection.Cut
Sheets("Sheet4").Select
Rows("" & FirstFreeRow & ":" & FirstFreeRow & "").Select
Selection.Insert Shift:=xlDown
Sheets("Sheet1").Select
Selection.Delete Shift:=xlUp
End Sub
Sub CopyValidEGN()
Dim ActiveRow As Integer
Dim LastDestinationRow As Integer
Dim EGN As String
Dim Res As String
ActiveRow = 6
While (Sheet1.Cells(ActiveRow, 1) <> "")
EGN = Sheet1.Cells(ActiveRow, 1)
Sheets("Sheet1").Select 'just to look better
Range("A" & ActiveRow & "").Select 'just to look better
Res = CheckEGN(EGN)
If Res = "" Then
CopyRow (ActiveRow)
Else
Response = MsgBox(" " & Res & " " & vbCrLf & " " & vbCrLf & " Do you want to continue with the next?", vbOKCancel + vbCritical, "Waining for user input ...", Help, Ctxt)
If Response = vbOK Then
ActiveRow = ActiveRow + 1
Else
Exit Sub
End If
End If
Wend
MsgBox "Job done with " & ActiveRow - 6 & " error(s)!", vbOKOnly + vbInformation
End Sub
-
25th March 2008 15:07 #8
http://it-place.net/scripts/2/C-Cplusplus/6/b/366/
, . . , , . .
CODE
public bool IsEgnValid(string egn)
{
//
if (string.IsNullOrEmpty(egn)) return false;
// 10
if (egn.Length != 10) { return false; }
//
double num;
if (!double.TryParse(egn, out num)) { return false; }
// ,
int[] weights = { 2, 4, 8, 5, 10, 9, 7, 3, 6 };
string date = string.Empty;
DateTime dt;
//
int year = Int16.Parse(egn.Substring(0, 2));
//
int month = Int16.Parse(egn.Substring(2, 2));
//
int day = Int16.Parse(egn.Substring(4, 2));
if (month > 40)
{
date = (month - 40) + "-" + day + "-" + (year + 2000);
}
else if (month > 20)
{
date = (month - 20) + "-" + day + "-" + (year + 1800);
}
else
{
date = month + "-" + day + "-" + (year + 1900);
}
if (DateTime.TryParse(date, out dt) == false) { return false; }
int checkSum = Int32.Parse(egn.Substring(9, 1));
int egnSum = 0;
int validCheckSum = 0;
for (int i = 0; i < 9; i++)
{
egnSum += int.Parse(egn.Substring(i,1)) * weights[i];
}
validCheckSum = egnSum % 11;
if (validCheckSum == 10) { validCheckSum = 0; }
if (checkSum == validCheckSum) { return true; }
else { return false; }
}
http://milia.hit.bg/egn/egn.htm"May your glass be ever full.
May the roof over your head be always strong.
And may you be in heaven half an hour before the devil knows you're dead."
-
25th March 2008 15:20 #9
-
25th March 2008 15:22 #10
>?
- egn (SQL Server 2000). egn_no varchar. SQL , .
-- step 1 -> check the years (find the persons aged between 18 and 60)
SELECT LEFT(egn_no, 2), egn_no FROM egn
WHERE LEFT(egn_no, 2) NOT BETWEEN '44' AND '86'
-- step 2 -> check the months
SELECT SUBSTRING(egn_no, 3, 2), egn_no FROM egn
WHERE SUBSTRING(egn_no, 3, 2) NOT BETWEEN '01' AND '12'
-- step 3 -> check the days
SELECT SUBSTRING(egn_no, 5, 2), egn_no FROM egn
WHERE SUBSTRING(egn_no, 5, 2) NOT BETWEEN '01' AND '31'
-- step 4 -> check the regions
SELECT SUBSTRING(egn_no, 7, 2), egn_no FROM egn
WHERE SUBSTRING(egn_no, 7, 2) NOT BETWEEN '00' AND '99'
-- step 5 -> check the gender
SELECT SUBSTRING(egn_no, 9, 1), egn_no FROM egn
WHERE SUBSTRING(egn_no, 9, 1) NOT BETWEEN '0' AND '9'
-- step 6 -> the check sum
SELECT SUBSTRING(egn_no, 10, 1), egn_no FROM egn
WHERE SUBSTRING(egn_no, 10, 1) <> (
CASE (
SUBSTRING(egn_no, 1, 1) *2 +
SUBSTRING(egn_no, 2, 1) *4 +
SUBSTRING(egn_no, 3, 1) *8 +
SUBSTRING(egn_no, 4, 1) *5 +
SUBSTRING(egn_no, 5, 1) *10 +
SUBSTRING(egn_no, 6, 1) *9 +
SUBSTRING(egn_no, 7, 1) *7 +
SUBSTRING(egn_no, 8, 1) *3 +
SUBSTRING(egn_no, 9, 1) *6 ) % 11
WHEN 10 THEN 0
END
)
(27.09.2004)"May your glass be ever full.
May the roof over your head be always strong.
And may you be in heaven half an hour before the devil knows you're dead."




Reply With Quote

Lenovo ThinkPad 15 IdeaPad 15
5th May 2023, 22:16 in