Set range.value as range.address

Limone

Board Regular
Joined
Dec 20, 2018
Messages
57
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...

Code:
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
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
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.
 
Upvote 0
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.
 
Last edited:
Upvote 0
If in D15 you have "B12" then what you need is to get the number 12 to form "C12".
Could be like this:

Code:
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
 
Upvote 0
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.
 
Upvote 0
In cell D15 you must have a cell address, as you put in your example: D15 = B12
 
Upvote 0
Try

Code:
valor = WorksheetFunction.Trim(Range("D15").Value)
 
Upvote 0
Could you put an image or your file to see the data on the sheet?
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,938
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top