strange behaviour of ActiveWorkbook.Names.Add Name...

Hello, I have a problem with writing a code which should name some cells in newly created workshhets.
I came up with:

Sub Naming ()
    Dim strRangeAddress As String
    Dim strRangeName As String
    Dim strWariable As String

    strWariable = "Sitodruk"
    i = 1
    'ActiveSheet.Name -> 1.Naklejki_wariant_A

    strRangeName = "_" & strWariable & i & "text"

    strRangeAddress = "=" & ActiveSheet.Name & "!R6C1"
        
    ActiveWorkbook.Names.Add Name:=strRangeName, RefersToR1C1:=strRangeAddress

End Sub
When I run it I get a Run-time error '1004' - "faulty formula" with options to:
continue - inactive
end, debug
help - to nothing specific, just the generic view

"Debugging" puts me in the line
Then I can hit F8 to move forward with no problems. In the end desired cell is named as i wanted it to be.

So it works but it does not. Please help


I know how to have a known range added to a defined name in Excel and I can also do it via macro;
however, if I am looking at data that might change, I am wanting to define a range based off the following:

Code:
Sub Macro3()
'QTY RANGE SELECTION
    'START
StartCell = Range("$IV$3").End(xlToLeft).Select
    'END
StartCell2 = Range("$A$3").End(xlToRight).End(xlToRight).Select

'Range
QtyRange2 = Range(Range("$IV$3").End(xlToLeft) & ":" & Range("$A$3").End(xlToRight).End(xlToRight))

'MONTH RANGE SELECTION
    'START
StartCell3 = Range("$IV$2").End(xlToLeft).Select
    'END
StartCell4 = Range("$A$2").End(xlToRight).End(xlToRight).Select

      
Names.Add Name:="Incident", RefersTo:=StartCell & ":" & StartCell2
Names.Add Name:="Month", RefersTo:=StartCell2 & ":" & StartCell3
End Sub
Then when I go into the Insert, Name, Define
I see both Incident & Month names in workbook defined; however, they're both defined as:
="True:True"
I feel like I'm right there, but just can't put my finger on the right formula to use

Any suggestions, help?

I'm trying to take the value of a cell and use the value as a name for the row.
If cell a1 has value = June. I want to change the name of row 1 to June.
I'm not sure what I'm doing wrong with the following code.

Sub Name_a_row()
'
'
Dim TheName As String
Dim RowNum As Integer
TheName = ActiveCell.Value
RowNum = ActiveCell.Row
ActiveWorkbook.Names.Add Name:="TheName", RefersToR1C1:="=Data!R&RowNum"

End Sub

Thanks for the help.

Hi all

Trying to add a named range at run-time
Here's what I have so far

	VB:
	
wbNew.Activate 
ActiveWorkbook.Names.Add Name:=tblVendorInv, RefersTo:= _ 
wbNew.Worksheets("Data").Range("J2:J" & rowsKill) 
strDuplicateCheck = "=IF(COUNTIF(tblVendorInv,J2)>1,""Duplicate"","""")" 
wbNew.Worksheets("Data").Range("P2:P" & rowsKill).Formula = strDuplicateCheck 
wbNew.Worksheets("Data").Range("P2:P" & rowsKill).Copy 
wbNew.Worksheets("Data").Range("P2:P" & rowsKill).PasteSpecial (xlValues) 
wbNew.Worksheets("Data").Range("P1") = "Duplicate?" 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Returns
Run-time error '1004':
Application-defined or object-defined error Here

	VB:
	
ActiveWorkbook.Names.Add Name:=tblVendorInv, RefersTo:= _ 
wbNew.Worksheets("Data").Range("J2:J" & rowsKill) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Where did I go wrong here?
Full code below
Thanks
-marc

	VB:
	
 
 
Sub comp_ldg_Dump_VendorInvoice() 
    Dim wbBook As Workbook 
    Dim wbNew As Workbook 
    Dim wsData As Worksheet 
    Dim wsKill As Worksheet 
    Dim rngData As Range 
    Dim rngCrit As Range 
    Dim rngDest As Range 
    Dim arrCrit 
    Dim myRange As Range 
    Dim C As Range 
    Dim cRange As Range 
    Dim newRange As Range 
    Dim lngRows As Long 
    Dim rowsKill As Long 
    Dim strFormula As String 
    Dim Response As String 
    Dim tblVendorInv As Range 
    Dim strDuplicateCheck As String 
     
    With Application 
        .ScreenUpdating = False 
        .Calculation = xlCalculationManual 
        .DisplayAlerts = False 
    End With 
     
    Set wbBook = ThisWorkbook 
    Set wsData = wbBook.Worksheets("Data") 
    Set wsKill = wbBook.Worksheets("Kill") 
     
    lngRows = wsData.Range("A65536").End(xlUp).Row 
     
    wsKill.UsedRange.Clear 
     
     'Vendor Invoice - KR
    Set rngData = wsData.Range("A1:Q" & lngRows) 
    Set rngDest = wsKill.Range("A1") 
     
    arrCrit = Array("Code2", "KR") 
    Set rngCrit = wsData.Range("IV1:IV2") 
     
    rngCrit = WorksheetFunction.Transpose(arrCrit) 
     
    rngData.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=rngCrit, CopyToRange:=rngDest, Unique:=False 
    rngCrit.ClearContents 
     
     'Vendor Invoice - KG
    Set rngDest = wsKill.Range("A65536").End(xlUp).Offset(1, 0) 
     
    arrCrit = Array("Code2", "KG") 
    Set rngCrit = wsData.Range("IV1:IV2") 
     
    rngCrit = WorksheetFunction.Transpose(arrCrit) 
     
    rngData.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=rngCrit, CopyToRange:=rngDest, Unique:=False 
    rngCrit.ClearContents 
     
     
     '/Remove 2nd Header
    rowsKill = wsKill.Range("A65536").End(xlUp).Row 
    Do While rowsKill >= 2 
        With wsKill 
            If .Cells(rowsKill, 1).Interior.ColorIndex = 56 Then 
                .Cells(rowsKill, 1).EntireRow.Delete 
            End If 
        End With 
        rowsKill = rowsKill - 1 
    Loop 
     
     
     'Dump To New File
     
     '/Define new workbook
    Set wbNew = Application.Workbooks.Add 
    Response = InputBox("Add File Notation", "Ledger Dump Utility") 
    wbNew.SaveAs "C:datadelete " & Response & " Vendor Invoices" 
    wbNew.Sheets("Sheet1").Name = "Data" 
    ActiveWindow.Zoom = 75 
     
     '/Define Copy - Paste Ranges
    rowsKill = wsKill.Range("A65536").End(xlUp).Row 
    Set cRange = wsKill.Range("A1:Q" & rowsKill) 
    Set newRange = wbNew.Worksheets("Data").Range("A1") 
     
     '/Paste old to new
    cRange.Copy 
    With newRange 
        .PasteSpecial xlPasteValuesAndNumberFormats 
        .PasteSpecial xlPasteFormats 
    End With 
     
     '/Check for duplicates
    wbNew.Activate 
    ActiveWorkbook.Names.Add Name:=tblVendorInv, RefersTo:= _ 
    wbNew.Worksheets("Data").Range("J2:J" & rowsKill) 
    strDuplicateCheck = "=IF(COUNTIF(tblVendorInv,J2)>1,""Duplicate"","""")" 
    wbNew.Worksheets("Data").Range("P2:P" & rowsKill).Formula = strDuplicateCheck 
    wbNew.Worksheets("Data").Range("P2:P" & rowsKill).Copy 
    wbNew.Worksheets("Data").Range("P2:P" & rowsKill).PasteSpecial (xlValues) 
    wbNew.Worksheets("Data").Range("P1") = "Duplicate?" 
     
     '/Save - Close new workbook
    wbNew.Save 
    wbNew.Close 
     
     '/Clear wsKill For Next Process
    wsKill.UsedRange.Clear 
     
     '/Cleanup
    Set wbBook = Nothing 
    Set wbNew = Nothing 
    Set wsData = Nothing 
    Set wsKill = Nothing 
    Set rngData = Nothing 
    Set rngCrit = Nothing 
    Set rngDest = Nothing 
    Set myRange = Nothing 
    Set C = Nothing 
    Set cRange = Nothing 
    Set newRange = Nothing 
     
    With Application 
        .ScreenUpdating = True 
        .Calculation = xlCalculationAutomatic 
        .DisplayAlerts = True 
    End With 
     
     
End Sub 

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


In order to perform automatic matrix operation I want to add name to a series of column...Something like :
R1C1:R10C1 = name "C_1"
R1C2:R10C2 = name "C_2"
R1C3:R10C3 = name "C_3"

The code should look like this:

For i=1 to 3
ActiveWorkbook.name.Add Name = "C_" & i, RefersToR1C1 ="=Sheet1!R1C & i : R10C & i "
Next i

Thanks for you help.

I find it....
For i=1 to 3
ActiveWorkbook.Names.Add Name:="C_" & i, RefersToR1C1:=Range(Cells(1, i), Cells(10, i))
Next i

Hi to everyone.

I noticed a strange behaviour in excel 2007 data tables. It doesn't happen in excel XP/2003. I wonder whether something has changed or perhaps it's a sort of bug. Let's cut to the chase.

I set up a one-way data table that generate (for example) an income statement for 10 companies (in this example I would have the formulas in the leftmost collumn and the name of the 10 companies in the top row). Then in one cell I put a simple formula that sum an item of all ten companies (say EBITDA for example summing all items in a row). The problem is that this cell doesn't get updated (ie it doesn't perform the sum) even if I press F9. The only way to have excel calculate the sum is either to enter the cell and press enter or re-open the file. Very strange.

Any suggestion to solve this ?

PS: sorry for my english

Paolo

Italy

HI

Once again I have used one of Jerry's very helpful replies to help me get a macro to work. The issues I have is
1. To many factors in the array
2. How to offset from column "M" where data is to set Defined name to Cell in Column "K".

1st I have it copying col K to M then replacing spaces with underscore. This is a long method but am learning at the ripe old age of 60. and it is working.

Next I want it to define the name as Offset (0,-2) which I believe is same row 2 columns before?
I am not sure how / where to put this did try replacing R1C1 but did not work.

Could this look at a list in another sheet for the phrase and then use the correctly formated word in the column next to it. i.e.
Column A "Employee Turnover"
Column B "Employee_Turnover"

Once it has completed this it will then run another macro which is a double click action which open a definition document.

Both codes are below.
Thanks very much for any help

Sub SearchAddNames()
Dim MyArr       As Variant
Dim lngStrName  As Long
Dim StrFind     As Range
Dim wsFind      As String

'replace spaces with underscores and formats column M
        Columns("M:M").ClearContents
        Range("M10").Select
        ActiveCell.FormulaR1C1 = "=RC[-2]"
        Range("M10").AutoFill Destination:=Range("M10:M39"), Type:=xlFillDefault
        Range("M:M").Copy
        Range("M:M").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Application.CutCopyMode = False
        Selection.FormatConditions.Delete
        With Selection.Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
        End With
        With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
        Range("M:M").Replace What:=" ", Replacement:="_", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        End With
        
wsFind = "=" & ActiveSheet.Name & "!"

MyArr = Array("Employee_Performance_Plans", "Employee_Turnover", "Employee_Engagement",
"Absenteeism", "Admin_Ratio", "Agency_Income", "Audit_Report_Risk_Resolution", _
              "Brand_Position_Index", "Budget_Performance", "Capital_Adequacy",
"Channel_Performance_Index", "Employee_Engagement", _
              "Employee_Performance_Plans", "Employee_Turnover", "Employee_Engagement",
"Forecast_accuracy", "Health_Processing", "Hospital_Negotiated_Benefits", _
              "Increase_Ancillary_claims_Processed_Electronically",
"Increase_Medical_claims_Processed_Electronically", "Internal_Satisfaction", _
              "Investment_Income", "Mandatory_Training_Completion", "Member_Loyalty_Index",
"Net_Surplus", "On_time_Management_of_Breaches", _
            "On_time_Management_of_Disputes", "On_time_Management_of_Notifiable_Breaches", _
            "Operating_Result", "Performance_Reviews_Completion",
"Products_meeting_positive_GM_targets", "Retention_Ancillary_Policies", _
            "Retention_Home_Risks", "Retention_Hospital_Policies", "Retention_Motor_Risks", _
            "Retention_Total_Policies", "Revenue", "Risks_Managed_Appropriately",
"Sales", "Sales_Ancillary", _
            "Sales_Calls", "Sales_Home", "Sales_Hospital", "Sales_Motor",
"Sales_National", "Sales_Nil_Advice", "Sales_per_FTE", "Sales_Travel", _
            "Tier_1_System_Availability", "Underwriting_Margin", "WA_Market_Share",
"Work_Request_Resolution")


    For lngStrName = LBound(MyArr) To UBound(MyArr)
    
        Set StrFind = Cells.Find(MyArr(lngStrName), After:=ActiveCell, _
            LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
            SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
        ActiveWorkbook.Names.Add Name:=UCase(MyArr(lngStrName)), _
            RefersToR1C1:=StrFind
    
    Next lngStrName
    
Set StrFind = Nothing

End Sub
2nd code
Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Call Create_Shape_CLOSE_KPI_DEFINITION


If Not Application.Intersect(Target, Range("Employee_Performance_Plans")) Is Nothing Then
    Call Show_EmployeePerformancePlans
    Exit Sub
End If

If Not Application.Intersect(Target, Range("Employee_Engagement")) Is Nothing Then
    Call Show_ENGAGEMENT
    End If

If Not Application.Intersect(Target, Range("Performance_Reviews_Completion")) Is Nothing Then
    Call Show_PerformanceReviewsCompletion
    End If

If Not Application.Intersect(Target, Range("Employee_Turnover")) Is Nothing Then
    Call Show_EmployeeTurnover
    End If

If Not Application.Intersect(Target, Range("Absenteeism_")) Is Nothing Then
    Call Show_Absenteeism_
    End If

If Not Application.Intersect(Target, Range("WAMarketShare")) Is Nothing Then
    Call Show_WA_Market_Share
  End If

If Not Application.Intersect(Target, Range("RetentionHospitalPolicies")) Is Nothing Then
    Call Show_Retention_Hospital_Policies
End If

If Not Application.Intersect(Target, Range("RetentionAncillaryPolicies")) Is Nothing Then
    Call Show_Retention_Ancillary_Policies
End If

If Not Application.Intersect(Target, Range("RetentionTotalPolicies")) Is Nothing Then
    Call Show_Retention_Total_Policies
End If

If Not Application.Intersect(Target, Range("RetentionHomeRisks")) Is Nothing Then
    Call Show_Retention_Home_Risks
End If

If Not Application.Intersect(Target, Range("RetentionMotorRisks")) Is Nothing Then
    Call Show_Retention_Motor_Risks
End If

If Not Application.Intersect(Target, Range("SalesNational")) Is Nothing Then
    Call Show_Sales_National
End If

If Not Application.Intersect(Target, Range("SalesTotalPolicies")) Is Nothing Then
    Call Show_Total_GI_Sales
End If

If Not Application.Intersect(Target, Range("SalesHospital")) Is Nothing Then
    Call Show_Sales_Hospital
End If

If Not Application.Intersect(Target, Range("SalesAncillary")) Is Nothing Then
    Call Show_Sales_Ancillary
End If

If Not Application.Intersect(Target, Range("SalesHome")) Is Nothing Then
    Call Show_Sales_Home
End If

If Not Application.Intersect(Target, Range("SalesMotor")) Is Nothing Then
    Call Show_Sales_Motor
End If

If Not Application.Intersect(Target, Range("SalesTravel")) Is Nothing Then
    Call Show_Sales_Travel
End If

If Not Application.Intersect(Target, Range("SalesHG")) Is Nothing Then
    Call Show_Sales_HG
End If

If Not Application.Intersect(Target, Range("SalesNilAdvice")) Is Nothing Then
    Call Show_Sales_Nil_Advice
End If

If Not Application.Intersect(Target, Range("BrandPositionIndex")) Is Nothing Then
    Call Show_Brand_Position_Index
End If

If Not Application.Intersect(Target, Range("ChannelPerformanceIndex")) Is Nothing Then
    Call Show_Channel_Performance_Index
End If

If Not Application.Intersect(Target, Range("MemberLoyaltyIndex")) Is Nothing Then
    Call Show_Member_Loyalty_Index
End If

If Not Application.Intersect(Target, Range("InternalSatisfaction")) Is Nothing Then
    Call Show_Internal_Satisfaction
End If

If Not Application.Intersect(Target, Range("HealthProcessing")) Is Nothing Then
    Call Show_Health_Processing
End If

If Not Application.Intersect(Target, Range("AncillaryElectronic")) Is Nothing Then
    Call Show_Ancillary_Electronic
End If

If Not Application.Intersect(Target, Range("MedicalElectronic")) Is Nothing Then
    Call Show_Medical_Electronic
    Exit Sub
End If

End Sub


I have a variable that holds a number.
I have a start point for a named range that needs to go out the number of
columns based on that variable. The following works wonderfully for vertical
situations but I need that variable “movs” to denote the column not row.

movs = Sheets("WIP2").Range("I4").Value
ActiveWorkbook.Names.Add Name:="TEST", RefersTo:=Range("A1:A" & movs)

Spastic morning,
Arturo

Code originally posted by Datsmart

I was trying to utilize this code to load some named ranges. I get debug error 1004 on the last line before the C loop(line starts with activeworkbook). I tried changing to Dim Var2 as range to Dim Var2 as string, same error.

Dim Rng1 As Range
Dim Rng2 As Range
Dim c As Range
Dim Var2 As Range
Sheets("Temp").Select
Set Rng1 = Range("A1:A" & Range("A65536").End(xlUp).Row)
For Each c In Rng1
Var2 = c.Offset(, 1).Value
Var2 = Right(Var2, Len(Var2))
ActiveWorkbook.Names.Add Name:=c, RefersToR1C1:=Var2
Next c

Thanks

I have a macro to add range names to a workbook and the names and ranges are
listed in a worksheet. I'm able to add the names, but the range is shown as
text. Here is what I have:

Sub PPQDefineRangeName()
'
' DefineNamefromList Macro
' Macro recorded 1/16/2006 by Barb Reinhardt
'
Dim lastrow
Dim rangename
Dim seriesrange
Dim CurBook
Dim i
Dim sht

CurBook = Application.ActiveWorkbook.Name
lastrow = Worksheets("RangeName").Cells(Rows.Count, "a").End(xlUp).Row
Debug.Print lastrow
For i = 2 To lastrow
rangename = Workbooks(CurBook).Worksheets("RangeName").Range("a" &
i).Value
seriesrange = Workbooks(CurBook).Worksheets("RangeName").Range("c" &
i).Value
sht = Workbooks(CurBook).Worksheets("RangeName").Range("d" & i).Value
Debug.Print rangename; seriesrange; sht
'range name can't have a "'" or a "-" or a "%" or a " " or a "." or "("
or ")" or "+"
'Added the following
'Sheets(sht).Activate
ActiveWorkbook.Names.Add Name:=rangename, RefersToR1C1:=seriesrange
'Exit Sub
Next

End Sub

When I look at the data in the named ranges, it looks like:

="OFFSET(InterTeam-ChartLabel,4,0)"
but it should look like
=OFFSET(InterTeam-ChartLabel,4,0)

The data is displayed in the original sheet as:
OFFSET(InterTeam-ChartLabel,4,0)

How do I get the " to go away so that the ranges are useful?

Thanks in advance,
Barb Reinhardt

Hi All,

I have some code to create multiple named ranges as below:

Sub AddNames12month()
    For i = 1 To 50
        ActiveWorkbook.Names.Add Name:="12month_" & i, RefersTo:= _
                "=INDEX('Correlation 12|Correlation12'!$B:$B,MATCH(9.9999E+307,'Correlation
12|Correlation12'!$B:$B)-COUNT('Correlation 12|Correlation12'!$B:$B)+1):INDEX('Correlation
12|Correlation12'!$B:$B,MATCH(9.9999E+307,'Correlation 12|Correlation12'!$B:$B))"
    Next i
End Sub
This code is though incomplete. What I want to happen is that as i loops from 1 to 2 all instances of $B:$B should change to $C:$C, as it moves to 3 all instances of $B:$B should change to $D:$D. So like below:

When i = 2 the named range 12month_2 should be

When i = 3 the named range 12month_3 should be 

Any ideas how this can be done?

Thanks

Data is coming into a worksheet, and from a macro, I determine the range
using Selection.CurrentRegion.Select, I then .Add Name, but the macro is
setting the range. I want the range to change each time the macro is run.

Hi,
I have a macro which imports several sets of data into a single worksheet.
Each of these sets is automatically assigned a range name.
I am trying to select the intersection between each of these ranges and select columns.
The problem code is below, I keep getting the error message "Run-time error '1004': Method 'Range' of object'_Global' failed".


	VB:
	
ActiveWorkbook.Names.Add Name:="t", RefersToR1C1:= _ 
Columns("B:B") 
ActiveWorkbook.Names.Add Name:="i", RefersToR1C1:= _ 
Columns("I:I") 
For counter = 0 To steps 
    Application.Intersect(Range("SeriesName & counter"), Range("t")).Select 
    ActiveWorkbook.Names.Add Name:=SeriesName & counter & XTitle, RefersToR1C1:= _ 
    Selection 
    Application.Intersect(Range("SeriesName & counter"), Range("i")).Select 
    ActiveWorkbook.Names.Add Name:=SeriesName & counter & YTitle, RefersToR1C1:= _ 
    Selection 
Next counter 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Any help would be much appreciated!

Hello All!
I'm attempting to do something I initially thought fairly simple but, alas, I'm falling short of my goal (to do this without any help) again!

I attempted to utilize Dave Hawley's suggestion at http://www.ozgrid.com/VBA/add-worksheet.htm, but I have several other inputs that I'm not quite sure how to accomplish ...

1. I'd like the VBA code to ensure the first WS in the WB is selected prior to adding the "Merged" WS.
2. I also need to check if the WS "Merged" already exists and, if so, exit this sub and continue processing the data.
3. I'd like to ensure the "Merged" WS is the Active WS prior to running additional VBA code (which I'll most likely need additional help with ... but in another thread).

Here's what I've tried:

	VB:
	
 AddWorksheet() 
     ' Select the first worksheet in the workbook
     ' Set ActiveWorksheet = Sheet1  'Worksheets have variable names (Doesn't work!)
     ' What I really want to do is add a WS at the beginning of the WB named "Merged"
     ' At this point, I should see if WS "Merged" already exists ... if so, need to exit
     ' ActiveWorkbook.Sheets.Add Count:=1
     ' ActiveSheet.Name = "Merged"
    Worksheets.Add().Name = "Merged" 'This works better
     ' At this point, I would like to select "Merged" as the active WS
     ' ws.Activate     ' ???
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
As always, I sincerely appreciate any help anyone can send my way!

Thanks!

Hello All,

Huge fan of these forums, have taught me very much!

However I have come to a point where I too must ask for specific help.

I am trying to take the Name of the Range that is currently selected and then set the Name to a variable to be called later on.

Current Code:


	VB:
	
wbk.Sheets("Totals").Range("Totals1").Select 
 
Dim XY_Name As Object, C_XY_Name As Object, C_Range As Range 
 
 ' XY_Name = CurrentRegion.Name.Name ???
 
XY_Name = CurrentRegion.Name 
 
Dim C_Sel As Object 
Set C_Sel = Selection 
ActiveWorkbook.Names.Add Name:=XY_Name & "R", RefersTo:=C_Sel 
ActiveWorkbook.Names(XY_Name & "R").Comment = "" 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Any help would be greatly appreciated!!!

Hi,

I am using follwoing piece of code for offset function. For Some reason it is not working and need your help to correct it.


	VB:
	
 
Reference = Cells(TableRowNumber, TableColumnNumber).Address 
Col = Application.WorksheetFunction.CountA(rngF.Columns(TableRowNumber, TableColumnNumber).EntireColumn) 
 
ActiveWorkbook.Names.Add Name:=TableName, RefersToR1C1:= _ 
"=OFFSET(Reference,0,0,Col-3,1)" 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Col value is also being calculated to zero. TableRowNumber and TableColumnNumber are the variables which store the starting location of the column.

Thanks and regards

Angshuman

Hi,

suppose I named the range R1C1:R3C3 "myName" by writing the following code:


	VB:
	
ActiveWorkbook.Names.Add Name:="myName", RefersToR1C1:= _ 
"=Sheet1!R1C1:R3C3" 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Later on I want to add another range, say R5C1:R8C3, to myName, i.e. I want to do something like:

myName = myName + R5C1:R8C3

if you understand what I mean.

I couldn't get that to work with any syntax I tried.
So, what do I have to write in VBA?

I have five macros that look like this


	VB:
	
 Region1() 
    ActiveWorkbook.Names.Add Name:="Region_Name", RefersToR1C1:= _ 
    "=Scroll.xls!Name_1" 
    ActiveWorkbook.Names.Add Name:="Region_Values", RefersToR1C1:= _ 
    "=Scroll.xls!Region_1" 
End Sub 
 
Sub Region2() 
    ActiveWorkbook.Names.Add Name:="Region_Name", RefersToR1C1:= _ 
    "=Scroll.xls!Name_2" 
    ActiveWorkbook.Names.Add Name:="Region_Values", RefersToR1C1:= _ 
    "=Scroll.xls!Region_2" 
End Sub 
 
Sub Region3() 
    ActiveWorkbook.Names.Add Name:="Region_Name", RefersToR1C1:= _ 
    "=Scroll.xls!Name_3" 
    ActiveWorkbook.Names.Add Name:="Region_Values", RefersToR1C1:= _ 
    "=Scroll.xls!Region_3" 
End Sub 
 
Sub Region4() 
    ActiveWorkbook.Names.Add Name:="Region_Name", RefersToR1C1:= _ 
    "=Scroll.xls!Name_4" 
    ActiveWorkbook.Names.Add Name:="Region_Values", RefersToR1C1:= _ 
    "=Scroll.xls!Region_4" 
End Sub 
 
Sub RegionAll() 
    ActiveWorkbook.Names.Add Name:="Region_Name", RefersToR1C1:= _ 
    "=Scroll.xls!Name_all" 
    ActiveWorkbook.Names.Add Name:="Region_Values", RefersToR1C1:= _ 
    "=Scroll.xls!Region_all" 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
In the chart
Region_Name is the name of the series
Region_Values is the values of the series
I have a button for each routine
I want to use only one button instead. How?

Hello,

I have a define name on my worksheet
it is called chartdata
it is defined by =OFFSET(Graph!$A$16,0,0,6,3)

in my code (see below) I can change the value of the last 3 to any number I wish. This part is working fine.

I have a chart that I want to base the source data on the Name "chartdata" that is established above. For some reason the code will not work.

Can anyone help me out here?


	VB:
	
 add_Lap() 
    For lap = 1 To 2 
        For car = 2 To 6 
            Cells(car, lap + 2) = Rnd + 12 
             
             
            ActiveWorkbook.Names.Add Name:="Chartdata", RefersToR1C1:= _ 
            "=OFFSET(Graph!R16C1,0,0,6," & lap + 2 & ")" 'sets the chartdata name
             
            ActiveSheet.ChartObjects("Chart 1").Activate 
            ActiveChart.ChartArea.Select 
             
            ActiveChart.SetSourceData Source:=Sheets("Graph").Range(chartdata) 
             'The above line is where I get an error!!!
             'I prefer to not have the above line in my code by setting the source data on the chart to the name chartdata,
but for some reason that does not work either.
        Next car 
    Next lap 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Hope I was clear in my description. If not let me know.

Thanks,

Ross

I have tried to use two different ways to display the sheets name in a cell A1 in every sheet of a workbook.

1) By using a self-defined Function: =SH_NAME()
Function SH_NAME()
Application.Volatile
SH_NAME = ActiveSheet.Name
End Function

2) By using a sheets formula:
=RIGHT(CELL(("filename")),LEN(CELL("filename"))-SEARCH("]",CELL("filename")))

BOTH behave very strange - they display the SAME sheets name - depending from within which sheet I pressed F9.

a) Is there a way to correct this situation !?

b) If I will change one of the sheets name I want to see ONLY the appropriate cell being changed.

Thanks, Michael

Hello again. I'm trying to add a worksheet based on the year month week day and hour number; I'm not going to embarrass myself by publishing the code I've written so far :o

I can add a worksheet, I can check for a worksheet - delete it and add it again, but I can't extract the numbers I need and concatenate them for the ActiveWorkbook.Worksheets.Add.Name = str "blah"

Well I can sort of extract the weeknum but nothing else...I just want to be able to add this bit of VBA to a procedure that opens another workbook, populates a worksheet, copies the used range and pastes it to the new worksheet so I don't get confused about when I did it!

I'd appreciate very much the code for extracting the year, month, day and hour number (minute would be good, too!) and concatentaing them...

the day of the week is WEEKDAY("NOW()",1), yes? (starts Sunday)
the week number is WEEKNUM("NOW()") (Starts Sunday)

bleh, sounds like I've done nothing to help myself but I'm at my wit's end

TIA
HuX

How does one give the selected area a name in VBA?

I have a named range used in several formula. Rather than using a dynamic named range, I'd like to use a macro to update the named range. When I recorded the macro, I received this:


	VB:
	
ActiveWorkbook.Names.Add Name:="Dates", RefersToR1C1:="=Data!R4C1:R34C1" 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
...but I need to tweak that line of code so it refers to the current selection (determined earlier in the macro).

Hi,

Can anybody help me and tell why I get error 1004 "Method Range of object _
Global failed" ?

I try to ensure it always will exist a named range when the workbook is
opened, even if a user somehow deleted one of the hidden named ranges in an
earlier session.

I have this code in ThisWorkbook in Public Sub Workbook_Open() event:

ActiveWorkbook.Names.Add Name:="Sh104Rng",
RefersTo:=Range("C29:G48,C52:G71,C75:G94"), Visible:=False

/Kind regards

I need to name a union of 52 ranges into a range named "SUPER!
Year" (the same series of cells on other worksheets will have the name
Year as well) I tried using:

ActiveWorkbook.Names.Add Name:="SUPER!Year1",
RefersTo:=Application.Union(Range("$AE$5:$AH$9"), _
Range("$AE$12:$AH$16"), Range("$AE$19:$AH$23"), Range("$AE$26:$AH
$30"), Range("$AE$33:$AH$37"), _ Range("$AE$40:$AH$44"), Range("$AE
$47:$AH$51"), Range("$AE$54:$AH$58"), Range("$AE$61:$AH$65"), _
Range("$AE$68:$AH$72"), Range("$AE$75:$AH$79"), Range("$AE$82:$AH
$86"), Range("$AE$89:$AH$93"), _
Range("$AE$96:$AH$100"), Range("$AE$103:$AH$107"), Range("$AE$110:$AH
$114"), _
Range("$AE$117:$AH$121"), Range("$AE$124:$AH$128"), Range("$AE$131:$AH
$135"), _
Range("$AE$138:$AH$142"), Range("$AE$145:$AH$149"), Range("$AE$152:$AH
$156"), _
Range("$AE$159:$AH$163"), Range("$AE$166:$AH$170"), Range("$AE$173:$AH
$177"), _
Range("$AE$180:$AH$184"), Range("$AE$187:$AH$191"), Range("$AE$194:$AH
$198"), _
Range("$AE$201:$AH$205"), Range("$AE$208:$AH$212"), Range("$AE$215:$AH
$219"), _
Range("$AE$222:$AH$226"), Range("$AE$229:$AH$233"), Range("$AE$236:$AH
$240"), _
Range("$AE$243:$AH$247"), Range("$AE$250:$AH$254"), Range("$AE$257:$AH
$261"), _
Range("$AE$264:$AH$268"), Range("$AE$271:$AH$275"), Range("$AE$278:$AH
$282"), _
Range("$AE$285:$AH$289"), Range("$AE$292:$AH$296"), Range("$AE$299:$AH
$303"), _
Range("$AE$306:$AH$310"), Range("$AE$313:$AH$317"), Range("$AE$320:$AH
$324"), _
Range("$AE$327:$AH$331"), Range("$AE$334:$AH$338"), Range("$AE$341:$AH
$345"), _
Range("$AE$348:$AH$352"), Range("$AE$355:$AH$359"), Range("$AE$362:$AH
$366"))

But it will not handle 52 (only 30). I saw a thread a few days ago
that had my answer (in fact it is what got me this far and the other
ranges I needed named) but now I can't find it again, and apparently
my notes were not a good as I thought. What would the VBA code be to
do either of the followinng?

name the selected cells in a worksheet (name is local to that
worksheet)
or
Name a union of named ranges (QUPER!Qtr1, SUPER!Qtr2, SUPER1Qtr3,
SUPER!Qtr4)

Thanks

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