Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I optimize this code?
Hello,
I have many sheets with many checkboxes! My problem is, that th function checks each checkboxes (until Zelle.Address ol.TopLeftCell.Address!), although the address of the searched checkbo is known (Set rng = ws.Cells(z, s)). Is it possible to check only the searched checkbox (=ws.cells(z,s)) i the function? Thanks a lot for help, wullux Sub Auswertung() Dim rng As Range Dim ws As Worksheet Dim j As Integer Dim s As Integer Dim wert As Integer Dim z As Integer Dim lngletztezeile As Integer For j = 1 To Worksheets.Count For s = 2 To 32 wert = 0 lngletztezeile = Worksheets(j).Cells(Worksheets(j).Rows.Count 1).End(xlUp).row For z = 4 To lngletztezeile Set ws = ThisWorkbook.Worksheets(j) Set rng = ws.Cells(z, s) If ZelleHatOLEObject(Zelle:=rng, blatt:=ws) Then wert = wert + 1 Tabelle1.Cells(j, s) = wert End If Next z Next s Tabelle1.Cells(j, 33) = WorksheetFunction.Sum(Cells(j, 2), Cells(j 3)) Next j Set ws = Nothing Set rng = Nothing End Sub Function ZelleHatOLEObject(Zelle As Range, blatt As Worksheet) A Boolean Dim ol As OLEObject ZelleHatOLEObject = False For Each ol In blatt.OLEObjects If Zelle.Address = ol.TopLeftCell.Address Then If ol.Object.Value = True Then ZelleHatOLEObject = True Exit Function End If End If Next ol End Functio -- Message posted from http://www.ExcelForum.com |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
HELP! optimize calculating values | Excel Discussion (Misc queries) | |||
Optimize simple macro | Excel Worksheet Functions | |||
Optimize SumProduct | Excel Discussion (Misc queries) | |||
Optimize SumProduct | Excel Worksheet Functions | |||
Macro too slow...how to optimize | Excel Programming |