Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Define variable range input for SUM() function | Excel Worksheet Functions | |||
Define Variable Name using VBA | Excel Discussion (Misc queries) | |||
How to define variable | Excel Programming | |||
Define Object Variable | Excel Programming | |||
Define variable range | Excel Programming |