Free Microsoft Excel 2013 Quick Reference

Setting named range in VBA- how to set as formula/reference instead of text string?

I want to toggle between two ranges (i.e. reset the same named range to
either option, depending on user input).

The named ranges are used by graphs, and show either (option 1) a baseline
data point and 12 months of current data, or
(option 2) 24 months of data.

I tried doing this change on the worksheet and ran into big problems, and
decided I should also look at setting these in VBA and
see if it would be easier. The problem is that when I go back into the
named range, it shows the correct "formula" but
it is returned as a string instead of a reference, e.g.
="OH2!$C$49,OH2!$C$20:$C$31"
instead of
=OH2!$C$49,OH2!$C$20:$C$31

Any/all help greatly appreciated,
Keith R
XL97

Here's what I've got so far:
----------------------------------------------------------------------------
-----------------------------
"Active" is a named range =VLOOKUP(Info!$B$2,Info!$B$4:$C$12,2), and works
properly-
it returns a sheet name, for example, OH2, GMC2, etc.
----------------------------------------------------------------------------
-----------------------------
Public Function ChangeGraph(GType As Range, ActiveSite As Range)
'function can't be put in put in the same cell as Gtype, which is fine

If GType.Value = 1 Then
ActiveWorkbook.Names.Add Name:="Graph01_A", _
RefersTo:=Evaluate(Names("active").Value) & "!$C$49," & _
Evaluate(Names("active").Value) & "!$C$20:$C$31"
ActiveWorkbook.Names.Add Name:="GraphsXAxis", _
RefersTo:=Evaluate(Names("active").Value) & "!$A$7," & _
Evaluate(Names("active").Value) & "!$A$20:$A$31"

ElseIf GType.Value = 2 Then
ActiveWorkbook.Names.Add Name:="Graph01_A", _
RefersTo:=Evaluate(Names("active").Value) & "!$C$8:$C$31"
ActiveWorkbook.Names.Add Name:="GraphsXAxis", _
RefersTo:=Evaluate(Names("active").Value) & "!$A$8:$A$31"

Else
MsgBox "Only values of 1 or 2 can be accepted in ChangeGraph
Function", , "Error: Value Out Of Range"
End If

'I don't set Gtype back to a value so it returns zero- which is
'fine because I just use it to trigger the named range change
'while avoiding the volatile issue

End Function


Post your answer or comment

comments powered by Disqus
Does anyone know how to return the reference of a cell instead of it's value?
Specifically my problem is as follows:
In my spreadsheet, cell O4 references and returns the value from cell C4. In cell P4, I want to return the value from the cell that's one row down and one column left of cell C4. So I need cell P4 to call the cell reference instead of the cell value of C4, then manipulate the cell location to go one down and one to the left, and return that cell's value .. which should be the value of cell B5 = 18. So, cell P4 should return 18. Is there a way to do this?

I'm trying to define a named range in VBA using the following syntax.
However, it gives me an error regarding the equal sign and if I remove the
equal sign, then the named range is defined but it treats the formula as a
text value so it doesn't evaluate it. I'm sure there's something easy that
I'm overlooking but what is it?

Any help is appreciated.

ActiveWorkbook.Names.Add Name:="PivotRange", RefersToR1C1:= _
"=OFFSET(Sheet1!$A$1,0,0,COUNTA(Shee1!A:A),10)'"

--
Richard

Hi-

I'm looking to create a named range based on certain values from a column. For example, with this array:

1 Run Tom Judy James
1 Run Tom Judy James
1 Run Tom Judy James
2 Walk Tom James
2 Walk Tom James
2 Walk Tom James
3 Sit James
3 Sit James
3 Sit James
3 Sit James
4 Stand Judy James
4 Stand Judy James
4 Stand Judy James

From this data, I would like to create three named ranges in VBA:

A list including one instance of each row which contains TomA list including one instance of each row which contains JudyA list including one instance of each row which contains JamesTom's list:

1 Run Tom Judy James
2 Walk Tom James

Judy's list:

1 Run Tom Judy James
4 Stand Judy James

James' list:

1 Run Tom Judy James
2 Walk Tom James
3 Sit James
4 Stand Judy James

Is this possible? These lists could be very long (10-15k lines).
It doesn't necessarily have to be a named range, It would be acceptable if these lists were copied into their own sheets.

Thanks!
-Aaron

How do I refer to a named range in VBA.

I have a Range named "Parts" I use for a ComboBox list.

How can I refer to the selected item by the range name or is it easier to refer to the ComboBox selection in VBA.

I want to past the item selected in the ComboBox into another sheet for a parts list.

Thanks!!

Hi All

Is there a way of defining dynamic named ranges in VBA (Excel 2003)? I have an application which heavily uses named ranges, which was seriously bloated. When I exported it to a new workbook to clean it, I found that the dynamic named ranges have all been lost!! Any solutions or ideas?

Many thanks

iFixem

I have a macro that is called each time a different sheet in the
workbook is selected. The macro is supposed to define the determined
range as a named ramge, "FilterRange".

Sub CtyShtFltRng()
Dim lCol as Long
Dim lRow As Long
Dim wActSheet As Worksheet
Set wActSheet = ActiveSheet

Set rStart = Range("B3")
lRow = rStart.End(xlDown).Row - 1
lCol = rStart.End(xlToRight).Column
Set rCtyShtFltRng = Range(rStart, Cells(lRow, lCol))
Set wActSheet.Names("FilterRange") = rCtyShtFltRng <---ERROR
wActSheet.Range("FilterRange").Select

End Sub

I'm getting an "wrong number of arguments or invlalid property
assignment" at the marked line. I've tried about 10 or 12 variations on
this line, but none worked. Can someone tell me how to make this work?
Thanks!

Greetings.

Excel 97, Windows 98.

I made several ranges and named them. I pasted the names into the spreadsheet. I have set up a combo box to allow the user to sleect which name they want to work with. The combo box is linked to a cell. I now want to use offset and match to evaluate so I can use index to get data on the named range based on other inputs. The named range is put in cell A15. If I put A15 in the offset formula, it naturally goes to cell a15. If I put in T(a15) it gives an error. I also trued to use concatenate. No success. Any ideas? If I put in the name of the range, I get the right answer.

I have a financial forecasting Excel 2007 template. The template has 35 tabs, two for each cost center the managers are forecasting for. Example naming convention is "12345" and "12345 Cust Vol". In worksheet "12345" there is a range of cells that calculate the estimated revenue, the formula is "=($I103*'12345 Cust Vol'!E47)*12". $I103 is the Rate and "'12345 Cust Vol'!E47" is the number of units to be sold.

When the spreadsheet was created both sheets were copied to create a set of worksheets for 9 cost centers. The formula on the other 9 tabs calculating revenue are referring to the original spreadsheets name "12345 Cust Vol". I need to update these formulas on all 9 worksheets for about 50 workbooks. Each workbook the worksheets have been renamed to the cost center it is for, the order in which the spreadsheets are in, is the same across workbooks.

I am looking for the best way to update these formulas without having to touch each worksheet in each workbook.

Example:

Wookbook 1 has the following sheets in this order. 1234512345 Cust Vol2345623456 Cust Vol3456734567 Cust VolWorkbook 2 has the following sheets in this order. 1111111111 Cust Vol2222222222 Cust Vol3333333333 Cust Vol4444444444 Cust VolI need the VBA macro to set Workbook 1
Worksheet 12345 Formula needs to reference worksheet 12345 Cust VolWorksheet 23456 formula needs to reference worksheet 23456 Cust VolWorksheet 34567 formula needs to reference worksheet 34567 Cust VolAnd so on for all worksheets and workbooks.

Thank you for any guidance.

How do I use a Named Range in the code below?
Column "M" is the named range, "ExpiredCerts", note how the sheet range starts in row 7.
I am trying to avoid changing column letters as I add or remove columns.
           
With Sheets(1)
        ShLastRow = .Cells(Rows.Count, "M").End(xlUp).Row
        Set ShRange = .Range("M7:M" & ShLastRow)
End With


hi all,
I am developing a excel tool and during the developement I need to
move/copy sheets from a earlier version workbook to the latest version
workbook (latest version workbook has new code in it).
I am using named ranges in a sheet called "settings" and value from
these range is used in the macros/VBA code to do a lot of things.
NOw my problem is that when copy sheets from earlier version workbook
to the latest version workbook, I keep getting the message, "A formula
or sheet you want to move or copy contains the name [some name], which
already exists on the destination worksheet. do you want to use name
from destination file and bla-2", when I copy more than one sheet at a
time. but if i copy one sheet at a time to latest version workbook
there's no porblem. and if i say no to this msg, then for every named
cell i need to give a new name and if press yes for all the named
cells, then there's no longer any named cell in the copied sheets!

can someone help?
TIA
amit

hi all,
I am developing a excel tool and during the developement I need to
move/copy sheets from a earlier version workbook to the latest version
workbook (latest version workbook has new code in it).
I am using named ranges in a sheet called "settings" and value from
these range is used in the macros/VBA code to do a lot of things.
NOw my problem is that when copy sheets from earlier version workbook
to the latest version workbook, I keep getting the message, "A formula
or sheet you want to move or copy contains the name [some name], which
already exists on the destination worksheet. do you want to use name
from destination file and bla-2", when I copy more than one sheet at a
time. but if i copy one sheet at a time to latest version workbook
there's no porblem. and if i say no to this msg, then for every named
cell i need to give a new name and if press yes for all the named
cells, then there's no longer any named cell in the copied sheets!

can someone help?
TIA
amit

I am attempting to use a dynamic named range in a chart data series as described in Excel Hacks (Hack #42 and Hack #52).

Using Insert|Name|Define I have created a named range called CashFlowSaleChartDataRange that is set to the following value:

=OFFSET('Cash Flow-Sale'!$O$10,0,0,NumFlows,1)

When I subsequently assign a reference to this named range in the data series dialog it will accept the answer and my chart will adjust to reflect the updated range. However, if I return to the data series dialog, I now see the range address returned by the OFFSET function rather than the named range reference.

What am I doing wrong?

Hi, how to set strings to = "aaa"bbb"ccc" ?
This means the string is aaa"bbb"ccc

which is the special character that must be placed?

Thanks!

I have a spreadsheet I am using for financial statements. In one area, I have
to create sub-totals based on the rows above.

The problem is that some of the cells are set up as named ranges for use
elsewhere, and some are not. As a result, the formulas for my sub-totals
contain both names and cell references. When I try to copy these formulas to
adjacent cells for other months, some formula components change (eg. C35
becomes D35) while others don't (eg. Jan_Sales stays as Jan_Sales).

This makes a complicated edit, overly prone to errors. It is also not
something I am comfortable turning over to someone else to use on an ongoing
basis.

I could create names for the un-named cells, but this would result in a HUGE
number of names to set up. Is there any way to use the cell reference for the
named cells in the formulas I am creating?

I have a dynamic named range in my sheet called "lateaccounts" (the sheet is also called "Late Accounts."


	VB:
	
 Macro1() 
    On Error Resume Next 
    ActiveWorkbook.Names("lateaccounts").Delete 
     
    ActiveWorkbook.Names.Add Name:="lateaccounts", RefersTo:= _ 
    "=OFFSET('Late Accounts'!$A$1,1,0,COUNTA('Late Accounts'!$A$1:$A$10000)-1,COUNTA('Late Accounts'!1:1)-1)" 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I want to re-define this named range. When I use the code above, the last part of the code,


	VB:
	

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
becomes


	VB:
	

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Any idea why?

Hi, is there an easy way to set a cell, for example, C35, to be the top-left cell of current screen. Thanks!

I have a sheet that has lots of named ranges. What I need to do is take
each named range and copy it to a new sheet then name the sheet using
the named range. What I dont know how to do is loop through all the
named ranges.

I can do this and it works...

Sub Copy_Range()

ActiveSheet.Range("CPG0162").Select
Selection.Copy
Sheets("Sheet7").Select
Range("A1").Select
ActiveSheet.Paste
End Sub

Thanks

Programmatically, how do I get all of the named ranges in the workbook to be
displayed on my Sheet1. I have named ranges that are workbook specific and
worksheet specific. I'd like to know the sheet that they are specific to if
they are present.

Thanks,

Hi my problem is that i want to set a same conditional formatting for more than 50 cells but it allows me to set one at a time. How to set conditional formatting for all the cells at a time ??

Is this possible?

If I have a named range that covers A1:A10, A12:A15, A17:A20, can I use code that loops through? (Specifically, I'm using a "For each variable In variablelist.... Next".)

I tried running it, and the code seems to end at A10 (last item in first segment of named range), so I'm assuming I either have to use a different type of loop, or some sort of workaround. It would be nice if these named ranges didn't have to be separated to be operated upon. (There are more segments than I listed)

Thanks!

I have a sheet that has lots of named ranges. What I need to do is take
each named range and copy it to a new sheet then name the sheet using
the named range. What I dont know how to do is loop through all the
named ranges.

I can do this and it works...

Sub Copy_Range()

ActiveSheet.Range("CPG0162").Select
Selection.Copy
Sheets("Sheet7").Select
Range("A1").Select
ActiveSheet.Paste
End Sub

Thanks

Hello all:

This probably a simple question, but I'm kind of new to this stuff...

I would like to create a named range in VBA for column A, which is a sum of B and C. The problem is that A can have 0 as a value. What I would really like to do is define the named range in column A as A2 to the last column with a value in column B(B10) which would make the named range in column a A2:A10.

A B C
615 555 60
0 0
1250 1200 50
725 725
0 0
850 850
19 19
7216 7200 16
995 995
0
0
0
0
0
0
0
0
0

Hi guys,

I have a template spreadsheet which has a macro in it that is accessed from another workbook. This template will eventially be about 30 different workbooks. I have this code:

    
    Sheets("Fundraiser").Select
    Range("B60:G261").Select
    Selection.Copy
    ActiveWindow.SmallScroll Down:=12
    Range("B263").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Fundraiser.xls").Activate
    Rows("2:2").Select
    Selection.Insert Shift:=xlDown
    Windows("AFBP_test.xls").Activate
    Application.CutCopyMode = False
    Selection.ClearContents
    ActiveWindow.SmallScroll Down:=-246
    Range("D17").Select
    ActiveWindow.SmallScroll Down:=-30
    Range("B2").Select
I need the code on the line where it selects the "AFBP_text.xls" to select a range from a page in the workbook. The range is "Scriptname" and will be in every workbook.

Obviously the scriptname range will change for each workbook, but the macro needs to run the same. So instead of manually changing which workbook it selects in the code for each new workbook I create from the template, I'm wondering if there is code to connect to this range automatically?

Thank you in advance!

Matt

Hi All,

On a once daily basis I need to import approx. ten worksheets of data (from an Access db) into an Excel workbook and analyse this data using various array formulae into a summary page. The data imported can vary between one and ten thousand rows per worksheet.

I am wondering if it is possible to use dynamic named ranges in my arrays to avoid the tedious task of manually redefining the many ranges each time I download new data.

The following is an example of one of the arrays from my summary worksheet.

=IF(COUNTIF(sales!$G$2:$G$1036,E2)>1,AVERAGE(LARGE(IF((sales!$G$2:$G$1036=$E2)*(sales!$I$2:$I$1036<7 ),sales!$H$2:$H$1036),{1,2})),0)

where :-

sales!$G range is text (names)
sales!$H range is numbers (sale values) and
sales!$I range is a count of the sales for each salesman using the formula :
=IF(ROW()=2,1,IF(G2=G1,I1+1,1))

I have the appropriate formula for defining the respective text and numerical ranges (from other posts on this forum) and have tried using the range names in the above array, without success. The array holds up when I use the range name for sales!$G in the COUNTIF calculation but when I put it in the AVERAGE formula I get an N/A! error.

Is it possible/practical to use dynamic named ranges in this manner and if so what additions/changes to syntax are necessary or would I be better advised just defining static ranges which exceed my likely requirements in each case?

Thanks in advance for any help and apologies if this post would be better suited to another forum.

Barry


No luck finding an answer? You could always try Google.