Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using defined names in VBA...
Hi All
When writing excel formulas I use defined ranges that are named (eg "Schedule") instead of stipulating the range (eg "$AC$22:$DO$100"). How do I use defined names instead of actual ranges when writing code? I need this because the range on the excel spreadsheet keeps changing when users insert/delete rows/columns/cells and the range needs to move with the changes. Here is an example of code where the ranges would need to be replaced with the defined name... Sub Example() Sheet("Sheet2").Select Range("AC22:DO100").Select 'This is called "Schedule" Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Selection.NumberFormat = "#,##0.00" With Selection.Font .Name = "Tahoma" .FontStyle = "Regular" .Size = 10 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With Sheet("Sheet1").Select Range("A13:DY100").Select Selection.Replace What:="0", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False -- Thank for your help BeSmart |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using defined names in VBA...
Hi BeSmart,
Change your code to this: Sub Example() With Thisworkbook.Names("Schedule").ReferstoRange .Copy .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False .NumberFormat = "#,##0.00" With .Font .Name = "Tahoma" .FontStyle = "Regular" .Size = 10 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With Regards, Jan Karel Pieterse Excel MVP www.jkp-ads.com Sub Example() Sheet("Sheet2").Select Range("AC22:DO100").Select 'This is called "Schedule" Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Selection.NumberFormat = "#,##0.00" With Selection.Font .Name = "Tahoma" .FontStyle = "Regular" .Size = 10 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With Sheet("Sheet1").Select Range("A13:DY100").Select Selection.Replace What:="0", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False -- Thank for your help BeSmart |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using defined names in VBA...
Thanks for that Jan
I tested the replace functions on the second defined range in the original code and it worked beautifully. That helps heaps and teaches me something. Cheers BeSmart "Jan Karel Pieterse" wrote: Hi BeSmart, Change your code to this: Sub Example() With Thisworkbook.Names("Schedule").ReferstoRange .Copy .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False .NumberFormat = "#,##0.00" With .Font .Name = "Tahoma" .FontStyle = "Regular" .Size = 10 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With Regards, Jan Karel Pieterse Excel MVP www.jkp-ads.com Sub Example() Sheet("Sheet2").Select Range("AC22:DO100").Select 'This is called "Schedule" Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Selection.NumberFormat = "#,##0.00" With Selection.Font .Name = "Tahoma" .FontStyle = "Regular" .Size = 10 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With Sheet("Sheet1").Select Range("A13:DY100").Select Selection.Replace What:="0", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False -- Thank for your help BeSmart |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using defined names in VBA...
A late question....
In the name of protecting code, I have to save the code in a different workbook ("Masterfile") to the one it actually has to apply too ("Template"). The master workbook auto opens when the template is opened. The user clicks on a button in the "template" to active and apply the macro. When testing the code in the Masterfile, "ThisWorkbook" doesn't work because the formatting is not suppose to apply to the Masterfile - it has to happen to the current workbook selected. How do I change the code to accommodate this? Note: the name of the template will alway change, so I need to apply the macro to the "workbook currently selected" not a specific workbook name. TFYH BeSmart Here is the code again: Sub Example() With Thisworkbook.Names("Schedule").ReferstoRange .Copy .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False .NumberFormat = "#,##0.00" With .Font .Name = "Tahoma" .FontStyle = "Regular" .Size = 10 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With End With End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using defined names in VBA...
Hi BeSmart,
Note: the name of the template will alway change, so I need to apply the macro to the "workbook currently selected" not a specific workbook name. Use Activeworkbook instead of Thisworkbook Regards, Jan Karel Pieterse Excel MVP www.jkp-ads.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Why use defined names? | Excel Discussion (Misc queries) | |||
defined names | Excel Worksheet Functions | |||
Defined names | Excel Worksheet Functions | |||
NAMES DEFINED | Excel Discussion (Misc queries) | |||
Defined names | Excel Discussion (Misc queries) |