Macro - Formula RC[?] with ? being a variable number

[steven.holloway]

I am having a few problems trying to reference a variable column in a;
..FormulaR1C1 ="=RC[?]" or should it be .Formula="=RC[?]" not sure on
difference.
either way the ? needs to be replaced with the column count offset, this
would be easy if the column was fixed, but it will vary from the source.

The column needed should always be equal to say;
SpecialCells(xlLastCell).Column -1
but I am not sure if this can be used within the RC[?] formula or if you can
set up a name which equals this column number and reference the name in the
RC[?] formula.

The extract of my macro is below and any help would be great.

New_Start_cell = "B4"
Opening_Row_Count = 5

With Sheets("Pivot")
New_Last_Row = .Range(New_Start_cell).End(xlDown).Row
Set Rank_Range = .Range(.Range(New_Start_cell).Offset(1, -1),
..Cells(New_Last_Row - 1, "A"))
End With

For Each Cell In Rank_Range
With Sheets("Pivot")
.Range("A" & Opening_Row_Count).FormulaR1C1 =
"=RANK(RC[?],Rank_Range)"
Opening_Row_Count = Opening_Row_Count + 1
End With
Next Cell

 

[Bob Phillips]

..FormulaR1C1 ="=RC[" & SpecialCells(xlLastCell).Column -1 & "]"


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[steven.holloway]

Hi Bob

I tried this but got a compile error sub or function not defined and it
highlights the SpecialCells from below?

Many thanks

..FormulaR1C1 ="=RC[" & SpecialCells(xlLastCell).Column -1 & "]"


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

I am having a few problems trying to reference a variable column in a;
.FormulaR1C1 ="=RC[?]" or should it be .Formula="=RC[?]" not sure on
difference.
either way the ? needs to be replaced with the column count offset, this
would be easy if the column was fixed, but it will vary from the source.

The column needed should always be equal to say;
SpecialCells(xlLastCell).Column -1
but I am not sure if this can be used within the RC[?] formula or if you
can
set up a name which equals this column number and reference the name in
the
RC[?] formula.

The extract of my macro is below and any help would be great.

New_Start_cell = "B4"
Opening_Row_Count = 5

With Sheets("Pivot")
New_Last_Row = .Range(New_Start_cell).End(xlDown).Row
Set Rank_Range = .Range(.Range(New_Start_cell).Offset(1, -1),
.Cells(New_Last_Row - 1, "A"))
End With

For Each Cell In Rank_Range
With Sheets("Pivot")
.Range("A" & Opening_Row_Count).FormulaR1C1 =
"=RANK(RC[?],Rank_Range)"
Opening_Row_Count = Opening_Row_Count + 1
End With
Next Cell

 

[steven.holloway]

Added the dot in front and now have a new run-time error '438' object doesn't
support this property or method and this time highlights the whole row of code

Thanks
Steve

Hi Bob

I tried this but got a compile error sub or function not defined and it
highlights the SpecialCells from below?

Many thanks

..FormulaR1C1 ="=RC[" & SpecialCells(xlLastCell).Column -1 & "]"


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

I am having a few problems trying to reference a variable column in a;
.FormulaR1C1 ="=RC[?]" or should it be .Formula="=RC[?]" not sure on
difference.
either way the ? needs to be replaced with the column count offset, this
would be easy if the column was fixed, but it will vary from the source.

The column needed should always be equal to say;
SpecialCells(xlLastCell).Column -1
but I am not sure if this can be used within the RC[?] formula or if you
can
set up a name which equals this column number and reference the name in
the
RC[?] formula.

The extract of my macro is below and any help would be great.

New_Start_cell = "B4"
Opening_Row_Count = 5

With Sheets("Pivot")
New_Last_Row = .Range(New_Start_cell).End(xlDown).Row
Set Rank_Range = .Range(.Range(New_Start_cell).Offset(1, -1),
.Cells(New_Last_Row - 1, "A"))
End With

For Each Cell In Rank_Range
With Sheets("Pivot")
.Range("A" & Opening_Row_Count).FormulaR1C1 =
"=RANK(RC[?],Rank_Range)"
Opening_Row_Count = Opening_Row_Count + 1
End With
Next Cell

 

[Bob Phillips]

Sorry Steven, I am being thick.

You have to SpecialCells a range, like

..FormulaR1C1 ="=RC[" & rng.SpecialCells(xlLastCell).Column -1 & "]"

I am not sure from your post what range you are looking into to get the Last
Cell, but presumably it is something like Rank_Range, so maybe

..FormulaR1C1 ="=RC[" & Rank_Range.SpecialCells(xlLastCell).Column -1 & "]"



--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

Added the dot in front and now have a new run-time error '438' object
doesn't
support this property or method and this time highlights the whole row of
code

Thanks
Steve

Hi Bob

I tried this but got a compile error sub or function not defined and it
highlights the SpecialCells from below?

Many thanks

..FormulaR1C1 ="=RC[" & SpecialCells(xlLastCell).Column -1 & "]"


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

message I am having a few problems trying to reference a variable column in a;
.FormulaR1C1 ="=RC[?]" or should it be .Formula="=RC[?]" not sure on
difference.
either way the ? needs to be replaced with the column count offset,
this
would be easy if the column was fixed, but it will vary from the
source.

The column needed should always be equal to say;
SpecialCells(xlLastCell).Column -1
but I am not sure if this can be used within the RC[?] formula or if
you
can
set up a name which equals this column number and reference the name
in
the
RC[?] formula.

The extract of my macro is below and any help would be great.

New_Start_cell = "B4"
Opening_Row_Count = 5

With Sheets("Pivot")
New_Last_Row = .Range(New_Start_cell).End(xlDown).Row
Set Rank_Range = .Range(.Range(New_Start_cell).Offset(1, -1),
.Cells(New_Last_Row - 1, "A"))
End With

For Each Cell In Rank_Range
With Sheets("Pivot")
.Range("A" & Opening_Row_Count).FormulaR1C1 =
"=RANK(RC[?],Rank_Range)"
Opening_Row_Count = Opening_Row_Count + 1
End With
Next Cell

 

[steven.holloway]

Thanks Bob, from your guidance I have managed to work out a solution.

Many thanks

Sorry Steven, I am being thick.

You have to SpecialCells a range, like

..FormulaR1C1 ="=RC[" & rng.SpecialCells(xlLastCell).Column -1 & "]"

I am not sure from your post what range you are looking into to get the Last
Cell, but presumably it is something like Rank_Range, so maybe

..FormulaR1C1 ="=RC[" & Rank_Range.SpecialCells(xlLastCell).Column -1 & "]"



--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

Added the dot in front and now have a new run-time error '438' object
doesn't
support this property or method and this time highlights the whole row of
code

Thanks
Steve

Hi Bob

I tried this but got a compile error sub or function not defined and it
highlights the SpecialCells from below?

Many thanks

:

..FormulaR1C1 ="=RC[" & SpecialCells(xlLastCell).Column -1 & "]"


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

message I am having a few problems trying to reference a variable column in a;
.FormulaR1C1 ="=RC[?]" or should it be .Formula="=RC[?]" not sure on
difference.
either way the ? needs to be replaced with the column count offset,
this
would be easy if the column was fixed, but it will vary from the
source.

The column needed should always be equal to say;
SpecialCells(xlLastCell).Column -1
but I am not sure if this can be used within the RC[?] formula or if
you
can
set up a name which equals this column number and reference the name
in
the
RC[?] formula.

The extract of my macro is below and any help would be great.

New_Start_cell = "B4"
Opening_Row_Count = 5

With Sheets("Pivot")
New_Last_Row = .Range(New_Start_cell).End(xlDown).Row
Set Rank_Range = .Range(.Range(New_Start_cell).Offset(1, -1),
.Cells(New_Last_Row - 1, "A"))
End With

For Each Cell In Rank_Range
With Sheets("Pivot")
.Range("A" & Opening_Row_Count).FormulaR1C1 =
"=RANK(RC[?],Rank_Range)"
Opening_Row_Count = Opening_Row_Count + 1
End With
Next Cell