Results 1 to 4 of 4

Problem changing R1C1 code to refer to specific, asbolute range

Threaded View

  1. #1
    Registered User
    Join Date
    11-23-2011
    Location
    Scotland
    MS-Off Ver
    Excel 2010
    Posts
    15

    Problem changing R1C1 code to refer to specific, asbolute range

    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:

    '   Find dT > 5
            For i = 4 To lrow
            .Range("O" & i).FormulaR1C1 = "=IF((RC[-13]-R[-1]C[-13])>5,1,0)"
            Next i
    (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:

    '   Find dT >5
            For i = 4 To lrow
            h = i - 1
            dT = .Range("B" & i).Value - .Range("B" & h).Value
                If dT >= 5 Then .Range("O" & i).Value = 1 _
                    Else .Range("O" & i).Value = 0
            Next i
    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
    Last edited by MichaelMcF; 12-15-2011 at 12:56 PM. Reason: Problem Solved!

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1