Nur markierte Zellen verarbeiten

Sub NA_raus() Dim rng As Range, cell As Range, fmla As String Set rng = Cells.SpecialCells(xlCellTypeFormulas, 16) For Each cell In rng fmla = Right(cell.Formula, Len(cell.Formula) - 1) cell.Formula = "=if(iserror(" & fmla & "), """"," & fmla & ")" Next End Sub

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Benutzer

Sub NA_raus() Dim rng As Range, cell As Range, fmla As String Set rng = Selection.SpecialCells(xlCellTypeFormulas, 16) For Each cell In rng fmla = Right(cell.Formula, Len(cell.Formula) - 1) cell.Formula = "=if(iserror(" & fmla & "), """"," & fmla & ")" Next End Sub

Sub NA_raus() Dim rngF As Range, rngC As Range, strFml As String Set rngF = Selection.SpecialCells(xlCellTypeFormulas) For Each rngC In rngF If IsError(rngC.Value) Then strFml = Right(rngC.Formula, Len(rngC.Formula) - 1) rngC.Formula = "=if(iserror(" & strFml & "), """"," & strFml & ")" End If Next rngC End Sub

Sub NA_raus() Dim rngF As Range, rngC As Range, strFml As String Set rngF = Selection.SpecialCells(xlCellTypeFormulas) If Not rngF Is Nothing Then Set rngF = Intersect(rngF, Selection) If Not rngF Is Nothing Then For Each rngC In rngF If IsError(rngC.Value) Then strFml = Right(rngC.Formula, Len(rngC.Formula) - 1) rngC.Formula = "=if(iserror(" & strFml & "), """"," & strFml & ")" End If Next rngC End If End Sub

Hi,
noch eine Variante, mit ",16":


Sub NA_raus()
Dim rngF As Range, rngC As Range, strFml As String
On Error Resume Next
Set rngF = Selection.SpecialCells(xlCellTypeFormulas, 16)
On Error GoTo 0
If Not rngF Is Nothing Then
If Not Intersect(rngF, Selection) Is Nothing Then
For Each rngC In Intersect(rngF, Selection)
strFml = Right(rngC.Formula, Len(rngC.Formula) - 1)
rngC.Formula = "=if(iserror(" & strFml & "), """"," & strFml & ")"
Next rngC
End If
End If
End Sub

Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort

Sub NA_raus() Dim rng As Range, cell As Range, fmla As String On Error Resume Next Set rng = Selection.SpecialCells(xlCellTypeFormulas, 16) On Error GoTo 0 If Not rng Is Nothing Then For Each cell In rng fmla = Right(cell.Formula, Len(cell.Formula) - 1) cell.Formula = "=if(iserror(" & fmla & "), """"," & fmla & ")" Next End If End Sub

Sub NA_raus() Dim rng As Range, cell As Range, fmla As String If Selection.Count > 1 Then On Error Resume Next Set rng = Selection.SpecialCells(xlCellTypeFormulas, 16) On Error GoTo 0 Else If IsError(Selection) Then Set rng = Selection End If If Not rng Is Nothing Then For Each cell In rng fmla = Right(cell.Formula, Len(cell.Formula) - 1) cell.Formula = "=if(iserror(" & fmla & "), """"," & fmla & ")" Next End If End Sub

Beliebteste Forumthreads (12 Monate)

15
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

46 Forumthreads zu ähnlichen Themen

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

Nur markierte Zellen verarbeiten

15 Beiträge zum Forumthread Beiträge zu diesem Forumthread

46 Forumthreads zu ähnlichen Themen

VBA Beispiele zu verwandten Themen

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

15
Beiträge zum Forumthread

Beiträge zu diesem Forumthread