?
Solved

Excel VBA name range

Posted on 2015-01-19
2
Medium Priority
?
112 Views
Last Modified: 2015-01-19
Hello,

please see attached file.

somehow the dynamic range does not work always. therefore i wanted help how to change the code
 to do the following

Define BigStr as referring to:  =REPT("z",255)
 
Define Icol as referring to:=MATCH(BigStr,Details!$1:$1)  instead of =COUNTA(Sheet1!$1:$1)
 
Define lrow  as referring to: =MATCH(BigStr,Details!$A:$A)  instead of =COUNTA(Sheet1!$A:$A)


Sub CreateNames()
' written by Roger Govier, Technology4U

    Dim wb As Workbook, WS As Worksheet
    Dim lrow As Long, lcol As Long, I As Long
    Dim myName As String, Start As String

    ' set the row number where headings are held as a constant
    ' change this to the row number required if not row 1
    Const Rowno = 1

    ' set the Offset as the number of rows below Rowno, where the
    ' data begins
    Const ROffset = 1

    ' set the starting column for the data, in this case 1
    ' change if the data does not start in column A
    Const Colno = 1
    
    ' Set an Offset from the starting column, for the column number that
    ' will always have data entered, and will therefore be used in calculating lrow
    
    Const COffset = 0  ' in this case, the first column will always contain data.

    On Error GoTo CreateNames_Error

    Set wb = ActiveWorkbook
    Set WS = ActiveSheet

    ' count the number of columns used in the row designated to
    ' have the header names
 
    lcol = Cells(Rowno, Columns.Count).End(xlToLeft).Column
    lrow = WS.Cells(Rows.Count, Colno).End(xlUp).Row
    Start = Cells(Rowno, Colno).Address
    
    wb.Names.Add Name:="lcol", RefersTo:="=COUNTA($" & Rowno & ":$" & Rowno & ")"
    wb.Names.Add Name:="lrow", RefersToR1C1:="=COUNTA(C" & Colno + COffset & ")"
    wb.Names.Add Name:="myData", RefersTo:= _
                  "=" & Start & ":INDEX($1:$1048576," & "lrow," & "Lcol)"

    For I = Colno To lcol
        ' if a column header contains spaces, replace the space with an underscore
        ' spaces are not allowed in range names.
        myName = Replace(Cells(Rowno, I).Value, " ", "_")
        If myName = "" Then
            ' if column header is blank, warn the user and stop the macro at that point
            ' names will only be created for those cells with text in them.
            MsgBox "Missing Name in column " & I & vbCrLf _
                   & "Please Enter a Name and run macro again"
            Exit Sub
        End If
        wb.Names.Add Name:=myName, RefersToR1C1:= _
                     "=R" & Rowno + ROffset & "C" & I & ":INDEX(C" & I & ",lrow)"
nexti:
    Next I

    On Error GoTo 0
        MsgBox "All dynamic Named ranges have been created"
    Exit Sub

    Exit Sub

CreateNames_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & _
    ") in procedure CreateNames of Module Technology4U"

End Sub

Open in new window

NameRange.xlsm
0
Comment
Question by:Flora
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 2000 total points
ID: 40557499
Small tweaks only:

    wb.Names.Add Name:="BigStr", RefersTo:="=REPT(""z"",255)"
    wb.Names.Add Name:="lcol", RefersTo:="=MATCH(BigStr,$" & Rowno & ":$" & Rowno & ")"
    wb.Names.Add Name:="lrow", RefersToR1C1:="=MATCH(BigStr,C" & Colno + COffset & ")"
    wb.Names.Add Name:="myData", RefersTo:= _
                  "=" & Start & ":INDEX($1:$1048576,lrow,Lcol)"

Open in new window


A Table would be simpler though.
0
 
LVL 6

Author Closing Comment

by:Flora
ID: 40557779
you are so cool Rory.

I would have been lucky having at least 20% of the capacity of your intelect :-)
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question