I know I've asked before but I can neither find my previous question or the answer. So, once again.... is it possible to refer to a named range in a CSE array formula? I know how I would type a formula that way -- but I would be happy to see an example.
Most importantly, will it always work? Or are there pitfalls and dangers?
I've got a problem referring to a named range in another worksheet.
Private Sub test() Dim var_StartWeek As String ' 0740 This is the value I make a lookup for Dim var StartWeekNr As Integer ' 40 and get this as a result
'*** Here is the named range in another worksheet (It doesn't work) Set rng = ActiveWorkbook.Names("WeekData2").RefersToRange var_StartWeekNr = WorksheetFunction.HLookup(var_StartWeek, Range("rng.Value"), 4) ' I'm doing something wrong here...
'*** If I have the named range in the same worksheeet it works fine 'var_StartWeekNr = WorksheetFunction.HLookup(var_StartWeek, ActiveSheet.Range("WeekData"), 4)
I put all my named ranges in a seperate worksheet in the workbook.
On a different sheet I have all my data with references to the named ranges. But what it gives me is the cell information from the sheet on which the named range is located and not on the worksheet where I actually need the calculations to be done.
How can I make the named range refer to the cells in the worksheet in which it sits?
Worksheet 1 --------------- 1 2 3 4
Worksheet 2 -------------- I name the function Red apples RedApples =CONCATENATE($A1&"Red Apples")
Question #1 How do I make the name reference in worksheet 2 refer to the cells located in the worksheet in which i use it. In other words, when I use RedApples in Worksheet 3, it gives me what is sitting in A1 in worksheet 2 instead of in A1 on Worksheet 1.
Question #2 How do I make the name reference in Worksheet 3 keep stepping down along Column A in Worksheet 1?
I have the following codes in which I am to refer a file and folder to do some further processes.
myfilename = "C:Documents and settingsacsMy DocumentsEntrymyfile1.xls" folderPath = "C:Documents and settingsacsMy DocumentsEntry"
I want to enter these two paths in a sheet in my excel report file (For example, I am running the report from Report1.xls, in which there is a sheet name "Filepaths". In this,
in B5, I would like to enter the Filepath (B5 named as "FILEPATH") in B6, I would like to enter the Folderpath (B6 named as "FOLDERPATH")
So that, if I am copying the folder to another area, I no need to change the code every time in the VBA editor. I can do changes in these cells and it will be taken as the path to run the code further.
I have a userForm (Form1) that contains a persons name that I would like to reference in a separate UserForm (Form2). In the separate UserForm (Form2) I need to reference this persons name many times, so I was wondering if there was a was to declare this name in the separate UserForm (Form2) as a constant. Only thing is that a constant, to the best of my knowledge, must be an expression and not a variable. Mainly, I'm trying to avoid declaring the myName variable in each Sub within Form2, which it will be needed for a ton of Sub's.
Code for Form2: Const myName As String = Form1.txtName.Value
So I have module1 -> Sub Macro1() module2 -> Sub Macro2() module3 -> Sub Macro3() module4 -> Sub Macro4()
I have declared
Dim mdNextTime1 As Double Dim myEnter As Integer 'Dim myK1 As Integer 'Dim myK2 As Integer 'Dim myK3 As Integer Dim myK4 As Integer Const myC As Integer = 82 Dim Lastrow As Integer Dim myActualRow As Integer
I have a master sheet and 102 'advisor named sheets'. The master sheet is updated daily with information (number and text) along 1 row in different columns (A:W). I would like when the advisors name is typed (W) =joebloggs! for this whole row to be auto input on joebloggs sheet. There can be multiple of these entered daily for same person, so the information would need to populate on the next row so not to type over the previous entry.
I need to create a named range on multiple sheets with the same named range & i cant figure out how to do this. EG :- I want to create a named range called "_SubUnitRows" on sheet1 starting from "A1:A50" & other named range again called "_SubUnitRows" on Sheet2 starting from "A1:A25" ...
I want to basically set a varible that can be called back into a multiple of documents I will try and explain this as best as I can but it is a fortnightly pay date... i.e. 22/04/2009 Wednesday... as the payroll is fortnightly I want to be able to use the current date or todays date in a future proof macro.
i.e. if todays date is the 14/04/09 then the next pay date has to be the 22/04/09, if this was run in two weeks it would be 06/05/2009.
I was thinking a long the lines of lock in one start date... 14/01/09, then adding increments of 14 days until todays date - variable + 14 * r = "-"
Option Explicit Public Const ARK As String = "Inddata-ARK" Public Const VVS As String = "Inddata-VVS" Public Const KON As String = "Inddata-KON" Public Const EL As String = "Inddata-EL" Public Const LD As String = "Inddata-LD" Public Const KØL As String = "Inddata-KØL" Public Const OpDat As String = "Opdatering"
And then macro's under it... So the aboce is in module "INIT_S". Now in another module "RUN_P" I have a sub-rutine that uses these constants with a select case. Likes this:
Sub OpEksInitNu() Select Case ActiveSheet. Name Case ARK With OpEks 'more stuff Case VVS 'more stuff Case KON 'more stuff End Sub
But when I try to run OpEksInitNu i comes with ambigious name with ARK as the error, later down I use another public constant. But i keep getting the ambigious name error.. What could cause that... I have tried to locate all ARK, VVS etc. to try to determine if it is used later on, but it's not! I only declare the constants one time, and no module is called ARK, VVS etc.
I'm looking to run several subs in a module which all refer to the same constant but I'm having difficulty defining that constant in a way that the various subs will accept. Basically I'm trying to define a constant LastRow. I am fairly new to VBA so am not sure whether to use a Get LastRow public property (which I tried to use and failed) or a Function and then how to make this readable by all the subs.
I have sales per month going down a column and I would like to subtract a corresponding $ amount going across columns. It's hard to explain but I would like a formula that I can populate all of the cells with. Spreadsheet should make it clearer.
Ok, I have a spreadsheet that I use to track the majority of my work throughout the day. I have about 10 columns that I use for my own information, and about 4 of them that my colleagues need updates on. I have one sheet "worktracking" and another sheet "printable." Printable has just the 4 columns of info that my colleagues might need, formatted in a way that prints out nicely. Each column in "printable" references a column in "work tracking" like this
Now the problem that I'm running into is that I routinely delete rows from the "worktracking" sheet, which results in a reference error on the "printable" sheet. I am wondering if there is a way to rework it, such that when I delete a row from "worktracking," then "printable" just does the same, and only retains rows that have data in them...
I have a list of data which which has repeated supplier numbers in column A. For instance
A123 A123 A123 X456 x456 x456 x456
The information in other 13 columns is variable and I need to be able to move all the rows which start with the first supplier number to another sheet which I will rename to that name, then move the second set of supplier numbers to a second sheet etc. So in my above mini example I would have one sheet named A123 with 3 rows of data and another sheet named X456 with 4 rows of data.
The number of supplier codes will be different every day and the number of rows within each supplier number will also vary. Each new datafile that comes in daily creates a new file do I do not have to worry about existing values only what the current file contains. I have used the following code to move data based on a known constant before but dont know how to tell the macro "move all the data while the value stays the same"
Sub DiffOrderNo() Dim totalrows As Long, row As Long Cells.Sort Key1:=Range("A1") totalrows = ActiveSheet.UsedRange.Rows.Count
I need to calculate a formula in a constantly changing range. In the example below, the formula in column C would yield the % change in column A between the first "P" value in column B and the minimum value prior to the next "P" value.
So, the first result would be =(A2-A1)/A1, which is (1896.3274 - 1973.4764)/1973.4764. The cells in column C would be blank until the next formula, which is =(A8-A7)/A7, then it gets tricky. The next would be =(A14-A10)/A10.
A B C 1 1973.4764 P 2 1896.3274 3 1922.5499 4 1905.2061 5 1985.6797