| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
R1C1 and A1 style conflict in formula problem
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))" |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
R1C1 and A1 style conflict in formula problem
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
R1C1 and A1 style conflict in formula problem
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
R1C1 and A1 style conflict in formula problem
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
R1C1 and A1 style conflict in formula problem
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
R1C1 and A1 style conflict in formula problem
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! "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 -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
R1C1 and A1 style conflict in formula problem
I screwed up my variables (somecell and test), but I hope you got my point.
Dave Peterson wrote: 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! "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 -- Dave Peterson -- Dave Peterson |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
R1C1 and A1 style conflict in formula problem
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" wrote: I screwed up my variables (somecell and test), but I hope you got my point. Dave Peterson wrote: 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! "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 -- Dave Peterson -- Dave Peterson |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
R1C1 and A1 style conflict in formula 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" wrote: I screwed up my variables (somecell and test), but I hope you got my point. Dave Peterson wrote: 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! "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 -- Dave Peterson -- Dave Peterson |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
R1C1 and A1 style conflict in formula problem
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. mikeb wrote: 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" wrote: I screwed up my variables (somecell and test), but I hope you got my point. Dave Peterson wrote: 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! "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 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Using INDIRECT & R1C1 Ref style | Excel Worksheet Functions | |||
| Sum Indirect Using R1C1 Style | Excel Worksheet Functions | |||
| How do I change sheet notation from R1C1 style to A1 style in XL 2 | Setting up and Configuration of Excel | |||
| How to use/evaluate another Cell Formula in R1C1 style | Excel Programming | |||
| can a1 reference style and r1c1 style be used in same formula? | Excel Worksheet Functions | |||
Linear Mode
