Results 1 to 10 of 10

Thread: Excel

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1
    Registered User 0gg1's Avatar
    Join Date: Mar:2007
    Location: Bulgaria
    Posts: 119

    Excel

    ,
    Excel.
    .

    :
    1) Sheet1, Sheet2, Sheet3 , - .
    2) Sheet1, Sheet2, Sheet3 Sheet4 50 .

    . , -


  2. #2
    Registered User entium's Avatar
    Join Date: May:2003
    Location:
    Posts: 1,426
    =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 .

  3. #3
    Registered User 0gg1's Avatar
    Join Date: Mar:2007
    Location: Bulgaria
    Posts: 119
    Quote Originally Posted by entium View Post
    =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 .


  4. #4
    Registered User 0gg1's Avatar
    Join Date: Mar:2007
    Location: Bulgaria
    Posts: 119
    ,

    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.

  5. #5
    Registered User dj_simo_boy's Avatar
    Join Date: Feb:2003
    Location: Sofia
    Posts: 154
    . 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.

  6. #6
    Registered User 0gg1's Avatar
    Join Date: Mar:2007
    Location: Bulgaria
    Posts: 119
    , .

    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

  7. #7
    Registered User dj_simo_boy's Avatar
    Join Date: Feb:2003
    Location: Sofia
    Posts: 154
    .... - - 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

  8. #8
    ... Cenzor's Avatar
    Join Date: Jan:2003
    Location:
    Posts: 17,879
    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."

  9. #9
    Registered User 0gg1's Avatar
    Join Date: Mar:2007
    Location: Bulgaria
    Posts: 119
    Cenzor , , Visual Basic. , , . .

  10. #10
    ... Cenzor's Avatar
    Join Date: Jan:2003
    Location:
    Posts: 17,879
    Quote Originally Posted by 0gg1 View Post
    Cenzor , , Visual Basic. , , . .
    >?



    - 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."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

Copyright © 1999-2011 . .
iskamPC.com | mobility.BG | Bloody's Techblog | | 3D Vision Blog |