I am working on an Excel project and I wanted to implement a UDF to change the value of multiple cells. I tried the following code I found on another thread to change value on an adjacent cell, so I played a bit around with the Offset parameters:
Function clear_it()
Dim pas As String
pas = "Call del(" & Application.Caller.Offset(0 , 6).Address(False, False) & ")"
Evaluate pas
End Function
'---------------------------
Sub del(rng As Range)
rng = 0
End Sub
This code worked as intended and it "deleted" the cell which was 6 places to the right by setting its value to 0!
Following the same logic, I tried the following code to delete multiple cells on the same column:
Function clear_it()
Dim pas As String
Dim i As Integer
For i = 0 To 5
pas = "Call del(" & Application.Caller.Offset(0 + i, 6).Address(False, False) & ")"
Evaluate pas
Next i
End Function
'---------------------------------------
Sub del(rng As Range)
rng = 0
End Sub
However this code just crashed my Excel Workbook and it bricked it. The file was just restarting and crashing on an infinite loop and I could not access the code to change it. So I had to delete the file permanently.
To clarify my intentions:
I have a column of data. When I set a particular cell to 0, I want 5 cells beneath it to also become 0 automatically, without pressing a button or something like that, so I wanted to create something like an If formula 6 cells to the left which calls my function:
=IF(J6 = 0; clear_it();0)
To further clarify my attempts:
I have only tested it by putting the above condition on a single cell, hence an infinite loop by recursive 0 valued cells is not possible.
Can anyone perhaps help me on what the problem might be or if I am trying something I shouldn`t with the UDF behaviour? Or is my approach to this problem totally wrong and there is a simpler way to do this on Excel?
