I have a column J that is named Premium. If I use the function =INT(Premium) in K4 then it returns the integer for the value in J4. If I do the same thing with a UDF then the function argument receives all the values in column J. The author of the function says this is how UDFs work by design. Is there a way to use defined names with UDFs and have it work the way it does with built in functions?
View 5 RepliesTrying to use a Defined Name as the cell reference in VBA. The idea is to change the value of a couple of other cells base on the value of the cell "FreightRow". I am starting with a Message box just to make sure the code was reading the cell reference.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim FR As Range
Set FR = ThisWorkbook.Names("FreightRow").RefersTo
If FR = 3 Then
MsgBox ("Help")
End If
End Sub
Is it possible to use defined names from other workbooks? I would like to use this defined name as part of a VBA code.
Example: ActiveChart.SeriesCollection(1).Values = Range(Name1)
where Name1 is a defined name from a different workbook
I use Vlookup's a lot so i define my range with specific names a lot. But when i want to clear the range names via Insert>Name>Define>"Select the range name then click delete. but instead of clicking on each range name, i would like to to delete all.
I can create a macro to do this but some days i use different range names that other days so the below will not work exactly.
Ive created the following code which I want to use to create a name in excel but it doesnt work.
when i put =INDIRECT i get an error and with out the = it places a text string in with speech marks on it e.g. defined name reference in excel reads ="INDIRECT('Health and Safety'!$AP$3)"
How do I get rid of those speech marks? the cell reference will change each time the macro runs and so will the sheet reference.
myC = ActiveCell.Address
Dim strNAME As String
strNAME = "YAXIS" & "_" & "HS" & Chartnumber
strSHEET = ActiveSheet.Name
ActiveWorkbook.Worksheets(ActiveSheet.Name).Names.Add Name:=strNAME, _
RefersToR1C1:="INDIRECT('" & strSHEET & "'!" & myC & ")"
I am currently overhauling a massive workbook, that previously relied on an absurd amount of named ranges in order to run vlookups. I no longer need these named ranges and would like to delete them to unclog the workbook. Currently I only see a way to delete them one by one. Is there a way to mass delete all of them?
How to delete all defined names from a workbook
View 14 Replies View RelatedI have several SumIf formulas that references a Cell in a Main Reference Tab. This is useful to me as I have over 100 tabs of data points and having a reference cell with that text is easier to change one time vs 100 times.
My question is this: Will Defined Names be "faster" than using a reference cell? With over 100 tabs, there's a lot of formulas and I'm looking for a faster way to get the same output without Excel Memory overload.
Why can't I alphabetize all of my defined name ranges in the Name box?
View 8 Replies View RelatedThis thread is related to this thread. Offset, Match, Max Formula In the Insert - Name - Define window:
If one wants to copy a formula from the "Refers to" box and the formula extends past the right side of the box, how does one use "select all" or mark the whole formula from beginning to end so it can be copied without messing up the formula. At the moment, when I try this, it changes the formula to include the active cell of the worksheet that's open when I use the Ctrl + c etc. I have tried Ctrl + Ins and Ctrl + a and Ctrl + c. None of these are working for me.
I have a situation where I copy a worksheet to further on in the same workbook (essentially, using it as a template to create new sheets). I have certain cells that contain references to named ranges, e.g. on NewSheet1, cell $A$1 contains =StudyNo. Unfortunately, it shows as #NAME?. But, if I go to the formula bar and press enter, it resolves the name properly. I thougt maybe that automatic calculation was on manual but it is not. Why is this happening and what can I do to resolve it.
View 3 Replies View RelatedI want to be able populate a combobox with all defined ranges in the workbook?
View 4 Replies View RelatedI would really appreciate if someone could help me with the following problem:
I wrote this code, but for some reason it doesn't work. I am completely new to VBA and have no idea what went wrong:
as the title says, i need to rotate a range but to keep all defined names (single cell or range) consistent.
i.e.
lets say i have the following cells filled:
1 2 3 4
1 2 3 4
1 2 3 4
1 2 3 4
1 2 3 4
1 2 3 4
1 2 3 4
now i have a few defined ranges. lets say:
range name, refers to
mycell1row1, A1
mycell90roanything, B1
mysomething, C1
mysomthingrange, A1:F1
etc.
what i need in the end is this:
1 1 1 1 1 1 1
2 2 2 2 2 2 2
3 3 3 3 3 3 3
4 4 4 4 4 4 4
and for defined names:
mycell1row1, A1
mycell90roanything, A2
mysomething, A3
mysomthingrange, A1:A7
I'm trying to use some defined names to create charts. Though it works at the very begin, some extremely weird things happen when I try to develop further .....
Attached please find the test file, which simplifies my real case but shows the same problem.
Test.xlsx
In the test file, three names are defined as below:
Line1: "=Xvalue*1"
Line2: "=Xvalue*2"
Xvalue: "=ROW(SampleChart!$A$1:$A$25)"
the chart is very sample: "=SERIES(,Test.xlsx!Xvalue,Test.xlsx!Line1,1)"
So far, it works very well. However, I just simply can't add the second line into the chart, for example, "=SERIES(,Test.xlsx!Xvalue,Test.xlsx!Line2,2)"
I canNOT even change "Line1" to "Line2" in the "SERIES" formula!
I am looking to create a formula which sums the values associated with several defined names. For example, I have a workbook with the following defined names SalesPerson1Total, SalesPerson2Total, etc. and these amounts are all sourced from multiple tabs. The amount of defined names (i.e. 'SalesPersons') is variable, therefore, I want the formula to read Sum the values of all defined names which are named with the following convention 'SalesPerson(X)Total'.
View 9 Replies View RelatedI have created 500+ defined names that refer to worksheets that do not exist (yet). When I add the previously nonexistent worksheets, the defined names that refer to them are not "live" and do not work in functions where that name is used. If you go into the defined names and click on the ones that refer to the now existent worksheet, they start to work, but I was hoping there was a way around this step (e.g. a simple macro that says "update all defined names").
I created all the names so that the person who will eventually be using this will only have to add an appropriately named worksheet and put the data in...then everything will automatically work. I do not think adding names is difficult, but the person who will be using this does, so I want to avoid that person mucking around in my names.
I'm trying to simplify a spreadsheet i've been given by defining names for certain values. I'm using Excel 2013. Is there any way to have all of the newly defined names I've created for cells automatically be inserted into all of the formulas that exist in the spreadsheet?
Example.
Old formula in one sheet of a workbook looks up a cell in another sheet with the value of ='sheet1!A1
I've given the value of cell A1 in Sheet1 a name of dgwd.
How do I get every formula in the workbook that references 'sheet1!A1 to change the value within that formula to dgwd?
I have 3 UDFs that I created as Excel addins. They are all loaded at run time,
but the first two return the #NAME? error when I try to use them... The third
(Age3) works fine...
Function Age(DoB As Date)
Age = Int((Date - DoB) / 365.25)
End Function
Function Age2(DoB As Date)
Age2 = Int((Date - DoB) / 365.25)
End Function
Function Age3(DoB As Date)
Age3 = Int((Date - DoB) / 365.25)
End Function
Although I'm able to write UDFs and distribute addins, one thing I've found is that if you use a UDF formula on a sheet on one machine, save it, then open it on another, Excel doesn't automatically look in the local .xla for the formula. In fact it tries to find it on the network (expecting to locate the originating user's machine, I suspect) and then throw a strop....
View 9 Replies View RelatedI know this is a mess - I am just learning how to create UDFs. I am trying to extract first and last name from an email address is this format - John.Smith@abc.com. This is what I have and of course it does not respond . .
View 9 Replies View RelatedI have a addin that contains user defined functions. I am using the MacroOptions command to assign the functions to categories. The addin loads without any problem in Excel 2003. In Excel 2002 and Excel 2007 Beta, if I open Excel and load the addin using the menu the addin loads OK. But when I try to open Excel with the addin already installed I get the following error message: Run-time error '1004':
Method 'MacroOptions' of object'_Application' failed. how to aviod this error message in Excel 2002 and Excel 2007 Beta?
When I Step through (Using the f8 Key) the below code -- Comments Explain my problem/Question
Code:
Sub SetUpTable()
Worksheets("Sheet1").Activate
Application.Calculation = xlCalculationManual ' Without entering this line the Macro in the next line or two), jumps to and begins running a UDF in a VBE ADDIN
'Module marked as Volitile
For TheYear = 1 To 5
Cells(1, TheYear + 1).Value = 1990 + TheYear
[Code]....
Windows 7, Excel 2010
I have an add-in with UDFs. I want explanation of the function arguments in the function wizard and also be able to click for Help in the wizard. So I register the functions like this:
Application.MacroOptions macro:=Range("afsfunctienaam")(i), _
Description:=Range("afsfunctiebeschrijving")(i), _
Category:=Range("afsfunctiecategorie")(i), _
HelpContextID:=Range("afshelpcontextid")(i), _
HelpFile:=HelpPad, _
argumentdescriptions:=Sheets("afsnederlands").Range(Paramtabel)
Where Param is the address of an array with argument descriptions, as text (like "A1:A4").
If I run the xlsm file, it works fine.
But if I load the xlam file in the add-ins dialog, the path to the Help file seems to be lost; I get directed to Excel's general online Help. The explanation of the arguments still work. Probably because they are already in the registry. But shouldn't the Help path be held in the registry as well?
I have been asked to look at moving a very large set of pricing sheets from Excel 2000 to Excel 2003. In the progress of this I have found that the functionality for a UDF in a cell to change/recalculate any other cells has been removed in Excel XP, whereas it was available in Excel 2000. The amount of work required to refactor the code is very large, and before starting on it it would be good to find out if there is a workaround.
The Sheet works in the following way:-User enters values, clicks a button to run a Macro,-Macro calculates all the ranges required to return a price-In many of the cells it is calculating are Functions which go and look at a large number of cells to calculate the price, sometimes writing to other Cells and sometimes having to recalculate other cells-As it is such a large sheet we can't simply calculate all cells before we tell it to price, as they are not all needed and so aren't calculated unless needed for performance reasons.It would be a lot of work to move the functionality from the Functions to the initial macro call, though this would solve the problem. Is there any other workaround?Macro security is set to low.The Errors I receive are the following:The UDF will hit a line like Range("DataRange").Calculate and this will raise a "Calculate method of Range class Failed" error. If it hits a line like Range("DataRange").Value = 1 this will raise a "Application-defined or object-defined error"If it hits a line like ActiveWorkbook.Names.Add Name:="This_Name", RefersToR1C1:="=Sheet1!R8C2" then it also raise a "Application-defined or object-defined error".Any of these run from a UDF in Excel 2000 works.I have a sample workbook if this isn't clear
I want to replace all defined range names in the sheet that start with "Street" to "Road"
For example I have 50 defined names in the sheet as such "Street-01", "Street-02"... all through "Street-50"
I want to change them all in vba to Road-01", "Road-02" etc.
I keep running into this error when I try to create a pivot table.
The debugger highlights the following line:
Trying to convert an Excel 2003 macro to work in Excel 2007.
The problem line is
Dim MyDataObject As DataObject
I suspect the problem is a Missing Reference, but I cannot figure out which one. I have the same ones (in 2007) as 2003 except for one which is not showing
Microsoft Forms 2.0 Object Library
Is this the one it needs? It is called something else in 2007?
The ones I do have ticked are
Visual Basic For Applications
Microsoft Excel 12.0 Object Library
OLE Automation
Microsoft Office 12.0 Object Library
Microsoft ADO Ext. 2.8 for DDL and Security
Microsoft DAO 3.6 Object Library
Microsoft ActiveX Data Objects 2.8 Library
Microsoft Scripting Runtime
Microsoft XML v2.6
'Code1
Call movedata(1, rrow, ecol, erow)
'Contact Person
Call movedata(26, rrow, ecol, erow)
Worksheets("new").Activate
Worksheets("new"). Range(Cells(erow, ecol), Cells(erow, (ecol - 2))).Font.Bold = True
'Name
Call movedata(2, rrow, ecol, erow)
'Street Address
Call movedata(3, rrow, ecol, erow)
'city
Call movedata(4, rrow, ecol, erow)
' zip
Call movedata(5, rrow, ecol, erow)
Worksheets("new").Range(Cells(erow, ecol)).Font.Bold = True
' speed dial
Call movedata(6, rrow, ecol, erow)
the first time font.bold is set to true, it completes w/o error. The bolded line returns 'application defined or object defined error 1004'. Ive tried activating the new sheet immediately before setting bold (like the first time it gets set) but it still errors.
Trying to add a named range at run-time
Here's what I have so far ...