Results 1 to 12 of 12
Thread: VBA Excel
Hybrid View
-
11th December 2008 10:16 #1Registered 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
!
-
11th December 2008 11:00 #2Registered 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.
-
11th December 2008 13:05 #3Registered 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
-
11th December 2008 13:18 #4Registered User
Join Date: Oct:2003
Location:
Posts: 4,317
, . . Pivot tables. - , .
-
11th December 2008 14:16 #5Registered User
Join Date: Apr:2004
Location: EU
Posts: 141
. ! Excel.
http://www.microsoft.com/dynamics/us...s_collins.mspx
"microsoft scripting runtime" .
- ? ... 12?
"Pivot tables"
-
11th December 2008 13:24 #6
-
11th December 2008 13:57 #7Registered 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
-
11th December 2008 14:27 #8Registered User
Join Date: Oct:2008
Location:
Posts: 95
zstoev, !
. , 4-5 . 30.
, . Perl + OLE WinAPI. exe, .
, VB. , , Cells, Range . ...
-
11th December 2008 14:36 #9Registered 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.
-
11th December 2008 15:21 #10Registered User
Join Date: Oct:2008
Location:
Posts: 95
zstoev, !
, , !
!
-
11th December 2008 15:24 #11Registered User
Join Date: Apr:2004
Location: EU
Posts: 141
:-)
, !!! .
, :-)
-
12th December 2008 21:17 #12Registered 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




Reply With Quote

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