Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 02-14-2023, 09:32 PM
soroush.kalantari soroush.kalantari is offline Problem in making Excel do define name based on cell value Windows 10 Problem in making Excel do define name based on cell value Office 2016
Competent Performer
Problem in making Excel do define name based on cell value
 
Join Date: Jun 2021
Posts: 115
soroush.kalantari is on a distinguished road
Default 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
Attached Images
File Type: png formulaname.PNG (14.9 KB, 15 views)
File Type: png formulaname01.PNG (27.8 KB, 14 views)
Reply With Quote
  #2  
Old 02-19-2023, 12:23 AM
soroush.kalantari soroush.kalantari is offline Problem in making Excel do define name based on cell value Windows 10 Problem in making Excel do define name based on cell value Office 2016
Competent Performer
Problem in making Excel do define name based on cell value
 
Join Date: Jun 2021
Posts: 115
soroush.kalantari is on a distinguished road
Default

I have found the answer. The “ RefersToR1C1:= formulaname” should be replaced with RefersToR1C1:=”=” & formulaname
Reply With Quote
  #3  
Old 02-22-2023, 09:00 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Problem in making Excel do define name based on cell value Windows 10 Problem in making Excel do define name based on cell value Office 2021
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

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
Reply With Quote
Reply

Tags
defined name

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Problem in making Excel do define name based on cell value 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
Problem in making Excel do define name based on cell value Excel 2007, highlight entire row based on data found in one cell MSofficeBLUE Excel 2 10-15-2013 09:51 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 01:10 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft