Define Sheet Level Name Range Code

Dec 3, 2007

In general defining Sheet Level Name Ranges works fine with me, most of the times I use something like shown in the code below.

However, I have discovered that apparently the successfull execution depends on the sheet-name format itself

The code below works perfectly fine with worksheet names like "234", "cas422", etc
but it fails to work with worksheet names like: "C12c", "eh34h4". It seems to me - but I am not sure of being able to generalize this - as if it works when the name contains [numbers][letters] or [letter][numbers], but it fails when it contains a mixture of both like [numbers][letters][numbers] or [letters][numbers][letters] .... in case of a failure of the code, I go into debug mode and simply rename the sheet, then everything's fine again.

Am I the only one to experience such difficulties? (I run Excel 2003 with SP3 on Windows XP) Or would a different code prevent this kind of trouble?

I think, I could write a workround, but before doing so I'd rather like to ask if I am doing something wrong here?


ActiveWorkbook.Names.Add name:=wks.name & "!myTime", RefersToR1C1:="=" & wks.name & "!R1C1:R5C8"

View 9 Replies


ADVERTISEMENT

Define Worksheet Level Names

Oct 26, 2009

I was trying to re-create an example from Chapter 4 the 2nd Edition of Professional Excel Development in which there is a data validation list,
that is conditionally populated according to the selection of another data validation list.

When you choose b/t Fruits or Vegetables (Source:=Categories) in column D, the list under Item (column E) will read as a group of Fruits or Vegetables (Source:=If(IsBlank(D4),"",Indirect(D4)).

When I look at the Define Name dialog box, there is a column indicating the sheet name, which I cannot re-create/build myself. My first question is how do I do this, and secondly, the 2nd formula above will not work when I plug it into the data validation, but it may be on account of these worksheet level names. If you guys can see another reason why, I'm all ears (or eyes as it were).

UPDATE: Of course, I just tried it again this morning and it worked, but i would still lke to know how to use Worksheet/book level names.

View 9 Replies View Related

2 Category Axis In Same Level - How To Define Uppermost

Aug 22, 2013

I'm building a chart wich displays data for each month and there are 3 phases that are defined by month and included in the chart. I've managed to show both of the category series, but I'd need to change the order in which the labels are displays. Now I have the axis in the following form:

PH2 PH2 PH3 PH4
1.2013 2.2013 3.2013 4.2013

I need it in the following form:

1.2013 2.2013 3.2013 4.2013
PH2 PH2 PH3 PH4

View 1 Replies View Related

Define Worksheet & Range In One Code

Nov 8, 2008

I got this code I have been trying to solve but it does not work...
What I need to do is define a worksheet and a range of that worksheet.
I can only seam to work out either a worksheet or a range but not the two together.

Private Sub cmd_Cform_Click()
Dim rng As Range
Set rng = Range("A1")
With Sheets("Customer")
rng = .Range("A1")
Application.CommandBars.FindControl(ID:=860).Execute
End With
End Sub

View 9 Replies View Related

Define Dynamic Range In Macro Code

Feb 26, 2008

I have been tring to define a dynamice range in VBA. At the moment, I use following method to Define the range,

Sub OTC_Future_Total()


Dim DynamicRange As Range

Worksheets("FinalReport").Select
Range("e9").Select
Set DynamicRange = Range(Selection, Selection.End(xlDown))
DynamicRange.Select...

View 5 Replies View Related

When A Sheet Is The Active Sheet Then Define A Range

Dec 14, 2008

When a sheet is the active sheet i use the following to define a range.

View 6 Replies View Related

Trying To Move My VBA Programming To The Next Level And Use More Efficient Code

Oct 18, 2007

I'm trying to move my VBA programming to the next level and use more efficient code.

I'm wondering if there is a faster way to run the loop below, perhaps removing the 'For c = 2 to LastRow' as it seems a little clunky to me.

For c = 2 to LastRow

If Cells(c, Range("Product_Type").Column) = "" Then _
Cells(c, Range("Product_Type").Column).Interior.ColorIndex = 41

Next c
I've seen a similar thing somewhere where it was all done in one statement without the loop.

View 9 Replies View Related

Display Macro Security Level Code

Sep 6, 2006

Although not a complete novice, I am fairly new to EXCEL/VBA.
Is there a script that can be embedded into an EXCEL workbook that checks the Macro security of the PC, and if set to HIGH, shows a message that states that "Macro security is too high to open this workbook".
With security set to high, Excel will open my w/ book, but not run any of my checks that are set in the module. So a "catch 22" really, I need the macros to run to that it is being opened on an authorised computer etc, but if security is set to HIGH, then they don't run, similarly, if I put a macro in to check the macro setting, it won't run unless the security is set to medium.

View 9 Replies View Related

Getting Sheet Level Names - VBA

Jul 24, 2012

How can I obtain the sheet-level name through VBA? I'm not referring to the sheet (tab) name but the name as defined though Insert/Define or Insert/Create, etc .

I know the way to get the cell level name goes something like this:

MsgBox Sheet1.Range("A1").Name.Name

But i don't know the correct syntax for sheet level.

View 4 Replies View Related

Dynamic Sheet Level Form Button Text

Jun 19, 2007

I have a form button on a worksheet which calls a specified macro, is it possible to have the text which shows on the button, dynamic? I have created a template excel file, which the user needs to set up as they see fit. I have a separate page which is an 'admin' page where they can customize the narrative in the main template, which includes being able to change the text on form buttons on sheet 1. I have tried with this: Sheets("Approvals").Object("Button 1").Text = Sheets("Admin").Range("e14").value

This unfortuantely results in a "run time error '438' Object doesn't support this property or method". From that i would assume this function is not available, but i'm hoping someone may know a way around it??

View 5 Replies View Related

Define Array On Different Sheet

Jan 4, 2007

I am having a problem defning arrays in VBA when they are on different sheets. When I do a step through the code it stops when I define an array which is not on the active sheet.
Sub MMULT_Test()

Dim wArray As Variant, xArray As Variant, yArray As Variant, zArray As Variant
Dim Fn As Object
Set Fn = Application.WorksheetFunction

a = Sheets("Inputs"). Range("G33")
b = Sheets("Inputs").Range("G34")
c = b - 1

View 3 Replies View Related

Define Range() In Vba

Jan 23, 2007

I want to define a Range() in VBA. have cell1 and Cell2 in the format of rows and cols. ie. Cell1 = Row 1, Col 2. Cell2 is dynamic, can be sometimes row100, Col200, or Row23, Col 1000. May i know how to define it in VBA?

View 2 Replies View Related

Define A Time Range

Jan 21, 2010

I have two columns containg the arrival and departure hours of workers. From these columns I must define the workshift by specifiyng the time range for each shift. AZ contain the arrival hours while BA the departure.

The formula I use doesn't return the results correctly because some shifts are almost the same. i.e: if shift one starts (arrival hours) between 05:00 and ends at 12:59 and shift 1-2 starts at 08:00 and ends at 23:59 then it will go with the first shift even though the times in range belong second shift.

View 8 Replies View Related

Define Range As Variable?

Feb 23, 2013

why this won't work.

Code:

Dim DRange As String
Dim ERange As String
Dim SRange As String
EndRow = Range("A65536").End(xlUp).Row
DRange = Range("D1", "Z" & EndRow)
ERange = Range("E1", "Z" & EndRow)
SRange = DRange

View 6 Replies View Related

Using Variable To Define End Of Range

May 19, 2008

how to define the end of a range using a variable determined by a rng.Rows.Count command (or anything else)? Is this even possible? Here is what I currently have:


Set rng = Range("A1:A10000")
For i = rng.Rows.Count To 1 Step -1
If rng.Cells(i).Value = "0" Then rng.Cells(i).EntireRow.Delete
Next

I'd like to define the end of the range with more accuracy than I'm currently doing.

View 9 Replies View Related

Define Range During Macro

Apr 25, 2006

I'm trying to define a range to be copied during execution of a macro. The range to be copied must include all contiguous data from a defined starting range to the rightmost column and the bottommost row containing data (boht of which can change depending on input data). I use <End>+<RightArrow> and <End>+DownArrow> to define the range when working in Excel (not under macro control).

I used the macro recorder to define the code for the process described above, with results shown below:

Sub Macro2()
Sheets("Source_Info").Select
Range("A2:D6").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Working_Data").Select
Range("A2").Select
ActiveSheet.Paste
End Sub

When I run the macro, however, the End(xlToRight) and End(xlDown) commands do not have any effect (i.e., the rang is not made any bigger when these statements are executed).

I've used the same code in previous macros (with previous versions of Excel and Windows), and it worked fine. I'm running Excel 2003 under Windows XP Professional now.

View 5 Replies View Related

Define An Unknown Range

Aug 13, 2006

is it possible to creaate a macro to highlight a range that starts at a1 to a? and there could be blanks amongst that range. the range will be unknown

View 9 Replies View Related

Define A Variable Range

Dec 22, 2006

I am searching for data, upwards from cell A1400. Once I find data (cell > 0), I need to count up 27 rows and then select these rows for columns A to E. Then I want to copy this block of data and paste it. I have been having difficulty with the selection of a block of data which will be in a different position depending upon the data used. The code I am using is below. The two lines that I need to replace with a Variables cell range reference are:

Range("A1268:E1242").Select
Range("A1268:E1242").Activate

Sub CalculateATR()
Sheets("Data").Range("a1400").Select
Do
If ActiveCell.Value > 0 Then
ActiveCell.Select
Exit Do
Else
ActiveCell.Offset(-1, 0).Select
End If
Loop Until ActiveCell.Value > 0

Range("A1268:E1242").Select
Range("A1268:E1242").Activate
Selection.Copy
Range("G5:K31").Select
ActiveSheet.Paste
Else
End If
End Sub

View 8 Replies View Related

Define Named Range

Feb 7, 2007

how to set up an array within a worksheet

View 3 Replies View Related

Define Range With Numbers Only

Mar 26, 2008

how do you go about referencing a cell by using a value of an integer? For example, I want to select cell A5. I have the number i= 5 (in my vba code) and I want to use that together with the row letter A.

View 4 Replies View Related

Cell Value To Define VLOOKUP Range?

Feb 26, 2014

is there a simple way to do a vlookup (or any other more appropriate formula) using the value of another cell to define the named range relevant to that lookup.

eg I will have two different ranges for fitness performance levels, each of these will be named "Male" and "Female".

I would like to do a lookup based on a formula as follows: Try this formula. Column letter & Row number are for example only. Array formulas will have curly brackets {} emcompassing the ENTIRE formula. You do not add these, you Enter the formula with Ctrl+Shift+Enter and Excel will add them (details: Array formulas). Many more Excel Formulas  Z100 =vlookup(D4,NAMED RANGE,2,false) 

the Named range would be a value in the same row ie B4 - being Male or Female

so I am hoping for something like Try this formula. Column letter & Row number are for example only. Array formulas will have curly brackets {} emcompassing the ENTIRE formula. You do not add these, you Enter the formula with Ctrl+Shift+Enter and Excel will add them (details: Array formulas). Many more Excel Formulas  Z100  =vlookup(D4,B4.Value,2,false) 

otherwise I could just use an If and do something like: Try this formula. Column letter & Row number are for example only. Array formulas will have curly brackets {} emcompassing the ENTIRE formula. You do not add these, you Enter the formula with Ctrl+Shift+Enter and Excel will add them (details: Array formulas).

Many more Excel Formulas  Z100 =if(B4="Male",vlookup(D4,Male,2,false),vlookup(D4,Female,2,false) ) 

View 2 Replies View Related

Define Percentage Based On Value Range?

Nov 27, 2013

I need to be able to work out a percentage based on a value range. ie.

Cell D2 to D5 will define the percentages (so I can adjust it and play)

D2 = 18%
D3 = 15%
D4 = 13%
D5 = 10%

I need to work out:

If the value in cell A10 is between 11 and 200 calculate on D2, if between 201 - 500 calculate on D3, if between 501 - 2000 calculate on D4 ect ect

Cells A10 to A100 will have some figures put in them.I then want to calculate the %value used based on weather the figure in one of the A10 to A100 cells falls in a range. The data being put into the cells in column A will vary from report to report.

I'm trying to automate the calculations :

View 6 Replies View Related

Define Range By Selected Cells

Jul 19, 2014

I am working on some code to copy data from one sheet to another, but I'm not sure if I'm going about defining the copy range correctly. I would like it so that the user can highlight a range of cells on the sheet between A3 and F last row (last row based on col C). However the user should be able to highlight rows in any column between A and F, and they could highly the records with just one column or multiple columns. It should take the row numbers of the highlighted range and use the that as the row number to extract the data from.

[Code] ........

Im currently getting runtime error 13 on 'CopyRange = Selection.Rows'

'CopyRange = Selection.Row' returns the single row number for the first cell in the selection, but i need the range of all the rows in the selection.

View 6 Replies View Related

How Can I Define A Range, Only For The Active Cell?

Jan 27, 2009

How can I define a range, only for the active cell?

View 6 Replies View Related

Define Range Start And End Using Comboboxes

Jan 25, 2012

I have a daily collection of data based on half hour meter readings. Responsibility for this data is to be split between core and non-core hours so for example core hours would be from 6:00 to 18:30.

What i would like to do is have a combobox for both the start time and end time allowing the user to change these as appropriate.

Where i'm struggling is using these comboboxes to select the data between these two times and total the values per row.

My data is currently arranged with the times (staring at 00:00) running horizontally on row 14.

View 9 Replies View Related

How To Define A Cell To Be A Range Dynamically

Feb 14, 2013

The way I have try to define lastcell is wrong. But I don't know how to do it.

Code:
Sub createUniqueRecordID_2()
'
' fill using CTRL + ENTER
'
Dim lastcell
Sheets("projects list").Select
[a1].Select

[Code]...

View 1 Replies View Related

VBA To Define A Range - Worksheet Name With Spaces

Apr 30, 2013

I found some code online that works great for defining a named range. My only issue is that occasionally my worksheet name will contain spaces. this throw off the current code. i do know that if i manually go in to the range and add a "'" to the beginning and to the end of the worksheet name it fixes it. unfortunatly i cannot figure out how to add the "'" in the following VBA.

Code:
Sub AddDynamicRangeVertical()
On Error Resume Next
Dim sRangeName As String
Dim n As Name

If ActiveWorkbook Is Nothing Then Exit Sub

[Code] ..........

View 4 Replies View Related

Define Range Variables And Use In Formula VBA?

May 12, 2014

I can't quite get this formula to give me the correct value.

imgur: the simple image sharer

I tried defining as a Range and I can't get it to work right either way. When I hover over my r1 and r2 values the range is correct but it does have "$A$227:$A$447" quotes around the range so I think that is messing me up.

View 4 Replies View Related

Define A Dynamic Column Range

Nov 3, 2008

I'm trying to define a dynamic column range call 'Cost'

If it starts in Column C, row 4 [C4]. I want the range that to go down to the Row I have defined as 'subtotal'

The user will be able to insert new rows above 'subtotal'

How can I define Column C4 so that any new row added will be including in the defined row range 'Cost'?

View 9 Replies View Related

Define Named Range In VBA Macro

Dec 29, 2009

I am trying to define a number of ranges in different sheets using VBA I am using the follwoing syntax but I am getting a 1004 error:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

ThisWorkbook.names.Add Name:="Test", RefersTo:=Worksheets("Sheet1").Range("E6", Range("E6").End(xlDown))

End Sub

As long as I have sheet1 active, it works. If I have another sheet active and change smt, i get the error.

The code is located in the ThisWorkbook object.

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved