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.
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.
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 = "-"
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
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.
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 RelatedI have 800+ files the problem is that the file name ends in 80 different combination so I need to try all of those for each file.
eg: one of the 800 is "109 st no 103 av" the file could be called:
"109 st no 103 av nb1_cleaned.xls" or
"109 st no 103 av nb 1_cleaned.xls" or
"109 st no 103 av nb 1._cleaned.xls" or
"109 st no 103 av sb1_cleaned.xls"
.
.
.
.etc
I wrote a code to try all those combinations, the issue lies a space the code adds before _cleaned, how to remove it?.
So the name should be
"109 st no 103 av nb1_cleaned.xls"
but my code is letting it be
109 st no 103 av nb1 _cleaned.xls
Where the variable Ord is the "1" after nb.
Find the code below:
[Code] ....
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.
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
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?
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.
In cell A1 exists data. In cell A2, I would like to test A1 to determine if A1 is a formula or a constant.
Examples:
=A9+3 would result "formula"
="1+3 would result "formula"
3 would result "constant"
'Blahblah would result "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.
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.
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?
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?
I have a template with formulas calculating a default value, but still allowing the user to override the cells with direct input.
I want to use conditional formatting to highlight any cells that have been overwritten, but can't find a way for Excel to differentiate between a cell with a formula or an inputted constant.
I realize there is a VBA "isFormula" function, but I don't want to have to use VBA for this.
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 2 Replies View Relatedi 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:
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.
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?
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].....
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.
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.
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 ...
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
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.
View 5 Replies View RelatedI 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 RelatedOk, 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...