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

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

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

..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" wrote in
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

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

Hi Bob

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

Many thanks

"Bob Phillips" wrote:

..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" wrote in
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

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

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

"steven.holloway" wrote:

Hi Bob

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

Many thanks

"Bob Phillips" wrote:

..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" wrote in
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

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

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)

"steven.holloway" wrote in
message ...
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

"steven.holloway" wrote:

Hi Bob

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

Many thanks

"Bob Phillips" wrote:

..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" wrote in
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

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

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

Many thanks

"Bob Phillips" wrote:

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)

"steven.holloway" wrote in
message ...
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

"steven.holloway" wrote:

Hi Bob

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

Many thanks

"Bob Phillips" wrote:

..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" wrote in
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