Link to home
Start Free TrialLog in
Avatar of Andreas Hermle
Andreas HermleFlag for Germany

asked on

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

Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Please attach your workbook.
Avatar of Andreas Hermle


Hi Martin,

ok, it is a huge file therefore I will prepare a sample file for you to work on. Thank you very much.
I found something on the Internet relating to this problem, but I am not able to tweak my code accordingly.
Avatar of Rgonzo1971


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

Avatar of Rgonzo1971

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi Rgonzo, you are the best :-)

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

Regards, Andreas