Relative reference in VB with If Then
 

Since this is occuring in thousands of rows I would like to automate it with
a macro and leave the formula in a cell that can be modified (if need be) by
the user. So, say it looks like this:
For x = 21 To 2500
If Cells(x, 6) Cells(19,6) Then
Cells(x, 15).Formula = Cells(19,15).Formula
flag = 1
End If
The formula in row19 column15 is this: =IF(AC190,0,IF(D19<"
",IF(B19="X",VLOOKUP(D19,'2006 IP Template'!B:C,2,FALSE),IF(D19<"
",IF(B19="y",VLOOKUP(D19,'2006 IP Template'!B:C,2,FALSE),IF(D19<"
",IF(B19="Z",VLOOKUP(D19,'2006 IP Template'!B:C,2,))))))))
My PROBLEM is I don't know how to make this formula copy down the rows as a
RELATIVE CELL REFERENCE. What code would make this formula change to refer
to row 21 and so on as it gets pasted down?

Your help is most appreciated!

Relative reference in VB with If Then
 

Bill, if '19' only occurs where there is a row in the formula simply do a
replace first:

Cells(x,15).Formula = Replace(Cells(19,15).Formula,19,x)

--
Charles Chickering

"A good example is twice the value of good advice."


"bill ch" wrote:

Since this is occuring in thousands of rows I would like to automate it with
a macro and leave the formula in a cell that can be modified (if need be) by
the user. So, say it looks like this:
For x = 21 To 2500
If Cells(x, 6) Cells(19,6) Then
Cells(x, 15).Formula = Cells(19,15).Formula
flag = 1
End If
The formula in row19 column15 is this: =IF(AC190,0,IF(D19<"
",IF(B19="X",VLOOKUP(D19,'2006 IP Template'!B:C,2,FALSE),IF(D19<"
",IF(B19="y",VLOOKUP(D19,'2006 IP Template'!B:C,2,FALSE),IF(D19<"
",IF(B19="Z",VLOOKUP(D19,'2006 IP Template'!B:C,2,))))))))
My PROBLEM is I don't know how to make this formula copy down the rows as a
RELATIVE CELL REFERENCE. What code would make this formula change to refer
to row 21 and so on as it gets pasted down?

Your help is most appreciated!

Relative reference in VB with If Then
 

"Wow wowwy wow wow!!" Thanks this is nice and simple and works perfect.
Absolutely, a good example. Can't thank you enough.

"Charles Chickering" wrote:

Bill, if '19' only occurs where there is a row in the formula simply do a
replace first:

Cells(x,15).Formula = Replace(Cells(19,15).Formula,19,x)

--
Charles Chickering

"A good example is twice the value of good advice."


"bill ch" wrote:

Since this is occuring in thousands of rows I would like to automate it with
a macro and leave the formula in a cell that can be modified (if need be) by
the user. So, say it looks like this:
For x = 21 To 2500
If Cells(x, 6) Cells(19,6) Then
Cells(x, 15).Formula = Cells(19,15).Formula
flag = 1
End If
The formula in row19 column15 is this: =IF(AC190,0,IF(D19<"
",IF(B19="X",VLOOKUP(D19,'2006 IP Template'!B:C,2,FALSE),IF(D19<"
",IF(B19="y",VLOOKUP(D19,'2006 IP Template'!B:C,2,FALSE),IF(D19<"
",IF(B19="Z",VLOOKUP(D19,'2006 IP Template'!B:C,2,))))))))
My PROBLEM is I don't know how to make this formula copy down the rows as a
RELATIVE CELL REFERENCE. What code would make this formula change to refer
to row 21 and so on as it gets pasted down?

Your help is most appreciated!