R1C1 Row to Absolute

[gtd526]

Hello,

I'm inserting the following formula via VBA. Once inserted, I fill the cells down with the same formula.

  Range("I9").Select
    ActiveCell.FormulaR1C1 = _
    "=IFERROR(LOOKUP(2, 1/((COUNTIF(R[-1]C8:R[-1]C8, '[NBA.xlsm]Favs'!R4C9:R33C9)=0)*('[NBA.xlsm]Favs'!R4C9:R33C9<>"""")), '[NBA.xlsm]Favs'!R4C9:R33C9),"""")"

I use the [-1] to go back 1 row. The formula works perfectly, but I need to change it from Relative Row to Absolute Row, as stated below. Otherwise I have to change it manually, then fill down.

Once inserted the formula becomes:
IFERROR(LOOKUP(2, 1/((COUNTIF($H8:$H8, Favs!$I$4:$I$33)=0)*(Favs!$I$4:$I$33<>"")), Favs!$I$4:$I$33),"")

I want to change the $H8:$H8 to $H$8:$H8 when the formula is inserted.
What adjustments are needed to make the Row Absolute? Its only the 1st Row listed not both.

Thank you.

 

[kevin9999]

R8C8 = $H$8
When you enter a digit immediately after R or C it ties the reference to that particular Row (or column)

 

[gtd526]

R8C8 = $H$8
When you enter a digit immediately after R or C it ties the reference to that particular Row (or column)

it has to be R[-1] because the row # will be different each time its entered. There can't be a specific row # assigned.

 

[kevin9999]

I was only referring to the first cell of the variable range. Try changing:

R[-1]C8:R[-1]C8

to:

R8C8:R[-1]C8

 

[gtd526]

I was only referring to the first cell of the variable range. Try changing:

R[-1]C8:R[-1]C8

to:

R8C8:R[-1]C8

it cant be changed to an assigned row number. it has to stay R[-1], I'm referring to the first cell of the range.

 

[kevin9999]

So you didn't try the suggestion then?

 

[RoryA]

it cant be changed to an assigned row number

Your code is specifically assigning a formula to I9 so I don't see why you can't hardcode the 8 for the start row.

 

[gtd526]

So you didn't try the suggestion then?

it can be entered into any cell, I'm only looking for the result of the formula. the formula will be entered into a different row each time its used. the R1C1 will result in whatever Row its entered into. this is where the formula will be entered. H285, H293, H301. i will copy/paste sv once the bet is complete. H301 is active. Re: H301, I manually changed the formula to $H$300:$H300 from $H300:$H300. But I want it entered as Absolute (using the formula I listed in my first post), only the first Row only, not the second.
Hopefully this make sense.

My Wagers.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
285	W	2/29		NBA	SPRD	$ 5.00	$ 4.55	NOP	IND	-5.5	$4.55	R, S, T>0, AL:AM >= 60%
286				NBA	SPRD	$ 5.00	$ 4.55
287				NBA	SPRD	$ 5.00	$ 4.55
288				NBA	SPRD	$ 5.00	$ 4.55
289				NBA	SPRD	$ 5.00	$ 4.55
290				NBA	SPRD	$ 10.00	$ 9.10
291											$4.55
292			$97.31	Balance
293	W	3/1		NBA	SPRD	$ 5.00	$ 4.76	ORL	DET	-10.5	$4.76	R, S, T>0, AL:AM >= 60%
294				NBA	SPRD	$ 5.00	$ 4.55
295				NBA	SPRD	$ 5.00	$ 4.55
296				NBA	SPRD	$ 5.00	$ 4.55
297				NBA	SPRD	$ 5.00	$ 4.55
298				NBA	SPRD	$ 10.00	$ 9.10
299											$4.76
300			$102.07	Balance
301		3/3		NBA	SPRD	$ 5.00	$ 4.76
302				NBA	SPRD	$ 5.00	$ 4.55
303				NBA	SPRD	$ 5.00	$ 4.55
304				NBA	SPRD	$ 5.00	$ 4.55
305				NBA	SPRD	$ 5.00	$ 4.55
306				NBA	SPRD	$ 10.00	$ 9.10
307											$0.00
308			$102.07	Balance
NBA

Cell Formulas
Range	Formula
H301:H306	H301	=IFERROR(LOOKUP(2, 1/((COUNTIF($H$300:$H300, [NBA.xlsm]Favs!$I$4:$I$33)=0)*([NBA.xlsm]Favs!$I$4:$I$33<>"")), [NBA.xlsm]Favs!$I$4:$I$33),"")
I301:I306	I301	=IFERROR(IF(1=MOD(MATCH($H301,[NBA.xlsm]Favs!$A$4:$A$33,0),2),INDEX([NBA.xlsm]Favs!$A$4:$A$33,MATCH(H301,[NBA.xlsm]Favs!$A$4:$A$33,0)+1),INDEX([NBA.xlsm]Favs!$A$4:$A$33,MATCH(H301,[NBA.xlsm]Favs!$A$4:$A$33,0)-1)),"")
J301:J306	J301	=IFERROR(INDEX([NBA.xlsm]Favs!$B$4:$B$33,MATCH($H301,[NBA.xlsm]Favs!$A$4:$A$33,FALSE)),"")
A301:A306	A301	=IFERROR(INDEX([NBA.xlsm]Favs!$C$4:$C$33,MATCH($H301,[NBA.xlsm]Favs!$A$4:$A$33,FALSE)),"")
L301:L306	L301	=IFERROR(INDEX([NBA.xlsm]Favs!$H$4:$H$33,MATCH($H301,[NBA.xlsm]Favs!$A$4:$A$33,FALSE)),"")
K301:K306	K301	=IF($H301="","",(IF($A301="W",$G301,IF($A301="L",$F301*-1,IF($A301="Psh",0,IF(ISNUMBER(J301),$F301*-1,))))))
K307	K307	=SUM(K301:K306)
C308	C308	=IF(AND(LOOKUP(2,1/(Chart!F:F<>""),Chart!F:F)="NBA",INDEX(Chart!F:F,LARGE(IF(Chart!F:F<>"",ROW(Chart!F:F)),2))="NFL"),INDEX(NFL!C:C,LARGE(IF(NFL!C:C<>"",ROW(NFL!C:C)),2))+LOOKUP(2,1/(NBA!K:K<>""),NBA!K:K),$C300+$K307)
Press CTRL+SHIFT+ENTER to enter array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G301:G306	Cell Value	="W"	text	NO
G301:G306	Cell Value	="L"	text	NO
G293:G298	Cell Value	="W"	text	NO
G293:G298	Cell Value	="L"	text	NO
G285:G290	Cell Value	="W"	text	NO
G285:G290	Cell Value	="L"	text	NO
O376:O377,A324:A1048576,A1:A313	Cell Value	="W"	text	NO
K:K	Cell Value	<0	text	NO
K:K	Cell Value	>0	text	NO
P6:P12,P14:P20,P22:P28,P30:P36,P38:P44,P46:P52,P54:P60,P62:P68,P70:P76,P78:P84,P86:P92,P94:P100,P102:P108,P110:P116,P118:P124,P126:P132,P134:P140,P142:P148,P150:P156,P158:P164,P166:P172,P174:P180,P182:P188,P190:P196,P198:P204,P206:P212,P214:P220,P222:P228	Cell Value	<0.525	text	NO
O376:O377,A324:A1048576,A1:A313	Cell Value	="L"	text	NO
O376:O377,A324:A1048576,A1:A313	Cell Value	="PSH"	text	NO

 

[gtd526]

So you didn't try the suggestion then?

this formula will be listed in a different Row each time. So giving it an assigned Row will not work. The row your viewing is only a sample, it will change the next time its entered which is why the R1C1 format is used. it doesn't matter which row its enter to, it will be the row where its assigned.

 

[kevin9999]

OK, things are much clearer now, and thank you for providing your actual data layout. If you're going to use the 'active cell' as the starting point for entering your formula, then you could use a variable in your code - for example:

Dim i as Long
i = ActiveCell.Row - 1
ActiveCell.FormulaR1C1 = "=sum(R" & i & "C8:R[-1]C8)"

which gives you the following formula in cell I9 (if that was the active cell)

=SUM($H$8:$H8)