Problem in making Excel do define name based on cell value
I have written the following code to make excel define names based on cell value. The code has a trouble in” formulaname = ActiveCell.Value” line, because it unexpectedly add a Quotation mark to cell value. (see the attached pictures)
How can I modify the code to solve this issue?
Option Explicit
Dim n As Integer
Dim sheetname As String
Dim fname As String
Dim i As Integer
Dim range0 As Range
Dim formulaname As Variant
Sub nameformula()
Set range0 = Selection
n = Selection.Rows.Count
sheetname = ActiveSheet.Name
For i = 1 To n
range0(i).Select
formulaname = ActiveCell.Value
Selection.Offset(-1, 0).Select
fname = Selection(i).Value
ActiveWorkbook.Worksheets(sheetname).Names.Add Name:= _
fname, RefersToR1C1:= _
formulaname
Next i
Application.WindowState = xlNormal
End Sub
|