Avatar of Andreas Hermle
Andreas Hermle
Flag 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)

    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

VBAMicrosoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
Andreas Hermle

8/22/2022 - Mon
Martin Liss

Please attach your workbook.
Andreas Hermle

ASKER
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 Hermle

ASKER
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/
Your help has saved me hundreds of hours of internet surfing.
fblack61
Rgonzo1971

Hi,

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

Regards
ASKER CERTIFIED SOLUTION
Rgonzo1971

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Andreas Hermle

ASKER
Hi Rgonzo, you are the best :-)

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

Regards, Andreas