R1C1 and A1 style conflict in formula problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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))"
 
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.
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))"
Click to expand...
 
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 said:
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.
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))"
Click to expand...
Click to expand...
 
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"


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 said:
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.
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))"
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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"


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 said:
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))"
Click to expand...
Click to expand...
Click to expand...
 
When I want to use ranges, I'll let Excel and VBA help me.

Dim myRng as range
With Sheets(CFPBEY)
set myRng = .Range("A5",.cells(intuniquetrdesc,"B"))
end with

Then I can use that variable in my =vlookup() formula and do what I want...

Kind of like:

Dim myAddr As String
myAddr = myrng.address(rowabsolute:=true, columnabsolute:=true, _
referencestyle:=xla1, external:=true)

Then
dim Test as string
somecell = "=vlookup(a2," & myaddr & ",2,false)"

And I can use those arguments to specify what I need.







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 said:
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"


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!

:

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))"
Click to expand...
Click to expand...
Click to expand...
 
I screwed up my variables (somecell and test), but I hope you got my point.

Dave said:
When I want to use ranges, I'll let Excel and VBA help me.

Dim myRng as range
With Sheets(CFPBEY)
set myRng = .Range("A5",.cells(intuniquetrdesc,"B"))
end with

Then I can use that variable in my =vlookup() formula and do what I want...

Kind of like:

Dim myAddr As String
myAddr = myrng.address(rowabsolute:=true, columnabsolute:=true, _
referencestyle:=xla1, external:=true)

Then
dim Test as string
somecell = "=vlookup(a2," & myaddr & ",2,false)"

And I can use those arguments to specify what I need.
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 said:
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!

:

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))"
Click to expand...
Click to expand...
Click to expand...
 
very cool Dave, never tried that before but will from now on, you've been a
great help, thanks a lot!! I'm actually posting a question about Find and
Find Next, maybe you'll have time to take a look. Thanks again!

Mike

Dave Peterson said:
I screwed up my variables (somecell and test), but I hope you got my point.

Dave said:
When I want to use ranges, I'll let Excel and VBA help me.

Dim myRng as range
With Sheets(CFPBEY)
set myRng = .Range("A5",.cells(intuniquetrdesc,"B"))
end with

Then I can use that variable in my =vlookup() formula and do what I want...

Kind of like:

Dim myAddr As String
myAddr = myrng.address(rowabsolute:=true, columnabsolute:=true, _
referencestyle:=xla1, external:=true)

Then
dim Test as string
somecell = "=vlookup(a2," & myaddr & ",2,false)"

And I can use those arguments to specify what I need.
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!

:

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!

:

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))"
Click to expand...
Click to expand...
Click to expand...
 
BTW, I'm getting some weird behaivour, I'm going as far as qualifying all
cell and range code with Thisworkbook.sheets(x) to make sure the macro runs
fine with more than one WB open, but I keep getting that 1004 error unless I
write sheets(x).select first. Now, I believe that isn't needed, I almost
never use select. Any ideas? Not very important but a pain in the neck.
Thanks!

Mike

Dave Peterson said:
I screwed up my variables (somecell and test), but I hope you got my point.

Dave said:
When I want to use ranges, I'll let Excel and VBA help me.

Dim myRng as range
With Sheets(CFPBEY)
set myRng = .Range("A5",.cells(intuniquetrdesc,"B"))
end with

Then I can use that variable in my =vlookup() formula and do what I want...

Kind of like:

Dim myAddr As String
myAddr = myrng.address(rowabsolute:=true, columnabsolute:=true, _
referencestyle:=xla1, external:=true)

Then
dim Test as string
somecell = "=vlookup(a2," & myaddr & ",2,false)"

And I can use those arguments to specify what I need.
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!

:

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!

:

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))"
Click to expand...
Click to expand...
Click to expand...
 
Make sure each reference to a range is fully qualified.

with sheets(....)
.range(.cells(...), .cells(...)).clearcontents
end with

Note the dots in front of the .range() and .cells().

If this doesn't help, post the current code and indicate the line that causes
the problem.
BTW, I'm getting some weird behaivour, I'm going as far as qualifying all
cell and range code with Thisworkbook.sheets(x) to make sure the macro runs
fine with more than one WB open, but I keep getting that 1004 error unless I
write sheets(x).select first. Now, I believe that isn't needed, I almost
never use select. Any ideas? Not very important but a pain in the neck.
Thanks!

Mike

Dave Peterson said:
I screwed up my variables (somecell and test), but I hope you got my point.

Dave said:
When I want to use ranges, I'll let Excel and VBA help me.

Dim myRng as range
With Sheets(CFPBEY)
set myRng = .Range("A5",.cells(intuniquetrdesc,"B"))
end with

Then I can use that variable in my =vlookup() formula and do what I want...

Kind of like:

Dim myAddr As String
myAddr = myrng.address(rowabsolute:=true, columnabsolute:=true, _
referencestyle:=xla1, external:=true)

Then
dim Test as string
somecell = "=vlookup(a2," & myaddr & ",2,false)"

And I can use those arguments to specify what I need.

mikeb wrote:

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!

:

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!

:

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))"
Click to expand...
Click to expand...
Click to expand...
 
Hi Mike,

I am writing to check if you still have any concern on this issue?
If so, please feel free to post here.
Thanks!

Best regards,

Perter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Run Time errot 1004, with formula 3
Problem with validation deletion/modification 4
Why are the two codes below not getting the right formula for myworksheet event change formula? 2
Macro autofills formula when a cell value is changed 4
Issue creating a furmula in VBA, Please Help 3
UserForm R1C1 problem 4
Macro to search range for a portion of a specific value and if findit - then paste formula in anothe 1
File Name changes with dates and Deleting Rows of Zeros 0

Back
Top