I have a spreadsheet where in the past we have used a turnover period (always 12 yrs) as our constant to calculate our average factors. For example we use formulas to calculate up to 12 years and the we just restart at year 13- providing that one of the formulas does not return a 0 value. I need to be able to choose our turnover period- say 8 years and have the values start then but in the next prokect I may need a 10 year turnover.
View 3 RepliesI noticed that sometimes if I stop a macro midway (completely stop, not just pause), and then re-run it from the beginning, my variables will still hold the value they were last set to right before i stopped the macro. But sometimes, this doesn't happen - the variables reset.
I'm wondering what defines how long the variable's value are held for? I'm considering writing a macro that can hold variables so that even after the macro is finished, the user can re-run and the variables will still be available. This is useful mostly for text boxes in userforms that will probably have the same values the next time the user runs, but the user can still change them if he wants.
I have four variables a1, a2, cr and s in the macro below.
Code:
Public Const cr As Long = 4
Sub Mode5()
'4 are the constants you can vary to test different scenarios
Const a1 As Single = 0.1
Const a2 As Single = 0.4
Const s As Single = 3
It is easier if think of these variables in this format (a1, b1, cr, s).
So taking the code above as an example they can be written as (0.1, 0.4, 4, 3).
Ultimately I would like to try different combinations of these values to get a desired outcome. Currently I change the values manually each time.
Variable a can range from 0.1 - 2.5 (25 numbers)
Variable b can range from 0.1 - 2.5 (25 numbers)
Variable s can range from 1 - 10 (10 numbers)
Variable s can range from 1 -10 (10 numbers)
write some code to iterate through different combinations of these variables. I presume it would be some sort of loop which increments each variable a certain amount each time until the max value is reached.
For example the first combination would be (0.1, 0.1, 1, 0.1) and the next would be (0.2, 0.1, 1, 0.1) and the next (0.3, 0.1, 1, 0.1) etc and the last one would be (2.5, 2.5, 10, 10).
Altogether there can be 25 x 25 x 10 x 10 = 62,500 combinations.
The attached table mimics the massive table that I am working with.
I wanted to populate values of two variables.
For e.g., I wanted to populate the yellow portion of the table with the value "A"; blue portion with the value "B"; Green portion with the value "3/1/1993" and so on (different colors were used to aid my question only, there are no such colors in the actual table).
I have about 700 such portions to be fill in.
My approach: I just started to copy and paste manually which is tedious.
Question: Would there be any automated way to do this?
I have declared a number of string variables named "hlpYear1", "hlpYear2" etc in the 'Worksheet_SelectionChange' function. "hlpYear1" contains the appropriate text to display if the selected language is English, "hlpYear2" is for French and so on. I was hoping that I would be able to use VBA to display the appropriate text by way of a 'For..Next' statement along the following lines:
View 4 Replies View RelatedI want to assign a cell a formula that changes base on variable, example
myVar=3
Cell(1,1).value = "=average(1:{myVar})"
what do i do to make this work instead of {myVar}?
trying to get it to put the 2 variables into 2 cells in the worksheet for the moment!
The Forms code is:
Private Sub CmdCancel_Click()
Me.Hide
Unload frmMsgBox
Exit Sub
End Sub
Private Sub CmdOK_Click()
Me.Hide
End Sub
It always fills the cells with False and blank, no matter what buttons are pressed (except cancel), so all I can think is that the information isn't feeding back through properly from the form.
I understand the use of Global variables in the sense that they can be used throughout the project. However, I also understand (I think) that unless these variables (like any) are released from memory or the values changed by some other means they will retain their values when accessed inside a another procedure regardless of where the calling procedure is Public or Private. This seems to be a convenient way to access the values of these variables without having to make ByVal or ByRef declarations in the procedure calling process.
Firstly, is my understanding of this correct? (That the variables and associated values) can be accessed from within a calling procedure without formally passing them in?
Second, even if this is true is it considered to be bad practice because the variables will store the last known value which may or may not be correct if you aren't paying attention to how the variable was last set.
My data: four variables A,B,C and D.
A growing dataset representing a group of people being nominated in different weeks for different jobs in different locations. Within a week and over several weeks an individual may be nominated for any number of jobs, in any number of locations.
A=week number (52 possibilities), B=job code (20 possibilities), C=location (2 possibilities), D=name (500+ possibilities) .....
For example, I have the numerical value "400" in R1C2 and that value would be used in the name "D400" which is to be assigned to R1C1:R5C2.
The code I have is:
Range("R" & DRow & "C1:R" & DRow + 4 & "C2") = "D" & Cells(DRow, 2).Value
but is throwing up the error:
"Run-time error '1004':
Method 'Range' of object '_Global' failed
DRow increases dependant on other variable data.
Also, am I right to be using R1C1 instead of A1, or can the same be achieved using A1 references? If so, I think I may not be able to see the wood for the trees.
I have 2 worksheets. The first sheet has data of which some needs to be copied to a second worksheet. The trigger is a value found in column E. If a match is found, then a copy statement needs to be built. The values in F, G & H are the values to be used in the copy statement. The content of cell H may be numbers or letters or both.
What is the correct format of the copy statement?
Dimension all variables
Dim RowPointer As Long
Dim wbContrib As Workbook
Dim wbMaster As Workbook
Dim SheetName As String
Dim target_sheet As String
Dim target_cell As String
Dim Target_value As String
Dim CellAddr
'
' Initialize variables
Set RowPointer = 1
Set wbMaster = “Master.xls”
'
'******************************
I have a worksheet which I've set to read-only, I am trying to get it to pull in information from external worksheets. I've created a Macro that will automatically bring in the data however it will only go into a pre-defined cell/row. I have another Macro which looks up the next available row (which is where I want the data to go) but can't get it to link into the macro's. In short, Macro1 brings in data, Macro2 finds next available row, Macro3 brings in another worksheet, Macro4 finds next available row etc etc.
Sub Macro3()
With ActiveSheet.QueryTables.Add(Connection:= Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=C:Documents and SettingsjspencerDesktopExcelAMMH.xl" _
, _
"s;Mode=Share Deny Write;Extended Properties=""HDR=NO;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database" _
, _
" Password="""";Jet OLEDB:Engine Type=35;Jet OLEDB:Database Locking................
I have three variables; Independent variables x & y, and dependent variable z. x & y can take the values 1,2,3 and can equal each other z can also take on values 1,2,3 but can never equal x or y. I tried using if/and statements to solve this (see below), but I would have to do it for each case and it exceeds Excels embedded function limit.
=If(and(B7=1,B10=1),riskduniform(E4:E5),if(and(B7=1,B10=2),3,if(and(B7=1,B10=3),2,if(and(B7=2,B10=2),riskduniform(1,3),if(and(B7=2,B10=1),3,if(and(B7=2,B10=1),3,if(and(B7=3,B10=3),riskduniform(1,2),if(and(B7=3,B10=1),2,if(and(B7=3,B10=2),1)))))))))
I have a query about using Array Variables in excel VBA. I have a set of lookup tables and a main data table. The data table will be downloaded everyday. I need to replace the ID's in the main table with actual data from the Lookup tables.
e.g.
Main Data Table
Color Operator
005--325 005
004--326 004
003--327
001--328
002--322
Lookup Color
001 - Red
002 - Blue
003 - Green
004 - Violet
005 - White
Lookup Operator Table
323 - Operator 1
324 - Operator 2
325 - Operator 3
326 - Operator 4
327 - Operator 5
328 - Operator 6
I have a huge amount of data in the main table. So I can't use a lookup formula for automation. Rather I would like to use VBA to create array variables, scan and copy the lookup data into the array and replace the ID's in the main table.
Only that I am unable to achieve this using arrays. I am very basic in executing code related to Arrays.
I've a big problem getting the Constraints in excel solver to use variables.
It seems that the only accepted variable type in the contraints are STRING variables. THe following simple example works AS LONG AS the variable apa is a STRING value and as long as it doesn't contain decimals....
So the question is. How to use varialbes in the FormulaText argement other than string values as integers?
I want to find the first topper student in each subject but the formula is not giving correct output.
View 14 Replies View RelatedIn cell G2 I have a formula that adds the values in column C that are labelled in column B as either 'Apple' or 'Grape'. This is accomplished in the formula using {'Apple', Grape"}. Is it possible to change the formula so that the {'Apple', Grape"} can be replaced with a reference to a range in which the names to be used in the formula will be listed (for example in J1:J3 in the attached file)?
Book6.xlsx‎
I have a column of names. Most of these data show multiple times. Another column has values. I'd like to find the maximum value for each name.
This solution might correspond to "=MAX" the way that "=SUMIF" corresponds to "=SUM".
Note: the table has over sixteen thousand rows; some solutions may work technically but not be practical.
How can I create a macro for excel to delete rows that have the SAME subject? (delete all duplicates - the price etc on that row may differ but as long as the subject is the same, I was to delete it off)
View 4 Replies View RelatedI am using the following code to automatically send an attachment to a specific email with a specific subject line.
ActiveWorkbook.SendMail Recipients:="johndoe@yahoo.com", Subject:="General subject"
ActiveWorkbook.Close SaveChanges:=False
Is there a way to reference a specific field in the active workbook as the subject line?
I've been asked to write a multiple if statement for someone here at work but I've been trying all day with no success. Basically, the OS liabilty in column C cannot exceed £2500. Here is an example inc the expected results............
So in row 1 the expected result is 500, as even though there is a 1000 reserve, 2000 of it has already been paid In row 2 nothing has been paid in column A so the OS liabilty in column C is the reserve up to a max of 2500 In row 3, there is 5000 in reserve, but 100 has been paid so the OS liability in col C must be 2000
In row 4 nothing has been paid in column A so the OS liability is the reserve in column B In row 5 150 has been paid, 150 is in reserve, so 150 is the OS liability
In row 6, 2500 has been paid, but even though there is an amount of 300 in reserve (col B), nothing is expected in OS liability as the 2500 max liability amount has been reached.
Unzip Code - Works without Variables, Breaks with Variables.... This has been driving me bananas...
I have the
I would like to select File/Send to/Main Recipient (As Attachment) within my Excel Spreadsheet. Within the email, I would like the subject line to be automatically filled in with an entered value of a cell block from within the spreadsheet.
For Example, If cell block A1 had Thursday entered in the cell. Then once I chose send to/Mail Recipient, The subject line would read Thursday.
A1: John Black B1: Jane Black C1: John Black D1: John Black
A2: 24 B2: 32 C2: 23 D2: 12
If I choose "John Black", I would like a list generated that lists 24, 23, and 12 in row 3, in separate columns, but no empty columns between the numbers.
Please check this sample file. Say for example; I am sending this file via mail to different agencies. See in this file sheet named base & column title Agency , filters concerned agency wise and send via outlook to the concerned agencies mail id from mailinfo sheet. What my concern is since these are different agencies, I wanted to bring the agency name in the mail subject.
Link below:- [URL] ............
I currently have the below in my VBA which adds the text to the subject of an email:
Application.Text(today - 1, "MM-DD-YY")
today = Int(Now()) is part of the macro. How do i format this to display workdays only.
For instance if today is 07/21 (monday) i need this to display 07/18 vs 07/20.
In excel that function would be =WORKDAY(A1,-1) where A1 is =Today(), just not sure how to translate this to VBA.
I have a simple sheet with a list of costs (small version attacheds as there are 430 items on full list) when i enter "Y" in the move to invoice column I would like the description, multiple and cost details copied to invoice sheet. The added complication for me is that if for example when there is a requirement for multiples of that cost to be added to the invoice.
View 9 Replies View Related cannot seem to get a function that works.
I have a sheet named "Master" with student reference numbers (A2), student names (B2/C2) and their four subjects (D2, E2, F2, G2).
I need a separate sheet for each subject, so potentially 30 additional sheets, and i would like for where a subject is mentioned in one of the four columns of the master, the students reference and names would then be copied and pasted into a row on that subject specific sheet. Leaving the subject sheets with a list of names and numbers of the students studying.
So you would have a student on the master sheet and then they would appear on four additional subject sheets.
Ideally it would also update subject sheets as new students are added. And additional information is being added to the subjects sheets so they cannot be cleared.
I would like to create a list covering multiple columns.
I would like to have 5 columns which will contain the same value/ (word).
If I select,that value from the list, I would like to have each row the word is
found be displayed regardless of which row it is found.
I am trying to send out an e-mail from excel with all kinds of combined info. Here is what I have so far, which works to send out an e-mail to a certain address with a compiled subject. No luck with inserting the BODY of the e-mail yet from a certain cell.
=HYPERLINK(CONCATENATE("mailto:",E32,"?subject=",F32))
Also, How do I make the text in each of these cells read "send e-mail" rather than the whole jumbled mess that is currently in there from the combination of the cells?