#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
I have found the answer. The “ RefersToR1C1:= formulaname” should be replaced with RefersToR1C1:=”=” & formulaname
|
#3
|
||||
|
||||
Please mark thread as "solves" ( under "thread tools"). Thx
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
Tags |
defined name |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Define range within word table between specific text instance and end of cell content | Thom.Simmo | Word VBA | 10 | 03-15-2022 05:28 PM |
VBA - Printing form in excel based on cell value | blah blah blah | Excel Programming | 1 | 08-04-2016 11:22 AM |
Hide a combo box (form control), based on a cell value - Excel 2010 | jaymudda | Excel Programming | 9 | 07-12-2015 10:59 AM |
Can anyone here tweek this macro for renaming Excel files based on a cell's contents? | chrisd2000 | Excel Programming | 6 | 07-01-2014 01:53 PM |
Excel 2007, highlight entire row based on data found in one cell | MSofficeBLUE | Excel | 2 | 10-15-2013 09:51 PM |