relative/absolute address - newbie Q.

[DonAndress]

Hello.

I am having a really hard time understanding absolute and relative ways of addressing cells.

Could you please help me undestand it or at least change the address below, as I have no idea how they work?
In my project I need absolute addresses.

Sheets("sheet2").Select
    Range("A1").Select
    val = "=sheet2!R[38]C[-7]-sheet2!R[50]C[-7]"
Sheets("sheet1").Select
    Range("J3").Select
    Selection.Value = val
    Selection.NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(* ""-""??_);_(@_)"
    Application.CutCopyMode = False

Of course I'm refering to "val" calculations so is there any possibility to change addresses to any understadable format?

 

[Cindy Ellis]

Hi, and Welcome to the Mr. Excel board!

Try this:

Sub FixedRefFormula()
'
With Sheets("Sheet1").Range("J3")
    .Formula = "=Sheet2!C38-Sheet2!C50"
    .NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(* ""-""??_);_(@_)"
End With
End Sub

You will need to replace "C38" and "C50" with the correct cell references...that was the best I could figure out from your code sample.
This assumes your worksheets are named "Sheet1" and "Sheet2". Change those as necessary also.

Hope that helps,

Cindy

 

[DonAndress]

Works like a charm! Thank you, Cindy

And to answer you - this is actually my concern. The cell addresses were not C38 and C50 but C42 and C54. Written as in my example
"=sheet2!R[38]C[-7]-sheet2!R[50]C[-7]"
gave exactly C42 and C54.

Why?
Could you please explain this to me?

 

[mikerickson]

R1C1 notation uses the brackets to indicate a relative address.

Your code put a formula in J4

=R[38]C[-7] will result in a formula with relative row, relative column addressing 38 rows below 3 and -7 columns to the left, i.e. =C42

=R38C[-7] would result in absolute row/relative column =C$38

=R[38]C7 would result in relative row/absolute column = $G42

=R38C7 would result in absolute row/absolute column =$G$38

Note if the number is omitted, it is assumed to be [0], so =RC7 would result in $G4 and =R38C would result in =J$38

 

[Cindy Ellis]

I guessed (correctly) that you were in column J when you entered the formula and got the relative reference, since column J is column 10 and 10 - 7 is 3, which is column C. (I knew the cursor couldn't have been in A1 when the formula was recorded, because a relative cell reference can't refer to a negative column number).
I initially guessed (incorrectly) that the cursor was in J3, which would have resulted in references to C41 (row 3+38) and C53 (row 3 + 50). I left it at 38 and 50 specifically for discussion
Confusing, isn't it...but sometimes relative references are really useful.
Hope that helps,
Cindy

 

[Cindy Ellis]

Just read Mikeriskson's answer...much more clear than mine...
Thanks!

 

[DonAndress]

Thank you guys, really appreciate this.

But then, how can I change below code (Abs part) using R1C1 notation:

If Abs(B1-J1) <= 1 Then
    Range("C1").Select
    Selection = "OK"
Else
    Range("C1").Select
    Selection = "** Check ES **"
End If

 

[mikerickson]

Are you looking for

Range("C1").FormulaR1C1 = "=IF(ABS(RC2-RC10)<=1, ""OK"", ""not"")"

Which would result in =IF(ABS($B1-$J1), "OK", "Not")

or

Range("C1").FormulaR1C1 = "=IF(ABS(RC[-1]-RC[7])<=1, ""OK"", ""not"")"

which would result in =IF(ABS(B1-J1), "OK", "Not")

 

[DonAndress]

Thanks a lot, mikerickson, but that results with pasting exactly the formula.
I would prefer value only.

 

[Cindy Ellis]

To put the value instead of the formula, you wouldn't use relative references (they're applicable to formulas in cells).
The code would be:

If Abs(Range("B1") - Range("J1")) <= 1 Then
    Range("C1") = "OK"
Else
    Range("C1") = "** Check ES **"
End If

Cindy​