Define Range Name with variable row value
 

I'm trying to define a name range using variable rows but somehow the
range name does not get calculated.
I currently have this code which works with fixed ranges.

What to do to use variable rows?

'Deploy Formula on both sheets to find data differences
Dim lastrowquery As Integer
Dim lastrowreport As Integer
lastrowquery = Sheets("sheet1").Range("C1").End(xlDown).Row
lastrowreport = Sheets("sheet2").Range("C6").End(xlDown).Row
'Assign Range Names before Formulas
Range("C1:C" & lastrowquery).Select
ActiveWorkbook.Names.Add Name:="rangename", RefersToR1C1:= _
"='sheet1'!R1C3:R348C3"
Sheets("sheet2").Select
Range("J6").Select
ActiveCell.FormulaR1C1 = "=ISNA(MATCH(RC[-7],rangename,FALSE))"
Selection.AutoFill Destination:=Range("J6:J" & lastrowreport),
Type:=xlFillDefault

Suggestions greatly appreciated...

Thanks


--
wayliff
------------------------------------------------------------------------
wayliff's Profile: http://www.excelforum.com/member.php...o&userid=29860
View this thread: http://www.excelforum.com/showthread...hreadid=502040

Define Range Name with variable row value
 

Hi,
Try:

ActiveWorkbook.Names.Add Name:="rangename", RefersToR1C1:= _
"='sheet1'!R1C3:R" & lastrowquery & "C3"


HTH

"wayliff" wrote:


I'm trying to define a name range using variable rows but somehow the
range name does not get calculated.
I currently have this code which works with fixed ranges.

What to do to use variable rows?

'Deploy Formula on both sheets to find data differences
Dim lastrowquery As Integer
Dim lastrowreport As Integer
lastrowquery = Sheets("sheet1").Range("C1").End(xlDown).Row
lastrowreport = Sheets("sheet2").Range("C6").End(xlDown).Row
'Assign Range Names before Formulas
Range("C1:C" & lastrowquery).Select
ActiveWorkbook.Names.Add Name:="rangename", RefersToR1C1:= _
"='sheet1'!R1C3:R348C3"
Sheets("sheet2").Select
Range("J6").Select
ActiveCell.FormulaR1C1 = "=ISNA(MATCH(RC[-7],rangename,FALSE))"
Selection.AutoFill Destination:=Range("J6:J" & lastrowreport),
Type:=xlFillDefault

Suggestions greatly appreciated...

Thanks


--
wayliff
------------------------------------------------------------------------
wayliff's Profile: http://www.excelforum.com/member.php...o&userid=29860
View this thread: http://www.excelforum.com/showthread...hreadid=502040

Define Range Name with variable row value
 

I feel pretty stupid since I tried that already but was missing one "
quotation.

Thanks for your help...made my hour.


--
wayliff
------------------------------------------------------------------------
wayliff's Profile: http://www.excelforum.com/member.php...o&userid=29860
View this thread: http://www.excelforum.com/showthread...hreadid=502040