With c.FormatConditions(1) : strFormula1 = .Formul
13.09.2013 11:05:35
Peter
Ich will in einem grösseren Workbook die Zellen mit bedingter Formatierung identifizieren.
Was ist beim Codebereich
With c.FormatConditions(1) : strFormula1 = .Formula1 :End With
nicht nicht gut?
Gruss, Peter
Sub PS_Analyse3()
Dim wbThis As Workbook, wsTab As Worksheet, WS As Worksheet
Dim c As Range, Zaehler As Long, strTemp As String, lngZeile As Long, lngSpalte1 As Long, _
lngSpalte2 As Long, lngSpalte3 As Long, strAddress As String, strFormula1 As String
Dim strTab As String
''GetMoreSpeed
strTab = "Analyse_BEDIFO"
Set wbThis = ThisWorkbook
Set wsTab = wbThis.Sheets(strTab)
wsTab.Cells.Clear
wsTab.Cells(1, 1) = "Worksheet"
wsTab.Cells(1, 2) = "Adresse"
wsTab.Cells(1, 3) = "strFormel"
For Each WS In Worksheets
If Left(WS.Name, 7) "Analyse" Then
For Each c In WS.UsedRange
strAddress = c.Address
With c.FormatConditions(1)
strFormula1 = .Formula1
End With
strTemp = strFormula1
strFormula1 = "F" & strFormula1
If strTemp "" Then
Zaehler = Zaehler + 1 ''': Debug.Print Zaehler & " " & WS.Name
lngZeile = 1 + wsTab.Cells(wsTab.Rows.Count, 1 + Int(Zaehler / 1000000)).End( _
xlUp).Row
lngSpalte1 = 1 + Int(Zaehler / 1000000) * 4
lngSpalte2 = 2 + Int(Zaehler / 1000000) * 4
lngSpalte3 = 3 + Int(Zaehler / 1000000) * 4
wsTab.Cells(lngZeile, lngSpalte1) = strFormula1
wsTab.Cells(lngZeile, lngSpalte2) = strAddress
End If
If Zaehler Mod 100000 = 0 Then
Debug.Print Format(Now, "HH:MM:SS") & " " & "Gespeichert bei Zaehler " & _
Zaehler
wbThis.Save
Application.ScreenUpdating = True
Application.ScreenUpdating = False
'Stop
End If
Next c
'Stop
End If
Next
''GetMoreSpeed (0)
End Sub