vba on error event #value

[cdafonseca]

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

Please Login or Register  to view this content.

-v

[Leith Ross]

Hello cdafonseca,

The problem looks like a referencing problem. By default, VBA variables are passed by Reference. This means the variable being must already be declared. If VBA discovers the variable has not been declared then an error occurs. The other method of referencing is by Value. VBA basically creates a copy of the variable to use in the procedure. Try the change below and see if it fixes your problem.

Please Login or Register  to view this content.

[cdafonseca]

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

[Leith Ross]

Hello cdafonseca,

Trapping the error will only prevent the error from be displayed in the cell. However, the reference needed by the UDF (your macro) will still be invalid. You will have to manually reset it.

The drag and drop feature does not work correctly with a UDF. There are many limitations on what a UDF can do. Unfortunately, I don't know of any workarounds for this problem.

[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

[cdafonseca]

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?

[cdafonseca]

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

Please Login or Register  to view this content.

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?

[JosephP]

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

yes-just include the sheet name