A macro for creating named ranges in a spreadsheet. The generated names are local in scope.The macro requires that at least one row of two adjacent cells be selected. The text in the left cell is used to define the name of the cell on the right. Note that there is no real error checking here.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
Sub GenerateLocalVariable() Dim SelectedRange As Range Dim N_rows As Integer Dim N_columns As Integer Dim CurrentSheetName As String Dim CellToName As Range Dim VariableName As String Dim FullVariableName As String Set SelectedRange = Selection N_rows = SelectedRange.Rows.Count N_columns = SelectedRange.Columns.Count CurrentSheetName = SelectedRange.Worksheet.Name If N_columns <> 2 Then Call MsgBox("You must have two columns selected", vbOKOnly, "Error") Exit Sub End If For I = 1 To N_rows VariableName = SelectedRange(I, 1).Value If VariableName <> "" Then FullVariableName = "'" + CurrentSheetName + "'!" + VariableName Set CellToName = SelectedRange(I, 2) ActiveWorkbook.Names.Add Name:=FullVariableName, RefersTo:=CellToName End If Next I End Sub |