AW: Zellen mit Linien verbinden?
22.05.2011 14:57:46
ransi
Hallo
ob Excel/VBA hierfür überhaupt die richtigen Werkzeuge sind.
Ich habs mal versucht.
Packe diesen Code mal in ein Modul:
' **********************************************************************
' Modul: Modul1 Typ: Allgemeines Modul
' **********************************************************************
Option Explicit
Public Function Finde(bereich As Range, Kriterium) As Boolean
Dim Bild As FreeformBuilder
Dim S As Shape
Dim myBol As Boolean
Dim arr As Variant
Dim L As Long
Dim I As Integer
myBol = False
On Error Resume Next
bereich.Parent.Shapes("myFreeform" & Application.ThisCell.Address(0, 0)).Delete
On Error GoTo 0
If WorksheetFunction.CountIf(bereich, Kriterium) > 1 Then
arr = bereich
For L = LBound(arr) To UBound(arr)
For I = LBound(arr, 2) To UBound(arr, 2)
If arr(L, I) = Kriterium Then
With bereich(L, I)
If myBol = False Then
Set Bild = bereich.Parent.Shapes.BuildFreeform(msoEditingAuto, .Left + (.Width / 2), .Top + (.Height / 2))
myBol = True
Else:
Bild.AddNodes msoSegmentLine, msoEditingAuto, .Left + (.Width / 2), .Top + (.Height / 2)
End If
End With
End If
Next
Next
With Bild
Set S = .ConvertToShape
S.Name = "myFreeform" & Application.ThisCell.Address(0, 0)
End With
End If
Finde = myBol
End Function
Der Aufruf geht dann ganz einfach so:
Tabelle1
| | A | B | C | D | E | F | G | H | I |
| 1 | 7 | 9 | 4 | 5 | 4 | 0 | | 7 | WAHR |
| 2 | 7 | 9 | 2 | 1 | 7 | 4 | | | |
| 3 | 6 | 10 | 7 | 3 | 2 | 0 | | | |
| 4 | 5 | 5 | 4 | 7 | 1 | 8 | | | |
| 5 | 10 | 7 | 4 | 5 | 3 | 9 | | | |
| 6 | 4 | 8 | 10 | 5 | 1 | 1 | | | |
| 7 | 7 | 10 | 7 | 2 | 9 | 0 | | | |
| 8 | 9 | 5 | 1 | 7 | 6 | 10 | | | |
| 9 | 10 | 1 | 2 | 2 | 1 | 3 | | | |
| 10 | 6 | 0 | 5 | 9 | 1 | 0 | | | |
| 11 | 2 | 5 | 4 | 4 | 0 | 4 | | | |
| 12 | 0 | 1 | 9 | 3 | 9 | 3 | | | |
| 13 | 5 | 0 | 7 | 0 | 8 | 3 | | | |
| 14 | 2 | 9 | 0 | 7 | 1 | 4 | | | |
| 15 | 3 | 7 | 3 | 4 | 9 | 8 | | | |
| 16 | 10 | 6 | 3 | 7 | 7 | 3 | | | |
| 17 | 9 | 7 | 2 | 9 | 2 | 5 | | | |
| 18 | 7 | 10 | 9 | 4 | 10 | 2 | | | |
| 19 | 7 | 3 | 9 | 9 | 0 | 5 | | | |
| 20 | 2 | 5 | 3 | 1 | 10 | 8 | | | |
| 21 | 8 | 5 | 8 | 5 | 9 | 1 | | | |
| 22 | 8 | 1 | 10 | 3 | 4 | 7 | | | |
| 23 | 9 | 1 | 4 | 10 | 5 | 10 | | | |
| 24 | 8 | 3 | 0 | 2 | 10 | 8 | | | |
| 25 | 6 | 8 | 3 | 8 | 1 | 3 | | | |
| 26 | 7 | 8 | 9 | 10 | 5 | 9 | | | |
| 27 | 9 | 2 | 0 | 8 | 0 | 4 | | | |
| Formeln der Tabelle |
| Zelle | Formel | | I1 | =machs(A1:F200;H1) |
|
Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
ransi
