Results 1 to 12 of 12

Thread: VBA Excel

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1
    Registered User
    Join Date: Oct:2008
    Location:
    Posts: 95

    VBA Excel

    ,

    , .
    Sheet1:
    A____B
    0001 5
    0001 66
    0002 12
    0002 11
    0002 7
    0002 3
    0003 7
    0003 5
    0003 13

    VBA , , B, A. Sheet2 A . :
    A____B
    0001 71
    0002 33
    0003 25

    !

  2. #2
    Registered User
    Join Date: Apr:2004
    Location: EU
    Posts: 141
    VBA Excel .

    - :
    Sub SumEx(keys As range, values As range, destination As range)

    keys -
    values -
    destination -

    : =SumEx(A1:A9,B1:B9,Sheet2!A1)

    :
    Dim results As Dictionary ( "microsoft scripting runtime" Dictionary)

    keys values.
    key Dictionary- ( ).
    key Dictionary- .

    Dictionary- . :-)
    Last edited by zstoev; 11th December 2008 at 11:08.

  3. #3
    Registered User
    Join Date: Oct:2008
    Location:
    Posts: 95
    , , .
    :
    Sub Makro1()

    Dim Count
    Count = 1
    For i = 1 To 12
    If Worksheets("Sheet1").Cells(i, 1) <> Worksheets("Sheet1").Cells(i + 1, 1) Then
    Cells(Count, 1) = Worksheets("Sheet1").Cells(i, 1)
    Count = Count + 1
    End If
    Next i

    ' , , Sheet1

    End Sub

  4. #4
    Registered User
    Join Date: Oct:2003
    Location:
    Posts: 4,317
    , . . Pivot tables. - , .

  5. #5
    Registered User
    Join Date: Apr:2004
    Location: EU
    Posts: 141
    Quote Originally Posted by bsb View Post
    , . . Pivot tables. - , .
    . ! Excel.
    http://www.microsoft.com/dynamics/us...s_collins.mspx

    "microsoft scripting runtime" .

    - ? ... 12?


    "Pivot tables"
    Attached Files

  6. #6
    Banned
    Join Date: Jun:2008
    Location:
    Posts: 1,880
    Quote Originally Posted by PhrozenCrew View Post
    ,

    , .
    Sheet1:
    A____B
    0001 5
    0001 66
    0002 12
    0002 11
    0002 7
    0002 3
    0003 7
    0003 5
    0003 13

    VBA , , B, A. Sheet2 A . :
    A____B
    0001 71
    0002 33
    0003 25

    !
    SUMIF()?

  7. #7
    Registered User
    Join Date: Oct:2008
    Location:
    Posts: 95
    ven_stoich, !
    :

    Sub Makro3()
    Dim tmp
    tmp = 1

    Dim rng As Range

    Dim Count
    Count = 1
    For i = 2 To 12
    If Worksheets("Sheet1").Cells(i, 1) <> Worksheets("Sheet1").Cells(i + 1, 1) Then
    Cells(Count, 1) = Worksheets("Sheet1").Cells(i, 1)
    Count = Count + 1
    End If
    Next i

    For i = 2 To 12
    Cells(i, 2) = Application.SumIf(Worksheets("Sheet1").R ange("A3:B20"), Cells(i, 1), Range("B3:B20"))
    Next i

    End Sub

  8. #8
    Registered User
    Join Date: Oct:2008
    Location:
    Posts: 95
    zstoev, !
    . , 4-5 . 30.
    , . Perl + OLE WinAPI. exe, .
    , VB. , , Cells, Range . ...

  9. #9
    Registered User
    Join Date: Apr:2004
    Location: EU
    Posts: 141
    PhrozenCrew, .
    .

    ... ... :

    Code:
    Sub Macro1()
        Dim sourceSheet As String
        Dim destinationSheet As String
        Dim startIndex As Integer
        Dim endIndex As Integer
        Dim idColumn As Integer
        Dim dataColumn As Integer
        
        Dim id As String
        Dim value As Double
        
        Dim result As Dictionary
        Dim i As Integer
        
        sourceSheet = "Sheet1" ''   
        destinationSheet = "Sheet2" ''     - Sheet-    !
        startIndex = 2 ''      
        endIndex = 25 ''    
        
        idColumn = 1 ''      -      000
        dataColumn = 2 ''         
        
        Set result = New Dictionary
        
        For i = startIndex To endIndex
            ''               
            id = Worksheets(sourceSheet).Cells(i, 1)
            ''       
            value = Worksheets(sourceSheet).Cells(i, 2)
            
            Dim tempSum As Double
            tempSum = 0
            If result.Exists(id) Then
                tempSum = result(id)
            End If
            
            tempSum = tempSum + value
            
            result(id) = tempSum
        Next i
        
        For i = 0 To result.Count - 1
            Worksheets(destinationSheet).Cells(i + 1, 1) = result.keys(i)
            Worksheets(destinationSheet).Cells(i + 1, 2) = result.Items(i)
        Next i
    End Sub
    . .
    a .

    :
    Microsoft VB Editor -> Tools menu -> References
    "Microsoft Scripting Runtime"
    Last edited by zstoev; 11th December 2008 at 15:03.

  10. #10
    Registered User
    Join Date: Oct:2008
    Location:
    Posts: 95
    zstoev, !
    , , !
    !

  11. #11
    Registered User
    Join Date: Apr:2004
    Location: EU
    Posts: 141
    :-)
    , !!! .
    , :-)

  12. #12
    Registered User
    Join Date: Oct:2008
    Location:
    Posts: 95
    , :
    Code:
    Sub cons()
    Count = 1
    Sum = Worksheets("Sheet1").Cells(1, 2).Value
    For i = 2 To 12
         If Worksheets("Sheet1").Cells(i, 1) = Worksheets("Sheet1").Cells(i - 1, 1) Then
              Sum = Sum + Worksheets("Sheet1").Cells(i, 2).Value
         Else
              Worksheets("Sheet2").Cells(Count, 1).Value = Worksheets("Sheet1").Cells(i - 1, 1).Value
              Worksheets("Sheet2").Cells(Count, 2).Value = Sum
              Count = Count + 1
              Sum = Worksheets("Sheet1").Cells(i, 2).Value
         End If
    Next i
    End Sub
    . !

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 |