on the active Sheet named 'My_Results
' I try to run a macro that goes like this:
Range("D5:D" & Range("A" & Rows.Count).End(xlUp).Row).Select
Selection = Evaluate("if(" & Selection.Address & "="""","""",substitute(clean(trim(" & Selection.Address & ")),char(160),""""))")
Whenever I run the 'CleanTrim_Selected_Cells'
macro (see above), the code for no apparent reason jumps into the Function section of the below macro (EnterSumFormula_MyResults
The below macro also works on the 'MyResults' Worksheet
(strSheet = ActiveWorkbook.Worksheets(
If I run the 'CleanTrim_SelectedCells' macro in another workbook, everything runs fine and smoothly.
Help is much appreciated. Thank you very much in advance.
Dim rng As Range
Dim LastRow As Long
Dim strSheet As Variant
Dim strFormula As Variant
strSheet = ActiveWorkbook.Worksheets("MyResults").Name
LastRow = Sheets(strSheet).Range("B" & Cells.Rows.Count).End(xlUp).Row
strFormula = "=CountByColor('" & strSheet & "'!$B$4" & ", '" & strSheet & "'!$B$4:$B$" & LastRow & ")"
ActiveWorkbook.Sheets("2_Auswertung").Range("B5").Formula = strFormula
Function CountByColor(CellColor As Range, CountRange As Range)
Dim ICol As Integer
Dim TCell As Range
ICol = CellColor.Interior.ColorIndex
For Each TCell In CountRange
If ICol = TCell.Interior.ColorIndex Then
CountByColor = CountByColor + 1