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

Andreas Hermle
Andreas Hermle used Ask the Experts™
on
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)

    Application.Volatile
    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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Please attach your workbook.
Andreas HermleTeam leader

Author

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 leader

Author

Commented:
I found something on the Internet relating to this problem, but I am not able to tweak my code accordingly.

http://forum.chandoo.org/threads/vba-jumps-to-a-function-for-no-apparent-reason.20787/
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Top Expert 2016

Commented:
Hi,

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

Regards
Top Expert 2016
Commented:
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

Andreas HermleTeam leader

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial