Set range.value as range.address

[Limone]

Hi.
I've got a formula that returns a cell address. I set up a worksheet_calculate event that's supposed to extract it (as a value) and give me the address of the cell next to the one originally returned by the formula among the other things. Sometimes it works but eventually it always crashes.

This is one of the various ways I've tried to make it work...

Private Sub worksheet_calculate()
Dim i As String
Set Target = ActiveSheet.Range("d15")
If Target Is Nothing Then Exit Sub
If Range("c15").Value = "" Then Exit Sub


i = Range(Range("d15").Value).Offset(0, 1).Address(False, False)


    Range("e15").Value = "'=SE(E(" & i & "<>"""";" & i & "<>""F"");VERO;FALSO)"


End Sub

 

[DanteAmor]

Could you give examples of what you have in the cells and what you expect from the result?
I see that you are trying to create a formula, you could put the formula you need and what data should vary in the formula.

 

[Limone]

Sure.

C15 = "John Smith" (name that the formula is going to look up in a different sheet)
D15 = B12 (cell where John Smith is placed, returned by formula that triggers the event)
E15 = formula strung together where the variable i is supposed to be the address of the cell right next to the one returned in D15

So i is going to be C12.

Complete formula (in English): =IF(AND(C12<>"";C12<>"F");TRUE;FALSE)

What I have issues with is find a way to use offset with the value inside of D15, which would give me what I need.

Also, do you know any other way to get Excel to allow me to return the formula comprised of "=" without having to place an apostrophe next to it? The formula is going to be copied and the apostrophe will as well if I don't find another way.

 

[DanteAmor]

If in D15 you have "B12" then what you need is to get the number 12 to form "C12".
Could be like this:

Private Sub worksheet_calculate()
    If Range("D15") Is Nothing Then Exit Sub
    If Range("C15") Is Nothing Then Exit Sub
    '
    'Gets the data that is inside the cell
    valor = Range("D15").Value
    'Get the row according to the data obtained from the cell
    i = Range(valor).Row
    '
    'Disable the events, since you are creating a formula and this would activate the Calculate event again causing an endless loop
    Application.EnableEvents = False
    'Create the formula
    Range("E15").Formula = "=IF(AND(C" & i & "<>"""",C" & i & "<>""F""),TRUE,FALSE)"
    'Enable events again
    Application.EnableEvents = True
End Sub

Try and tell me

 

[Limone]

Hi Dante, thank you for the code.
Unfortunately it returns a run time 1004 error and "i=Range(valor).Row" gets highlited.
Other than that, since the names are all placed along the same column I think your method will work fine, thank you.

 

[DanteAmor]

In cell D15 you must have a cell address, as you put in your example: D15 = B12

 

[Limone]

D15 does contain an address but I still get the same error.

 

[DanteAmor]

Try

valor = WorksheetFunction.Trim(Range("D15").Value)

 

[Limone]

Same error. Here's a screenshot of the code.

https://imgur.com/udQdfaq

 

[DanteAmor]

Could you put an image or your file to see the data on the sheet?