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
ADVERTISEMENT
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
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
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
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
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
Jun 7, 2014
I 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] ....
View 2 Replies
View Related
Jul 30, 2009
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 2 Replies
View Related
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
Feb 19, 2010
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 6 Replies
View Related
Jun 18, 2012
I need to create a new variable according to the formula:
Suma( Foreign_Owenrship_Plant * Number of Full Time Employees)/ Suma(All Full Time employees in the sector)!
However previosly I need to filter the firms within a sector within a country according to a three digit ISIC code. Lets say in Albania, in year 2002 in the Transport sector there are 5 groups of 3 digit ISIC codes. Once I have filtered them then I can proceed with calculating the new variable. How can I do this using programming?
View 1 Replies
View Related
Aug 4, 2012
Using VBA how can I make a variable name using the cell value.
Like if Range A1 value = "X1" and Range B1 = 1001
I would like to end up with Variabel (X1 = 1001)
View 1 Replies
View Related
Jul 11, 2014
I am working on a set of Books to be published together. I am attempting to count the number of times a Book contains a glossary word that only appears once in the whole series. Conceptually I need to check if a word in A1 == any word in Sheet2 A:A, if so then does the value in Column B == 1, if so increase count by 1; if not proceed to A2 == Sheet2 A:A, and so on.
If this were a basic program I would do as follows:
C==2 **place count
s==size(A:A) ** check for length of column A (which I know is 179)
if C<=s
match (A'C', Sheet2 A:A)
if B'C' = 1
COUNT == COUNT + 1 ** COUNT is the number of times a book contains a word that only appears in that book.
Else
[Code]...
Here is a sample set up
Screen.jpg
View 5 Replies
View Related
Oct 24, 2011
I have the following code.
Code:
ActiveSheet.Range("$A$1:$AF$" & lngRows).AutoFilter Field:=x, Criteria1:=Array("ELDU238", "ELDU239", "ELDU240", "ELDU241", "ELDU242", "ELDU243", "ELDU244", "ELDU245", "ELDU246", "ELDU247"), Operator:=xlFilterValues
The values ELDU238 - ELDU247 are values in a column beginning at A2. I want to create
"ELDU238", "ELDU239", "ELDU240", "ELDU241", "ELDU242", "ELDU243", "ELDU244", "ELDU245", "ELDU246", "ELDU247"
in a string variable strArray which will enumerate whatever values are in column A beginning at A2 and create the above. I can then have
Code:
ActiveSheet.Range("$A$1:$AF$" & lngRows).AutoFilter Field:=x, Criteria1:=Array(strArray), Operator:=xlFilterValues
Hence if the number of entries changes e.g. say I have
"ELDU238", "ELDU239", "ELDU240", "ELDU241", "ELDU242", "ELDU243", "ELDU244", "ELDU245", "ELDU246", "ELDU247", "ELDU248"
the code will still work.
View 4 Replies
View Related
Nov 25, 2013
I am trying to create a 2 variable data table. I have the variables in a different sheet from the datatable sheet, and I read here XL: Error Message: "Input Cell Reference Is Not Valid" that if I link these variables to the active sheet where the datatable is located it should work, but the table is showing the same result in all the fields.
When I put the put the variables value in the same sheet as the datatable and link to these values from the other sheet the table is working fine, so I know that my design is correct but it has to do with referencing or something else that I need to set but I am not sure what it is
View 1 Replies
View Related
Feb 3, 2008
I hv following table :-
Summary Report FGH1how urgent How ImptRank2highHigh 3midhigh 4LowHigh 5highmid 6Midmid 7Lowmid 8Highlow 9midlow 10lowlow Spreadsheet FormulasCellFormulaF1=+'Mega_Variable (#1)'!R4G1=+'Mega_Variable (#1)'!R5F2=+'Project (1) '!U63G2=+'Project (1) '!U64F3=+'Project (10)'!U63G3=+'Project (10)'!U64F4=+'Project (12)'!U63G4=+'Project (12)'!U64F5=+'Project (5)'!U63G5=+'Project (5)'!U64F6=+'Project (7)'!U63G6=+'Project (7)'!U64F7=+'Project (9)'!U63G7=+'Project (9)'!U64F8=+'Project (4)'!U63G8=+'Project (4)'!U64F9=+'Project (2)'!U63G9='Project (2)'!U64F10=+'Project (3)'!U63G10='Project (3)'!U64 Excel tables to the web >> Excel Jeanie HTML 4
I need code , when run it will fill in the ranking number :-
Summary Report FGH1how urgent How ImptRank2highHigh13midhigh24LowHigh35highmid46Midmid57Lowmid68Highlow79midlow810lowlow9Spreadsheet FormulasCellFormulaF1=+'Mega_Variable (#1)'!R4G1=+'Mega_Variable (#1)'!R5F2=+'Project (1) '!U63G2=+'Project (1) '!U64F3=+'Project (10)'!U63G3=+'Project (10)'!U64F4=+'Project (12)'!U63G4=+'Project (12)'!U64F5=+'Project (5)'!U63G5=+'Project (5)'!U64F6=+'Project (7)'!U63G6=+'Project (7)'!U64F7=+'Project (9)'!U63G7=+'Project (9)'!U64F8=+'Project (4)'!U63G8=+'Project (4)'!U64F9=+'Project (2)'!U63G9='Project (2)'!U64F10=+'Project (3)'!U63G10='Project (3)'!U64 Excel tables to the web >> Excel Jeanie HTML 4
View 9 Replies
View Related
Oct 17, 2006
I would like to create new empty folders from the list in column B.
The number of folders will vary depending on how many entry in column B.
I need the folders to be created in an existing folder in the "current directory" called "Shop_Drawings".
The following code may be able to be modified.
Sub CreateFolders()
Dim MyFile As String
Dim sDir As String
Dim rng As Range
Set rng = Sheets("Matdata").Range("B2")
While rng.Value <> ""
MyFile = rng.Text
sDir = "CurDirShop_Drawings" & MyFile
''above is where I am having trouble...don't know the correct syntax''
MkDir sDir
Set rng = rng.Offset(1)
Wend
End Sub
I have attached a sample workbook.
I have been getting by with code which requires changing the destination in the module whenever making folders in different diectories or drives.
View 6 Replies
View Related
Dec 8, 2012
VB:
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="Work_Log!" & Sheets("Work_Log").Cells(b, 1), TextToDisplay:="View"
I want this to link to Work_Log!.Cells(b,1), however instead of grabbing the specific cell, it is pulling the contents of the cell and creating a URL of that. For instance if b = 1 and cell A1 contains the value "yellow" then this code is giving me the hyperlink to WorkLog!Yellow when I want it to read WorkLog!A1
View 2 Replies
View Related
Oct 26, 2007
Let's say I have cell A1 on Sheet 1 of Book 1. I need this cell to be a range variable for a macro in Book Two, but cell A1 varies from time to time (A1 this time, A7 next time).
In Book Two, I write "A1" as the contents of cell C3, for example. How do I turn that string into a range variable for use, not the containing cell? Since the cell needs to manually changed each time I run the macro, having it as a text field somewhere on my Book 2 Sheet seems the only simple way to identify it but IS there another way? Selecting it as the active cell and using another subroutine defeats the use of the macro.
View 4 Replies
View Related
Jul 11, 2014
I've only done a small amount of macros with VBA.
I want to create a macro to insert 12 cells left of a column labeled "This Year" As the spreadsheet grows (by 12 columns @ year) the "This Year" column moves to the right. Thus I need to reference the range off of that column and then insert 12 columns directly to the left of it each year. Can I somehow reference the label "This Year"?
I then need to enter the month labels in the new columns row 8.
My problem is trying to reference off the "This Year" column.
View 8 Replies
View Related
Dec 23, 2013
I have an excel file with a large amount of employee data in it and want to create a search facility that will run on variable search functions and display the information on the screen
I want to be able to enter variable search functions as follows:
Employee Number; shows all information on employee
Division: shows all employees in division (possible from a list of all divisions)
Appraisal Eligibility : Applicable shows all the applicable employees
Job Role: shows all the employees with the same job role (possibly from a drop-list of all roles)
I know its easier in Access, but all records in Excel as a legacy and don't have time to create an access database currently.
View 1 Replies
View Related
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:
View 2 Replies
View Related
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
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
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
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
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
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
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
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
