Dave, this worked, so if I understand you correctly if you do not use brakets
you will get an absolute reference but if you use brakets then it will be a
relative reference

if r5c1:r123c2= $A$5:$B$123 then what would i use to remove "$"' or is that
not possible?

thanks, again!

"Dave Peterson" wrote:

In R1C1 reference style, you'll see r[-1] to represent the previous row.

That expression has no ['s and ]'s in them.

You'd see something like:
r5c1:r123c2

Which is $A$5:$B$123.

===
But take note, this expression is wrong:

RT & "'!r5c1:r" & intUniqueTrDesc & "c2" & intUniqueTrDesc

It should be something closer to:
RT & "'!r5c1:r" & intUniqueTrDesc & "c2"



mikeb wrote:

Hi Dave,
I see how you handled the A1 style in my formula, but will it be absolute,
that is what i need, in A1 style I made it absolute because I'm not sure if
R1C1 is absolute, and in general when using R1C1 how can one change between
relative and absolute?

RT & "'!r5c1:r" & intUniqueTrDesc & "c2" & intUniqueTrDesc

this needs to be an absolute range on sheet RT(const RT = Ref Tables)

thanks!

"Dave Peterson" wrote:

How about:

with sheets(CFPBEY)
.Range(.Cells(StartRow, pbeyeydesc), _
.Cells(intNumOfTrDesc + HdrRow, pbeyeydesc)).FormulaR1C1 = test
end with

Depending on where the code is, the unqualified range (cells()) will refer to
activesheet (if the code is in a general module) or the sheet that owns the code
(if the code is in a worksheet module).

Without any testing (it did compile, though),

Option Explicit
Sub testme()
Dim test As String
Dim ColLoc1 As Long
Dim ColLoc2 As Long
Dim pbeyeydesc As Long
Dim pbeydescchg As Long
Dim pbeyorigtrdesc As Long
Dim RT As Long
Dim intUniqueTrDesc As Long
Dim intNumOfTrDesc As Long
Dim CFPBEY As String
Dim StartRow As Long
Dim HdrRow As Long


With Sheets(CFPBEY)
.Range("E11:E20000").ClearContents
ColLoc1 = pbeyeydesc - pbeydescchg
ColLoc2 = pbeyeydesc - pbeyorigtrdesc

test = "=IF(RC[-" & ColLoc1 & "]<0,VLOOKUP(RC[-" & ColLoc1 & "],'" _
& RT & "'!r5c1:r" & intUniqueTrDesc & "C2" & _
",2,FALSE),VLOOKUP(RC[-" _
& ColLoc2 & "],'" & RT & "'!r5c1:r" _
& intUniqueTrDesc & "c2" & _
intUniqueTrDesc & ",2,FALSE))"

.Range(.Cells(StartRow, pbeyeydesc), _
.Cells(intNumOfTrDesc + HdrRow, pbeyeydesc)).FormulaR1C1 = test
End With
End Sub

Since I don't know what any of those variables could be, I don't if this will
actually work.

But yep, you've got to make that formula look like R1C1 addresses.

mikeb wrote:

Hi the code below generates error 1004, i think it's because I'm mixing
formula styles. Is there any way to rewrite this formula and make it work?
thanks in advance

Sheets(CFPBEY).Select
Range("E11:E20000").ClearContents
ColLoc1 = pbeyeydesc - pbeydescchg
ColLoc2 = pbeyeydesc - pbeyorigtrdesc
Dim test As String
test = "=IF(RC[-" & ColLoc1 & "]<0,VLOOKUP(RC[-" & ColLoc1 & "],'" & RT &
"'!$A$5:$B$" & _
intUniqueTrDesc & ",2,FALSE),VLOOKUP(RC[-" & ColLoc2 & "],'" & RT &
"'!$A$5:$B$" & _
intUniqueTrDesc & ",2,FALSE))"

Sheets(CFPBEY).Range(Cells(StartRow, pbeyeydesc), Cells(intNumOfTrDesc +
HdrRow, pbeyeydesc)).Formula = test

'_
' "=IF(RC[-" & ColLoc1 & "]<0,VLOOKUP(RC[-" & ColLoc1 & "],'" & RT &
"'!$A$5:$B$" & _
' intUniqueTrDesc & ",2,FALSE),VLOOKUP(RC[-" & ColLoc2 & "],'" & RT &
"'!$A$5:$B$" & _
' intUniqueTrDesc & ",2,FALSE))"

--

Dave Peterson


--

Dave Peterson