Microsoft Office Tutorials and References
In Depth Information
Die letzte Methode, die wir beschreiben, gibt die Daten aller vorhandenen Szenarien
im angegebenen Tabellenbereich aus:
Sub BereichF├╝llen()
1 Dim x As Long, z As Long
2 Dim spalte As Long
3 Dim rngScenarios As Range
4 Dim c As Range
5 Dim rngCells As Range
6 Dim sh As Worksheet
7 ReDim arrCells(1 To 1)
8 Set sh = ActiveSheet
9 Set rngScenarios = sh.Range(RefEdit1.Value).Cells(1, 1)
10 For x = 1 To sh.Scenarios.Count
11 Set rngCells = sh.Scenarios(x).ChangingCells
12 For Each c In rngCells
13 If z = 0 Then
14 z = z + 1
15 arrCells(z) = c.Address(0, 0)
16 Else
17 If IsError(Application.Match( _
c.Address(0, 0), arrCells, 0)) Then
18 z = z + 1
19 ReDim Preserve arrCells(1 To z)
20 arrCells(z) = c.Address(0, 0)
21 End If
22 End If
23 Next c
24 Next x
25 rngScenarios.Value = "Name"
26 rngScenarios.Offset(0, 1).Value = "Kommentar"
27 For z = 1 To UBound(arrCells)
28 rngScenarios.Offset(0, 1 + z).Value = arrCells(z)
29 Next z
30 For x = 1 To sh.Scenarios.Count
31 z = 0
32 Set rngCells = sh.Scenarios(x).ChangingCells
33 rngScenarios.Offset(x, 0).Value = sh.Scenarios(x).Name
34 rngScenarios.Offset(x, 1).Value = sh.Scenarios(x).Comment
35 For Each c In rngCells
36 z = z + 1
Search JabSto ::




Custom Search