Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Constant Array In Vba?

Is it possible to use a contstant array in Excel VBA? I have tried several differnt ways to declare it, but the VBA editor keeps yelling at me. The following did not work:

View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Extend A Constant Array
I've never had to venture into Arrays as there was no need.

I'm now faced with Arrays.

Generally I'm able to figure things out, in this case I'm stumped.

What I have is a Constant Array, that output the amount of Rows the Array displayed in.

I know the information is there, what I don't know is how to extend the rows.

ie it Outputs 5 Rows right now, with a top row header.

What I want is 10 rows with a header.

View Replies!   View Related
Array Constant Named Ranges
Is it possible to make an named array constant from named constants to use in a lookup?


I have defined the following constants in Names:

Num1 = "1234567890"
Num2 = "0987654321"

And would like to make the following array constant in Names:

Arr = {Text1,Num1;Text2,Num2}

However, it does not work.

I am using Excel 2007 by the way and the above values are made up.

View Replies!   View Related
Array Constant From Cell Values
I need to convert a cell that has a list of values A1 = 1,2,3. To a range expression {1;2;3}. I want to do this with a formula and not VBA. Example: Cell D1 has the formula = SUMPRODUCT(COUNTIF(C30:C31,A30:A32),B30:B32). I want to put the list range from column C into one cell.


View Replies!   View Related
Replace A Constant With A Variable In An Array Formula.
I have the following array function that I am trying to get to work properly:

ActiveCell.FormulaArray = "=SUM(IF(NCR!O2:O100=39326,NCR!Q2:Q100,0))"
39326 is the value of 9/1/2007, and this formula works properly.

I am looking for a way to use this formula but replace 39326 with whatever date is in the first row of the same column as the active cell when it is run.

That is, if the macro was run with cell B8 as the active cell, "39326" would be replaced with whatever value was in cell B1.

View Replies!   View Related
Array Constant Composed Of Named Constants
I try to understand what the syntax is to create and use an array named constant which would be composed of other constant.

Example :
Constant called AvgZone1 which is =AVERAGE(zone1)
Constant called AvgZone2 which is =AVERAGE(zone2)

and then create a named array constant based on the names : =AvgZone1,AvgZone2
is not working. You cannot for example do a SUM().

View Replies!   View Related
If And Constant Logical
i have problem with a long code.

So I divided that code in 4 parts.

So I have
module1 -> Sub Macro1()
module2 -> Sub Macro2()
module3 -> Sub Macro3()
module4 -> Sub Macro4()

I have declared

Option Explicit

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

all my sub have the following structure:

View Replies!   View Related
Declare Constant
what does the symbol # means in VBA? (but I couldn't put the # in the subject of my message )

I'm trying to understand someone's code... at some point he wrote:
sum_LU_Area = 0#

I thought that the # was used to declare a constant but I'm not too sure because in his code earlier he declared
Public sum_LU_Area As Double

Beside, sum_LU_Area is calculated somewhere further in the program.

View Replies!   View Related
Create A Constant Variable
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 = "-"

View Replies!   View Related
One Constant To Multiple Cells
I am just wondering if this can be possible:

this is an table with a lot of cells

12 36 89 56 97 78 98 36
36 89 56 97 78 98 36 12
97 78 98 36 56 97 78 98
12 36 89 56 97 etc ..

to become like this ...

View Replies!   View Related
Increment One Column But Keep The Other Constant
Here is what I try to do:
A1 = Power(C1,B1)
A2 = Power(C2, B1)
A3 = Power(C3, B1)
An = Power(Cn, B1)

How can I increment column C while keeping B constant at B1 by using right-bottom mouse pointer dragging on column A? Or is there another way?

View Replies!   View Related
Define Constant Last Row For Module
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.

View Replies!   View Related
Ambiguous Name Detected With Constant
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 KL As String = "Inddata-KL"
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.

View Replies!   View Related
Refer To Named Constant In VBA
I created a name in Excel 2002, Name = "NameToUse", Refers to = "Bill".
How do I get the value in VBA code?

Range("NameToUse").value ' doesnt work
NameToUse ' doesn't work

View Replies!   View Related
Access Public Constant From Another Workbook's Vba
how I can refer to a public constant in a different workbook's (which is already open) VBA from the workbook which I am currently working on's VBA?

View Replies!   View Related
Constant Line In Chart By Quarter
I have a bar chart that has data by the quarter over a span of 3 years, so there are a total of 12 bars on the chart. Everything looks good there, but now I need to add the minimum amount that the customer needs to acheive (which varies every quarter) in the form of a line. So I added a line chart with the minimum data, but the problem is it goes up and down from point to point and doesn't create a visual horizontal line for the customer to see if they are hitting their minimum every quarter. Is there any way around this?

View Replies!   View Related
Multiple Variables To Equal Constant
I'm trying to create a spreadsheet that will tell the user how much of two products to use in order to get a desired percentage of total fat. For example: if the total pounds is to be 3000 at a fat percentage of 30%. There would be two products (a lean meat and high fat meat). The percentage of fat in those two meats will change each time. The user will enter those percentages and then the formula needs to tell how many lbs of each to use in order to get the desired percentage of fat.

Each time there is a "target" amount of each product to use. For example: 1000 lbs of Lean Meant and 2000 lbs of Fat meat. Those are based on the ideal levels of fat in each.

However, sometimes the fat is not at the ideal so the formula has to be adjusted in order to compensate.

View Replies!   View Related
Detect If Cell Contains A Formula Or Constant
I need an equation that lets me determine whether a cell contains a static constant value (either a string or a number), or an equation that generates a value.

Problem: I have a cell that contains a default equation that generates a default value, depending on a few conditions. The user can overwrite this cell with a constant. In another cell I need to know whether that cell is still the default equation or a new constant value.

I cannot simply test the value of the contents to see if they match my default, because the user may choose to enter that value.

View Replies!   View Related
Making A Constant And A Variable In 2007
assume we have constant P = 100, K=50 and F=10.

I would like to "lock" these cells, and have a variable which could adjust and this variable would multiplicate each one and put out the number.

For example:

P = 100
K = 50
F = 10

Variable: 10 this would generate a sum of:

P = 1000
K = 50
F = 10

View Replies!   View Related
Named Constant To Return Sheet Name
Most of the methods used for returning the sheet name are quite cumbersome. "=MID(CELL("filename",A1), FIND("]",CELL("filename",A1))+1,256)" takes up a lot of space in a larger formula.

Is it possible, please, to create a Name which returns the sheet name? For example I could write "=ThisSheet" and it would return the name of the sheet.

Of course I could resort to a UDF, but I wondered if it could be done directly in Excel.

When I try to insert the formula "=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)" into a Name, Excel helpfully converts A1 to "'Sheet1'!A1". So "=ThisSheet" in Sheet2 returns "Sheet1".

If I make the sheet name part of the Name, as in "'Sheet1'!ThisSheet, it works OK, but has to be added for each new sheet (though it works if an old sheet is copied).

View Replies!   View Related
Dialog Constant For The Security Form
I have some code that requires access to the Visual Basic Project. If the user hasn't checked this option in Excel, I'd ideally like to open up the security form for them, with code like the below:


However, I can't find the dialog constant for the security form. Does anyone know it, or have any alternatives? I don't want to use SendKeys.

View Replies!   View Related
Constant In Formula To Change Based On Cell Value
I am creating on the fly an array (2 columns) in which the one row cell is mathematically derived from its neighbor (A left-hand cell). However, when the mathematically derived product exceeds a value, the row neighbor changes to another constant until its neighbor's product exceeds another value, etc. Can I create such an array without resorting to VBA?

View Replies!   View Related
Pasting Constant Formula Where Referral Sheet Changes
I am creating a data tracking workbook. Information is entered into cells on
different sheets, then the tallies are pulled to one easier to read sheet.

In creating the tally sheet, I am setting the cell formulas. The cells
referred to are constant, only the sheet referred to changes. For example,
the cell might read

='FP 1'!$I$25

and the next one should read

='FP 2'!$I$25

....and so on. I would like to be able to paste the formula and have the
referred to sheet change so that I do not have to go through and make
hundreds of little changes.

View Replies!   View Related
Use Cell Value As Constant (Const) In Macro Code
I'm trying to set named range as a constant in VBA, to allow me to recall the same range as an output target in different subs, without having to 'set' it in each module. I have tried to declare it at the top of a module like this...

Public Const tpnb_range = Worksheets(" SQL").Range("tpnbs")

and get a "Compile Error : Constant expression required"

View Replies!   View Related
Adding A Constant To Sets Of Rows Of Variable Size
I have data from 100 trials. Each trial has a variable number of data points (rows), but each row is labeled in the first column with the trial number. I would like a macro to identify the first row of each trial, calculate the difference of the value of the cell in column G of that row from a constant (718), and then add that difference to that cell and all other cells in that column of that trial.

So based on the attached sample, I would like all values in column G for trial 2 to be +1, for trial 3 to be -5, and for trial 4 to be +2.

In the file, I've cleared all the data from the impertinent columns. Eventually, I will also want to perform a similar process on column H but with a different constant.

View Replies!   View Related
Concatenate Query; Include 2 Variables With 1 Constant Entry
I have recently discovered the concatenate formula. Is it possible to include 2 variables with 1 constant entry. i.e. I wish to establish file names with 2 variables, e.g. 0051 and 1025 followed by a file extension, .EC0 - the file name being 00511025.EC0. The variables are in Excel columns. I can achieve the wanted result by establishing an extra column and copying .EC0 into every cell of that column; but I would prefer not to have that extra column in the completed Excel file.

View Replies!   View Related
How Can I Copy A Formula To New Rows So That Some Values Remain Constant?
I have used the following long but simple formula.

=SUM((B17*B14)+(C17*C14)+(D17*D14)+(E17*E14)+(F17*F14)+(G17*G14)+(H17*H14)+(I17*I14)+(J17*J14)+(K17* K14)+(L17*L14)+(M17*M14)+(N17*N14)+(O17*O14))

I need to copy it to numerous other cells. However I need the row fourteen values to remain the same while the other values change according to which row I'm copying it to e.g

=SUM((B20*B14)+(C20*C14)+(D20*D14)+(E20*E14)+(F20*F14)+(G20*G14)+(H20*H14)+(I20*I14)+(J20*J14)+(K20* K14)+(L20*L14)+(M20*M14)+(N20*N14)+(O20*O14))

Is there any way of doing this without changing the formula manually every time?

View Replies!   View Related
Calculate Balance To Zero Based On Constant Fix Rate & Withdrawals
I have a situation where I have to determine the required size of a deposit from which constant withdrawals are to be made until the deposit runs out to ZERO. The deposit earns interest at a fized rate, capitalized monthly after each withdrawal had been made.

In my example case, the target month (The actual cell that needs to have a value of ZERO (when the deposit runs out), or the "Range") is found in cell G16. In another situation, it may be found in cell G19 etc, depending on the situation.

It is this changing of the target cell (the one that needs to be ZERO) that has me stumped. I've been thinking of using VLOOKUP (not trying to lead you ;-)) to determine the actual position of the cell that need to have a value of zero but do not know how to build this into the VBA code of Goalseek. This target cell in the Goalseek code should be the cell in column G opposite the figure 1 in column A.

In my example I simply typed in a figure 1 in column A - in the actual spreadsheet the position of this figure is calculated with a formula and its position moves from situation to situation. If the 1 is in cell A19, the target cell, whose value should be ZERO, will then be cell G19..............

View Replies!   View Related
VBA Macro: Clear All Constant Cells In Range That Don't Have Formulas
is there a way to do this in VBA? I've also read: - Dave Hawley's recommendation of using: Sheet1.UsedRange.SpecialCells(xlCellTypeConstants).ClearContents

from another thread (which is excellent!)

- SHG's recommendation of using a named range, for example:


Given my limited knowledge of VBA, how would I now combine the two to write a VBA sub-routine that clears a named range entitled "Entry" on a sheet entitled "Data"? Would the following be the correct syntax: Worksheets("Data").Range("Entry").SpecialCells(xlCellTypeConstants).ClearContents

View Replies!   View Related
Copy Files From Constant URL & File Names Listed In Range
I have a list of filenames in Sheet1, range A1:A20. These files can be found in URL
http://mysite/x/y. Where y is the filename (including the extension) and x is the 1st 4 characters of the filename. So if in A1 I have the filename AB1234.pdf, this can be found in the link: http://mysite/AB12/AB1234.pdf. Is there a was in VBA where I can loop thru the list in A1:A20, and copy each from their respective URLs and save into C:Documents and Settings ?

View Replies!   View Related
Autofill In Macro Range Is Constant How Can I Code To Be A Variable Range?
I am trying to write a macro which will autofill specific columns. The macro will set the range from the start of my autofill to the end of my autofill as a constant range.

The problem I need to get around is the end of my range can always change each time I run the macro. For instance, the first time I run the macro I may only need to autofill from row 4 to row 15. The next time, I may only need to autofill from row 4 to 23 (because of user updates). How can I make the end of my range not be a constant address but variable?

View Replies!   View Related
Converting 3x10 Array To A 1X30 Array To Run A Match Formula
Say I have 3 columns of data: A1:C10 and I want to run a Match() function on them all together to see if I get a match any one those cells, say the value of have in X1.

Since, Match only allows a One-Column lookup array.. is there a way to "concatenate" or "append" the 3 columns together within a formula so now I would be looking to Match in an array that is 1 column * 30 rows?

Basically want to convert =Match(X1,A1:C10,0) to =Match(X1,A1:A30,0) without moving around the raw data in the sheet.

And I want to avoid doing an AND or OR formula that uses 3 separate MATCH() for each column.

I have a hunch that the MMULT or MMULT/TRANSPOSE functions are involved, but can't seem to get it right.

View Replies!   View Related
String Array Values To Array Of User-Defined Types
I have a class module with several private variables, including one that is an array of a user-defined type. I am trying to set the values of a single element of this array with "Property Let ..." from a string array:

View Replies!   View Related
Filter An Array (the Longer One) Using The Shorter Array As The Criteria
I am trying to filter an array (the longer one) using the shorter array as the criteria. I am currently doing this using the following method

IF(LOOKUP(lookup cell, array)=lookupcell, lookupcell, "FALSE")

I then copy and paste 'values' and filter out the 'false' to get my final result.

This has worked in the past, but for some reason that I simply can't figure out, the formula isnt working! I've attached the example, and I've highlighted a number in blue (cell E522 and C103), (that should be being found in the 'LOOKUP' function) but is returning a "FALSE". I have looked over the code and simply can't figure out why Excel isn't returning the right value.

This is obviously happening for a quite a few of my numbers, as my filter result is returning an array that is about 1500 shorter than it should be. I have highlighted E522 as the 'example cell' to look at.

View Replies!   View Related
Formula To Determine If A Cell Has A Formula Or Constant
In cell A1 exists data. In cell A2, I would like to test A1 to determine if A1 is a formula or a constant.

=A9+3 would result "formula"
="1+3 would result "formula"
3 would result "constant"
'Blahblah would result "constant".

View Replies!   View Related
First Array Remain Same,second Array Always 11 Cell Added
=CORREL(C1:C10,C12:C21) at H1
=CORREL(C1:C10,C23:C32) at H2
=CORREL(C1:C10,C34:C43) at H3

can i have a macro that first array remain the same, and second array always 11 cells added. drag it down also can

View Replies!   View Related
Multiply Each Element In A 6x6 Array By A Similar 6x6 Array
I've tried to multiply each element in a 6x6 array by a similar 6x6 array, both on the same sheet, and it worked.(see Macro2 and attached xls file "Test").Then I got more ambitious and tried to do the multiplication from a standard array in sheet "TestA", with the result on the same sheet, by each array in sheet "TestB" and failed.How do I solve this problem? Pgualb PS:I'm using the R1C1 style.

Sub Macro2()
For y = 29 To 34
For x = 2 To 7
Cells(x, y) = Cells(x, y - 27) * Cells(x, y - 18)
Next x
Next y
End Sub
Sub Teste12()
'Multiplica matriz em TestB por matriz padro em TestA com _
'resultado na matriz em TestA correspondente matriz em TestB
Dim x, y As Integer
For y = 2 To 7.............

View Replies!   View Related
Concatenate An Array (using Array Formula)
I just wanted to concatenate a few cells using an array formula like this:


Well, this does not work. I have no idea why it fails. Any solution to this without scripting?

View Replies!   View Related
Using Array Variable Instead Of Array Formula
i need to replicate what i did using array formulas with VBA macro (array variable). to make things clear and simple i created an example for illustration only. look at it & u will find what i did & what i need to do ,much of it
in writing so that i accurately describe my problem. attached is my example

View Replies!   View Related
Return Array Elements From 2D Array
I'm trying to use an array to carry out string function on a range of excel cells.

Here is the code I am using

Dim arrXl As Variant

arrXl = ws.Range("F1:F" & ws.Range("D1").End(xlDown).Row)

For i = LBound(arrXl) To UBound(arrXl)
MsgBox (LBound(arrXl))
'If 1st char is different from 3rd char then remove all of string after 1st char

If Left(arrXl(i), 1) <> Mid(arrXl(i), 3, 1) Then
arrXl(i) = Left(arrXl(i), 1)
End If
'If 1st char is different from 7th char then delete string after 5th char
If Left(arrXl(i), 1) <> Mid(arrXl(i), 7, 1) Then
arrXl(i) = Left(arrXl(i), 5)
End If

The lbound function returns the value of 1 as the lower bound, I do not have "Option base 1" set so I was expecting the lbound value to be 0. The first 2 cells in the F column are blank so this may have something to do with it, I am unsure if cells in excel can be null if they can be null one cell may be null and the other may be a zero length string but I am unsure about this.

The ubound function returns a value of 487.

The code breaks when I try to access an element in the array so it breaks on the line:

If Left(arrXl(i), 1) <> Mid(arrXl(i), 3, 1) Then

and returns the "Subscript out of range" error message.

View Replies!   View Related
Looking Up Row Array Data In Column Array
On one sheet (KPI) I have either the values "x" or "" in the range A84:A89 to mark wheter to use the corresponding project in the range B84:B89. On the sheet X-ref I have the same project names in range T4:Y4 and a corresponding target value in T8:Y8

What I want is the sum (or average) of the marked-projects target-values. The result should end up in KPI!G31.
In other words I want


but it doesn't work since the first range is an column-array and the second range is an row-array.

View Replies!   View Related
Multiple A Logical Result Array With A Text Array And Return Text
I need to multiply an array of logical results ( returned as {1,0,0,0,1,0 et.}) with a text array (a reference column) and return the text in the reference column in case the value in the logical array is 1.

View Replies!   View Related
"You Cannot Change Part Of An Array" When Editing Array Formula
I am trying to copy or edit a cell thats has the following formula (see below), I keep getting this message "You cannot change part of an Array!"


View Replies!   View Related
Return Array With Array
how to use Array formulas in Excel. I can Sum, Count
etc. I am wondering if you can return an array of data with an array formula


Name Replied?

Bob N
Brian Y
Robyn N
Rachel Y

From the table above if I test for "N" I'd like the formula to return a list
of names, in this case Bob and Robyn.

View Replies!   View Related
Coding An Array Within An Array
I need to create an array within an array (or loop within a loop). First loop works fine as

x = 0
Dim DirArray(0 To 80) As String
Dim intI As Integer
For intI = 0 To 80
DirArray(intI) = Range("start").Offset(intI, 0).Value

For x = 0 To 80
lob = DirArray(x)
Range("lob") = lob
Sheets("WBB LOB Summary").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

At the 'start' point (cell A1), first loop works downward through rows. Second loop (starting in cell B1) would work across columns in each row within the first loop. I will have to use a variable for 'y' (the across value) b/c the number of cells containing data can vary between 3 & 9, but I have a COUNTA in each row setup to use as the variable.

So spreadsheet data would look like this with X data being the 'start' cell:

X 1 2 3
Y 1 2 3 4 5 6
Z 1 2 3 4

I need to pickoff X1, X2, X3, Y1, etc, picking up every sub-category on the sheet.

View Replies!   View Related
Sum Array In VBA And Clearing An Array
1) after I have made an filled an array with data.
Dim orders(100000 To 200000) As Double
I want to then sum the orders above and below a number, lets say

View Replies!   View Related
Redim An Array Within An Array
Can I redim an array that is held within an array? I have (for example) 3 worksheets, and from each one I want to load 2 columns into an array. However I also want (I think) those 3 arrays to be contained within one array so that I can step through them easily. The code below will not run, but might better show what I am wanting;

View Replies!   View Related
VBA How Can I Pop These Into An Array
i have 3 columns of data... i want to populate an array from anything in column 1 that has an 'N' in column 3...

View Replies!   View Related
0 Out Array
Does making an array equal to 0 when you are done with it make the rest of the procedure run faster than if you did nothing to it?

I have an array that fills up with about 50-60K records. Once it fills up it displays it in a worksheet and then does some other stuff. I'm wondering if clearing out the array helps open up memory thus allowing the rest of the procedure to run faster.

View Replies!   View Related
Array Formula In VBA
I have a lengthy array formula that I try to enter using VBA. I get the common run-time error. I tried breaking it down, but that did not work. Maybe the syntax was wrong in the break down. Can some help out please?

=IF(COUNTIF($B:$B,B2) 1 Then
Range("B" & x).EntireRow.Delete
End If
Next x
Application.ScreenUpdating = True

View Replies!   View Related
Sum Array #Value! Errors
I have a workbook with data tabs (one shown here) and a summary tab. Essentially, one inputs hours for people in the Data tab (Tech) that are then calculated/summed to total $ based on rates I have hidden elsewhere in the workbook (not included). I'm trying to bring the subtotal lines (highlighted green) into the Summary tab based on which subtotal and what month/year it is. My formula works fine, except for the fact that it is returning a #Value! due to the "Hrs" heading for each Phase. I don't know why it's doing this because I've not run into sum array issues when mixing numbers and letters before. The error formula is highlighted in yellow in my attached sample, but below is a copy of the formula.

View Replies!   View Related
Copyright 2005-08, All rights reserved