vba on error event #value

hi forum I use this function that i use to remove the right side of a string!

my problem is that sometimes i get an error (#value) to correct this problem i need to reference the cells again .

the error happen when i drag a string on to the cell witch is referenced

I need a macro to catch this error on event sample.xls

the logic will be if error then reference again the cells

Function REMOVE_RIGHT_SIDE(MySTRING As String) As String
    Dim IFOUND As Integer
    Dim I As Integer
    IFOUND = 0
    For I = Len(MySTRING) To 1 Step -1
        If ((Mid(MySTRING, I, 1) = " ") And (IFOUND = 0)) Then IFOUND = I: Exit For
    Next I
    If (IFOUND <> 0) Then REMOVE_RIGHT_SIDE = Left(MySTRING, IFOUND - 1)
End Function

-v

thanks for the interest on this but it did not work for me


the function works great but

i only get this #ref error when i drag a string on to the cell instead copy and paste


thats i need some code to run on error event

if the cell shows #ref error then
reference the cells again
else
do nothing


else i need to rethink another way

Originally Posted by JosephP

if you mean that you are dragging cells to create that REF error then it's no different from regular excel functions. if you use
=REMOVE_RIGHT_SIDE(INDIRECT("A1"))
the function works because the reference is not direct

but can I use this if I need to reference from a different sheet?

I have created a macro to correct the references make one step faster but I have created another problem!
i want this macro to run automatically

now by placing this formula

=IF(ISERROR(SUM(A7:A25)),1,0)

on my sheet i can return a number 1 on a cell if any the referenced cells result on an error


is there a way to call a macro based on result of a formula?