Macro jumps into function section of another macro for no apparent reason

Dear Experts:

on the active Sheet named 'My_Results' I try to run a macro that goes like this:

Sub CleanTrim_SelectedCells()

Range("D5:D" & Range("A" & Rows.Count).End(xlUp).Row).Select

    Selection = Evaluate("if(" & Selection.Address & "="""","""",substitute(clean(trim(" & Selection.Address & ")),char(160),""""))")

End Sub

Open in new window

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). Why???
The below macro also works on the 'MyResults' Worksheet (strSheet = ActiveWorkbook.Worksheets("MyResults").Name)

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.

Regards, Andreas

Sub EnterSumFormula_MyResults()
    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

End Sub

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
        End If
    Next TCell

End Function

Open in new window

Andreas HermleTeam leaderAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
Please attach your workbook.
Andreas HermleTeam leaderAuthor Commented:
Hi Martin,

ok, it is a huge file therefore I will prepare a sample file for you to work on. Thank you very much.
Andreas HermleTeam leaderAuthor Commented:
I found something on the Internet relating to this problem, but I am not able to tweak my code accordingly.
Ensure Business Longevity with As-A-Service

Using the as-a-service approach for your business model allows you to grow your revenue stream with new practice areas, without forcing you to part ways with existing clients just because they don’t fit the mold of your new service offerings.


I suppose the evaluate function fires a recalculation of the sheet hence firing the recalculation of the functions used in the sheet.

then try
Sub CleanTrim_SelectedCells()

Range("D5:D" & Range("A" & Rows.Count).End(xlUp).Row).Select
    Application.Calculation = xlManual
    Selection = Evaluate("if(" & Selection.Address & "="""","""",substitute(clean(trim(" & Selection.Address & ")),char(160),""""))")
    Application.Calculation = xlAutomatic
End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Andreas HermleTeam leaderAuthor Commented:
Hi Rgonzo, you are the best :-)

Thank you very much for your superb and professional help. I really highly appreciate your help.

Regards, Andreas
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.