Constant Array In Vba?

Jul 24, 2009

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:

ADVERTISEMENT

Extend A Constant Array

Jul 30, 2008

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 9 Replies View Related

Array Constant Named Ranges

Jul 17, 2008

Is it possible to make an named array constant from named constants to use in a lookup?

Eg

I have defined the following constants in Names:

Text1 = "AAAAAAAAAA"
Text2 = "BBBBBBBBBBB"
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 9 Replies View Related

Array Constant From Cell Values

Aug 26, 2008

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.

ABCD
110140
2203
3305
450
560

View 4 Replies View Related

Replace A Constant With A Variable In An Array Formula.

Oct 1, 2007

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 9 Replies View Related

Array Constant Composed Of Named Constants

Jun 20, 2008

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 9 Replies View Related

Constant Variables - Declare Name In Separate UserForm As Constant

May 8, 2012

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

View 5 Replies View Related

If And Constant Logical

Jun 17, 2008

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 9 Replies View Related

Declare Constant

May 15, 2007

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 7 Replies View Related

Increment One Column But Keep The Other Constant

Jun 28, 2009

Here is what I try to do:
At
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 2 Replies View Related

Copy Formula With 1 Constant Value?

Oct 6, 2011

If I have a formula that I want to copy throughout a column, but I want to keep one of the parts of the formula constant, is there a short way to do this?

For example:

First cell is A1*O24
Second Cell is A2*O24
Third Cell is A3*O24

Right now when I try to copy the formulas, the second cell always reverts to A2*O25, which is not correct.

View 2 Replies View Related

Using Variable Instead Of Constant In VBA Formula

Jan 8, 2012

I try to adjust chart range using variable value instead of constant value.

My current code :
ActiveSheet.ChartObjects("Chart 4").Activate
ActiveChart.SeriesCollection(1).Values = "='Check_2G'!$Z$2:$Z$32"

I need to make range ( "='Check_2G'!$Z$2:$Z$32" ) adjestable with varible like m instead of 32 in previous code.

View 2 Replies View Related

Macro Is Counting A Constant Twice?

Apr 25, 2012

For a single column of values, the following macro counts the first value twice, how can i fix this? for example: A1 thru A5 are a,a,b,c,d the result is

a 2
a 2
b 1
c 1
d 1

and should be
a 2
b 1
c 1
d 1

Macro:

Sub CountOfEachItem()
Dim ListRange As Range
Dim NewList As Range

[Code].....

View 1 Replies View Related

Excel VBA - Using Constant For A Set Of Values

Oct 19, 2012

Code:
Const Books1 = Array("5638", "5639", "10984")
Const Books2 = Array("3140", "7509", "3050", "7549", "7508", "7032", "6770", "6773", "4388", "6460")

I want to set an array as above for multiple use in my code (Used for auto-filtering various data with the same criteria).

The above is not allowed it seems, is there an alternative method so that if my Constant changes I only need to amend this once.

View 6 Replies View Related

Create A Constant Variable

Apr 14, 2009

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 9 Replies View Related

One Constant To Multiple Cells

Jun 4, 2009

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 ..
etc...

to become like this ...

View 9 Replies View Related

Refer To Named Constant In VBA

Oct 1, 2007

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 3 Replies View Related

Ambiguous Name Detected With Constant

Oct 24, 2007

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.

View 2 Replies View Related

Define Constant Last Row For Module

Feb 4, 2008

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 5 Replies View Related

Subtracting Monthly Sales From Constant?

Dec 12, 2013

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.

View 6 Replies View Related

Making A Constant And A Variable In 2007

Nov 18, 2008

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 3 Replies View Related

Constant Errors When Referencing Another Sheet?

Nov 5, 2013

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

='worktracking'!c2

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...

View 3 Replies View Related

Moving Data Based On Constant

Oct 8, 2011

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

[Code] ...........

View 3 Replies View Related

Identify Min And Max In Constant Dynamic Range

Sep 13, 2012

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

[Code] ...........

View 7 Replies View Related

Declare Private Constant As Long?

Dec 27, 2012

I am trying to declare lngLr as Long and Constant. But it's buggin out on me. Is this the correct way to do it?

Code:
Private Const lngLr As Long = ".Cells(Rows.Count, 1).End(xlUp).Row"
Sub calculate_active_employees_sheet_years_of_service_w_Oasis()
Application.ScreenUpdating = True

[Code].....

View 4 Replies View Related

Error / Constant Expression Required

Jan 22, 2013

I am getting 'Constant Expression Required' and this line of code highlighted.

Code:
Const sFile As String = ThisWorkbook.Path & "Survey.xlsm"

View 5 Replies View Related

Replace Constant Value With Variable In R1C1 VBA

Mar 15, 2013

My current dataset goes to row 256, when I use the Macro Recorder it produces the following "static" code.

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"claim_export!R1C1:R256C23", Version:=xlPivotTableVersion12). _
CreatePivotTable TableDestination:="Sheet5!R3C1", TableName:="PivotTable1" _
, DefaultVersion:=xlPivotTableVersion12

QUESTION/PROBLEM:

Each month the amount of rows could be different (columns should be the same)... I have tried (3) different ways to replace the 256 with my variable name called "numbers"

SourceData:="claim_export!R1C1:R " & numbers & "C23" OR
SourceData:="claim_export!R1C1:R[" & numbers & "]C[23]" OR
SourceData:="claim_export!R1C1:R[" & numbers & "]C23

They all produce the same resulting error:

Run-time error '5': Invalid procedure call or argument.

Note* I am sure my variable is working, because when I "step into" (F8) my code and hover over my variable I can see it showing the number I expect.

View 1 Replies View Related

Use Same User-defined Constant In 2 Enum

May 11, 2013

I found out that using Enum one can have a dropdown box to choose from when inserting a parameter in a function.

But how can I have the same user-defined constant in more then one Enum? It works with standard VBA variables like vbEmpty, so why not with self made constants?

I tried this which gives the error: Ambiguous name detected

Code:
Private Const vbTest = 99 ''' = Ambiguous name detected !!!
Enum eTest1 '''gives 3 choices
vbEmpty ''' = standard VBA 1
vbLong ''' = standard VBA 3
vbTest ''' not in VBA, so I would assign 99 to it
End Enum

[Code] .....

View 1 Replies View Related

Subtracting Various Cells From One Constant Cell?

May 15, 2014

Say I have the number 325 in A1. Each time I type a number in A2, B2, C2, D2, E2, A3, B3, etc... I would like the number (325) in A1 to be reduced.

Also, how do I create a pull down list?

View 6 Replies View Related

Multiple Variables To Equal Constant

Mar 11, 2007

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 9 Replies View Related

Access Public Constant From Another Workbook's Vba

Jul 31, 2008

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 9 Replies View Related

Constant Line In Chart By Quarter

Dec 14, 2009

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 9 Replies View Related

Dialog Constant For The Security Form

Jul 23, 2006

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:

Application.Dialogs(xlDialogOpen).Show

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 3 Replies View Related

Named Constant To Return Sheet Name

Aug 23, 2007

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 4 Replies View Related

Detect If Cell Contains A Formula Or Constant

Dec 4, 2007

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 9 Replies View Related

Change Goals Each Item With Constant Variable

Oct 22, 2013

I have goal table on table A and i want it data fill to tabel B with constant sum. maybe like sudoku in sum right and sum down.

View 1 Replies View Related

Forcing Round Cell To Be Constant Number?

Dec 3, 2013

the way my spreadsheet's set up so far is that each employee should have a goal of, say, 100 for how many people they need to sign up. But they each have specific geographic breakdowns in their turf, some of which might be bigger or smaller than others, but at the end should all add up to 100 for each employee.

It's not super hard to do since I just take a goal of 100, in this example, and multiply it by the percent of population of the area in their overall assignment and give them a goal for that area based on that. So if Philadelphia has 70% of their total population, Upper Darby has 20% of their total population, and Phoenixville has 10% of their total population -- the goals would come out like this:

Philadelphia, Goal: 70
Upper Darby, Goal: 20
Phoenixville, Goal: 10
Ryan, Total Goal: 100

For some of the employees, that works out perfectly -- but based on the population sizes, the rounding sometimes gets a little off and it could end up like this:

Philadelphia, Goal: 71
Upper Darby, Goal: 19
Phoenixville, Goal: 11
Ryan, Total Goal: 101

For most employees, it ends up being dead-on 100 -- but there are some whose totals are at 98 or 99 or 101, and I was wondering if there was a way to force it to shave a point off or add a point on somewhere, pending on how close the decimal was when it rounded to make sure that it always ends up exactly on 100. (I'm using 'Data >> Subtotals' to get what's being represented above as "Ryan, Total Goal.")

View 10 Replies View Related

Pasting Constant Formula Where Referral Sheet Changes

Jan 1, 2006

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 10 Replies View Related

VBA In Constant Loop When Try To Refresh Other Pivot Tables

Jul 18, 2012

I have this code in an active worksheet that works with the exception that when I try refresh other Pivot Tables with the file that it goes into a constant loop.

Private Sub Worksheet_Change(ByVal Target As Range)
'
' Update the pivot table on this worksheet if data in columns A or B is changed
'
If (Target.Column = 1) Or (Target.Column = 2) Then
ActiveSheet.PivotTables(1).RefreshTable
End If
End Sub

View 2 Replies View Related

Constant In Formula To Change Based On Cell Value

Jan 3, 2008

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 9 Replies View Related

Use Cell Value As Constant (Const) In Macro Code

Jun 25, 2008

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 3 Replies View Related

ADVERTISEMENT