Problem changing R1C1 code to refer to specific, asbolute range

[MichaelMcF]

Hi guys, I hope someone can help me here.

I have a spreadsheet which has, in column B, a whole host of Temperature readings. What I'm doing is tracking the change in Temperature (dT) from one row to the next and, in column O, I want to put 0 if dT < 5 and 1 if dT >5 e.g. O4 = IF((B4-B3)>5,1,0).

This is in a macro because the number of rows will vary from time to time. I already have a pretty decent code to do this which is here:

Please Login or Register  to view this content.

(Contained within a With statement in a larger code).

The problem I have is that my boss wants others to be able to follow my (not complex) code and doesn't want the relative R1C1 format, preferring that I use absolute references instead. I've gotten used to R1C1 since I started playing around with code (not long ago) so I'm a little stuck. The code I thought would work is this:

Please Login or Register  to view this content.

The code runs without errors but doesn't apply correctly. The cells which are given a value of 1 seem quite arbitrary. Is there something I'm doing wrong?

Cheers,
Michael

[shg]

I think you could make your formula a little more legible, shorter, and faster, rather than changing to A1 notation:

Please Login or Register  to view this content.

[MichaelMcF]

Hi shg,

My boss is, for reasons best known to him, still unhappy with the R1C1 format... but he seems happy enough with the cleaner version of the code Not only that but you've cleared up a little bit of confusion I had around R1C1 AND given me a hint about cleaning up another section of my code. Thank you indeed!

Regards,
Michael

[shg]

You're welcome.

Inserting formulas with A1 notation via VBA usually involves a bunch of unnecessary catenations. If R1C1 notation didn't already exist in the user interface, it would have been invented for VBA.

Tell him he's not too old to learn useful new things.