VBA Range absolute formula to relative

[Jaymond Flurrie]

This should be pretty simple one. I have in a range a formula that is absolute. I want to turn it to fully relative. What's the smartest option?

So it is:

=COUNTIFS(Data!$I$2:$I$586;">0";Data!$EC$2:$EC$586;1)

And I want it as:

=COUNTIFS(Data!I2:I586;">0";Data!EC2:EC586;1)

Can I for example use FormulaR1C1 in between? I specifically don't want to make the original input as FormulaR1C1, but I could translate the final result to FormulaR1C1 that I then write to the cell.

 

[Zot]

Don't really understand what you want, but in VBA say you write say rng as Range:
rng.Address -- you get absolute address like $A$1
rng.Address(0,0) -- you get relative address like A1

 

[StephenCrump]

I also don't understand the question. This will insert a fully relative formula:

Range("SomeRange").Formula = "=COUNTIFS(Data!I2:I586,"">0"",Data!EC2:EC586,1)"

 

[bebo021999]

Or using find/Replace : Hightlight range, Find: $; replace with: leave it blank

 

[Jaymond Flurrie]

Ok, it seems that the actual problem I have is with the address property.

So I can do it with something like:

    dim myaddress as string
myaddress = Replace(myrange.Offset(, 1).Address, "$", "")

 

[Zot]

Ok, it seems that the actual problem I have is with the address property.

So I can do it with something like:

    dim myaddress as string
myaddress = Replace(myrange.Offset(, 1).Address, "$", "")

If you have the code already done that way, then @bebo021999 method is a quick way to re-code

If you want to get the relative address during runtime, then like I said
myaddress = myrange.Offset(,1).Address(0,0)

not need to do replacement. You get the address without $ sign already.

 

[Jaymond Flurrie]

If you have the code already done that way, then @bebo021999 method is a quick way to re-code

If you want to get the relative address during runtime, then like I said
myaddress = myrange.Offset(,1).Address(0,0)

not need to do replacement. You get the address without $ sign already.

Yeah, I think that's a lot better than a custom function