Relative References Named Ranges

[cmore]

Hi, I am having trouble pushing a formula to cell and maintaining my exact relative reference; please help if possible. For some reason it keeps referring to U$12 rather than K$12. I'm not sure RefersToRange helps. Thanks for your help

Please Login or Register  to view this content.

[romperstomper]

I suggest that you:
1. Post actual code in future.
2. Use R1C1 references, then you won't have to activate anything.

[cmore]

I suggest that you:
1. Post actual code in future.
2. Use R1C1 references, then you won't have to activate anything.

I would love to unfortunately it's part of a larger set of code that I cannot post here. I'm not sure how to do what you're saying with R1C1 though?

Also, are we saying it's not possible to capture the correct reference with A1? Everything else is working it's just giving me a "random" U reference instead of K

[romperstomper]

But what you posted wouldn't even have compiled.

[cmore]

But what you posted wouldn't even have compiled.

Please Login or Register  to view this content.

it compiles, this is a piece of code that adds the name, sorry, just looking for clarification what you need to help me, because I am in need of help sir. I just cannot put the whole sub. on here

[romperstomper]

That first line could not possibly compile.

I'd need to know what the other variables are because they will likely need converting to R1C1 format as well.

[cmore]

That first line could not possibly compile.

I'd need to know what the other variables are because they will likely need converting to R1C1 format as well.

It compiles.

Line 1: declares static variable but is just K$12

Line 2: GoTo cell

Line 3: adding Named Range Formula; incorrectly referring to U$12 instead of K$12

Line 4: nonsense, removed

[cmore]

So I found it (a little), basically when you do this weird relative thing, you have to add the relative formula with an Range("A1") basis if that makes any sense

[romperstomper]

Again, there is no way that compiles. You can't declare and assign a variable in one line in VBA, and the variable is clearly a string so its value should be in quotes. Anyway, irrelevant now but in future it would be better to post the actual relevant lines of code otherwise people trying to help you will be confused and distracted by code issues that don't actually apply, rather than trying to address your issue.

[cmore]

Ha, you're right, sorry about that, I didn't even realize I ddin't copy the whole declaration

Please Login or Register  to view this content.

the variable is left as variant. I 100% understand what you're saying and see how it can be distracting.

[cmore]

Ha, you're right, sorry about that, I didn't even realize I ddin't copy the whole declaration

Please Login or Register  to view this content.

the variable is left as variant. I 100% understand what you're saying and see how it can be distracting.

So what I ended up doing for this is, I add an offset formula to update the formula based on the cell's location. I don't think this is optimal though, because I have to use offset and address (volatile) in several cells. I am also taking your suggestion of converting formulas to R1C1 and then pushing them to the named range also (application.convertformula). I have to convert them because the formulas are a user input.

Still wondering if there's a better way to do this