Defining Relative Reference in separate Excel worksheet 1

[header777]

I am using MS Office Excel 2003. I want to compare the values in sheet1.cell N4 with sheet2.cell E4 using an if function in sheet1. I then want to autofill the remaining rows with the formula. I am able to do this but as the formula is carried down sheet1 in continues to reference sheet2.E4 instead of moving down that column as well.

What is the syntax to create a relative reference on the 2nd worksheet.

Here's a snippet of my code that I'm sure unveils my mediocrity.

NbRowsFinal = TheRecordsNum + 3

 'insert formula to compare SREA and QVR reports
    Range("T4").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-6]=PasteSummaryHere!R4C5,""ok"",PasteSummaryHere!R4C5)"
    Range("T4").Select
    Selection.AutoFill Destination:=Range(Cells(4, 20), Cells(NbRowsFinal, 20)), Type:=xlFillDefault

 

[Bong]

what happens if Type := xlFillCopy?

_________________
Bob Rashkin

 

[header777]

thanks for the quick reply bong. unfortunately - it doesn't change anything. all columns in sheet1 are still referencing R4C5 (cell $E$4) instead of incrementing the row number.

 

[AnotherHiggins]

It is best to avoid using Selection or Activate. Also, to make the reference to the other sheet relative while using RC notation, you'll need to refer to the cell relative to the formula-cell's location.

Try this instead:

LastRow = Range("A" & ActiveSheet.Rows.Count).End(xlUp).Row

Range("T4:T" & LastRow) = _
    "=IF(RC[-6]=PasteSummaryHere!RC[-15],""OK"",PasteSummaryHere!RC[-15])"

Note: I use "ActiveSheet.Rows.Count" because Excel 2007 has increased the number of rows from 65,536 to 1,048,576. "ActiveSheet.Rows.Count" will use the last row, no matter which row that is.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[header777]

Thanks John (anotherhiggins)! That did the trick and will be very useful in future macros. Just curious - why is it unadviseable to use selection and activate?

 

[xlbo]

There's generally no need to do so and if you do, it slows down code execution

Basically, any time you see

x.Select
Selection.DoSomething

or

x.activate
ActiveThing.DoSomething

you can get rid of the select / activate to produce a 1 liner

x.DoSomething

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question