Andreas Hermle
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:
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
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
Whenever I run the 'CleanTrim_Selected_Cells'
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.
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
Please attach your workbook.
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.
ok, it is a huge file therefore I will prepare a sample file for you to work on. Thank you very much.
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/
http://forum.chandoo.org/threads/vba-jumps-to-a-function-for-no-apparent-reason.20787/
Hi,
I suppose the evaluate function fires a recalculation of the sheet hence firing the recalculation of the functions used in the sheet.
Regards
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
Thank you very much for your superb and professional help. I really highly appreciate your help.
Regards, Andreas