I have a list of employees in a spread sheet. I am trying to add a macro that will add to the bottom of the list. I have managers that are not familiar with excel so I do not want them to just add lines. I have written a macro that will add lines or subtract, but I want the macro to always add at the bottom of the speadsheet.
View 9 Replies
how to achieve what I'll call "parameter driven" code where the "parameters" are variable names.
For example, in the following code... I'm looking for an answer to the question posed...
Sub Test_Sub()
Dim vX, vY, z
vX = 10
vY = "vX"
For z = 1 To 3
'This next line generates a Run-time error... But...
'=====================================
MsgBox z * vY '
Is there any way of referring to a variable name based on the value of another variable? I have the following code: (where the value for "aux" is defined somewhere else)
Dim aux As Integer
Dim number1 As Integer
Dim number2 As Integer
Dim number3 As Integer
If aux = 1 Then number1 = 1
If aux = 2 Then number2 = 1
If aux = 3 Then number3 = 1
The variables "number1", "number2" and "number3" are always filled with the same value ("1"). Is there any way of writing the "If-part" of the code in only one line? I was thinking that maybe there is a way of calling "number1", "number2" or "number3" depending on the variable "aux". For instance writing something similar to: number + aux = 1
I have counted the number of rows using i. Now I need to pass that variable to the ActiveCell referances shown.
Do While Not Selection. Offset(i, -6) = ""
ActiveCell.FormulaR1C1 = "= round(R[i]C[-8],2)&""±""&round(R[i]C[-7],2)"
ActiveCell.Offset(0, 1) = "=round(R[i]C[-7],2)&""±""&round(R[i]C[-6],2)"
ActiveCell.Offset(1, 0).Select
Loop
End Sub
I'm working on a spreadsheet that will access two weeks worth of data at a time. Is it possible to have the filename reference contain a variable or a link to another cell in it?
For example: ...
Dim vCriteria3 As String
vCriteria3 = ActiveCell.Text
Sheets("CUSTOMER OVR REVIEW").Select
Rows("25:25").Select
Selection.Copy
Rows("26:" & vCriteria3).Select
ActiveSheet.Paste
it hits the
Rows("26:" & vCriteria3).Select
and crasses with a "incorrect type" mesage. am i storing this into memory incorrectly? how do i fix it? the function is intended to use a variable to paste over x amount of rows.
Ive just finished writing a macro that copies data from different parts of one workbook into a new workbook. However after doing all that my boss now informs me that the first workbook will be renamed regularly.
Is there a way adding a reference to a workbook that is not dependant on the name? Possibly declare it as a variable at the beginning or something? The indexing is not possible either as they might have multiple Spreadsheets open at any one time.
I'm wondering if there's any way to put a variable for a cell address in an external reference formula. Basically, I'm creating an abridged report of customer data that takes a single customer's info from 2 other workbooks on a networked drive and compiles the chosen cell data into one worksheet summary of their info. The base external reference formula looks like this:
='F:Projects[CustomerDB.xls]Sheet1'!VariableCellGoesHere
I want to know if there's a way I can make the formula use a variable cell address that I can just type into another cell to tell it which row I need to grab info from. Generally, all of the customer data I need is spanned across multiple columns in one single row. The formula above appears multiple times in the sheet I'm trying to make. The first is for "Name", the next is for "Address", next is "Phone Number", etc. So for instance, if I want customer data from the main spreadsheet and that customer's info is on row 355, my first few formulas are:
='F:Projects[ProjectDB.xls]Sheet1'!$A$355
='F:Projects[ProjectDB.xls]Sheet1'!$B$355
='F:Projects[ProjectDB.xls]Sheet1'!$C$355
Is there a way I can maybe have a designated cell where I just enter the row number I need (e.g. 481) so the formula sees that number and plugs it into the cell reference above resulting in the formulas adjusting themselves to ='F:Projects[ProjectDB.xls]Sheet1'!$A$481?
I have a formula that pulls data from another sheet using standard reference of sheetname! I want to build this forumla to use several sheets, and would like to pull the sheet names from other cells.
For example... instead of LEMONS!a1 and LIMES!B2
I would like to use REF1!a1 and REF2!B2
where REF1 and REF2 are pulled from cell Z1 which contains test 'Lemons' and cell Z2 which contains text 'Limes'
How can I do this?
Why this doesn't work?
I get the error message: Invalid Next control variable reference
And it highlights the i in 'Next i'
[Code] .....
I need to reference a different workbook in a while loop. Each iteration, PopFive increases by 3, as I want to pull the numbers of every third row. How does the syntax of the line go after the " ! "?
[Code] ......
I have a spread sheet that lists a bunch of dates and expected volume that will be associated with the date. I need to build a macro that will look at the first date expand it out for several months then look at the volume and break that out over a week's time.
That part is simple enough but I need to turn this into a looping statement and I cannot figure out a way to get my macro to always come back to the next date in my list.
Ideally I would like something that is a variable that references the cell I want so I can do something like this.
Code:
MYRANGE = range("B2")
Do
range(MYRANGE).select
[Code]....
This should work for me perfectly if I can just figure out how to make the MYDATE varaible reference a sepecific cell rather than a value or text in the cell.
I'm trying to use a variable as a cell reference in a formula using '&', but can't seem to get it to work. I've looked through the forum, which have examples of how to do this using a range, but not just as a single cell reference. This puts the formula '=VLOOKUP("A & lastrow &"" , Vlookupinput, 2,0)", which obviously gives me an error.
My code is:
Code:
Dim lastrow As String
lastrow = Range("A" & Rows.Count).End(xlUp).Row + 1
ActiveSheet.Range("A" & Rows.Count).End(xlUp).Offset(1, 1).Select
ActiveCell.Formula = "=VLOOKUP("A & lastrow &"" , Vlookupinput, 2,0)"
i'm trying to use the sum across sheet function - i.e. - =SUM(Start:End!B1)
Whereby "Start" and "End" are the two relevant sheet names and "B1" is the cell I want added up.
However, can I make B1 variable, whereby there is a cell, say in A1, that contains the string "B1", so that I can make it sum C1 or B20 or anything on the other sheets?
I've got the following code, setting up and initializing a Public variable for a sheet, but I've found that it's has no value after the Auto_Open is run,
though it works fine in the procedure Auto_Open calls.
I can get around it by reSetting it in the module I need it in,
but that kind of defeats the purpose of having a Public variable:
code in Module1
Public parSheet As Worksheet
Sub Auto_Open()
Set parSheet = Worksheets("Params")
Call Initialization.BuySellInit
End Sub
I have the following line in VBA which works.
[Code]....
I have a variable for the column adress of the second part of the formula so it looks like this:
[Code] ....
But it says I don't have the correct syntax.
Not been around for some time now but have to do my annual update for this workbook and implement some new changes.
In the attached example workbook, I have created some formulas which reference a particular cell for each client e.g.
I am building an Excel sheet for my company which keeps track of who has done what with a new client in a step by step process. I have it set up so that once a department clicks complete in a box it automatically emails (VIA a macro) the next person who needs to do the next task. Some tasks are done by the same person no matter what, other tasks are done by a project leader which is different depending on the client.
The problem I'm having is that, within the macro, I need to reference an email address in a cell X cells to the left in the ".TO" line.
So, in Column A I have the CLIENT, Column B The PROJECT LEADER, and Column C The PROJECT LEADERS EMAIL. Then the next 22 Columns are labeled steps with drop down boxes, once the have selected "COMPLETED" it triggers the macro and send the email, just not on the ones when it needs to email the PROJECT LEADER, since it's a variable. Below is the macro, how I can have the ".TO" line in the email reference the email address in Column C to the left of it?
Sub Mail_Workbook_1()
Dim OutApp As Object
Dim OutMail As Object
[Code]....
I'd like to assign a value located in a cell in a workbook to a variable in a VBA program in another workbook. I do NOT want to open the workbook that contains the cell with the value. I can't get the hang of referencing an external cell reference. Let's say there's a value in a cell named "nbr" in a workbook named "tst.xls" located in a path "c: est" that I want to assign to a variable "tstnbr" in a VBA program, what is the best way to code that?
View 8 Replies View RelatedI want to create a formula of the form: =INDIRECT(ADDRESS(4,2,2,TRUE,($A9) and insert this in a cell BUT the $A9 reference needs to reference the row of the cell where the formula is being inserted which will vary. ie. A30, A31, etc. How do I do this?
View 2 Replies View RelatedI have a workbook with multiple sheets. There is an overview sheet and then 14 sheets allowing for 14 days worth of schedule information. There are then 3 sheets following the 14 days to total some information. The workbook users frequently change the sheet names of the 14 sheets to reflect days of the week.
I'm creating a copy for distribution that takes the active workbook and copies the values to a new workbook. There are columns that I would like to delete from the 14 day sheets and that would be easy enough if the sheet names were never changed.
The code I'm using currently follows (my thanks to Turtle 44 for helping on that section)
Sub Copy_Visible_Sheets()
Dim arr() As String
Dim i As Integer
Dim WB As Workbook
Dim WS As Worksheet
Set WB = ActiveWorkbook
Application. ScreenUpdating = False
'Make sure template is saved as .xls
If Not ThisWorkbook.Saved Then
MsgBox "Please save this workbook before generating a Client Copy."
Else
I have the following code which Clears the content of a cell. The next thing I want to have happen is to have another cell equal a formula but make that formula relative to its place. The formula is the following =IF(ISERROR(VLOOKUP(F56,Routes_All,2,0)),0,VLOOKUP(F56,Routes_All,2,0))
I am not sure how to paste it where the row changes depending on where it is pasted. Here is the code that does not work...
Private Sub CommandButton1_Click()
Worksheets("2008 Log").Select
Dim cRow
cRow = ActiveCell.Row '
Cells(cRow, Range("Column_Type_Of_Ride").Column).ClearContents
Cells(cRow, Range("column_duration").Column).value = "=IF(ISERROR(VLOOKUP(F56,Routes_All,2,0)),0,VLOOKUP(F56,Routes_All,2,0))"
End Sub
As you can see, no matter where I paste it it will always refer to row "F". How can I have it refer to row "cRow"?
I have a sub that I need to call multiple times. I'm trying to figure out if there is a better way to do this rather than write out the call each time:
AYForm = "UserForm2.txtAYMonths" & i
AYPForm = "UserForm2.txtAYPercent" & i
SummerForm = "UserForm2.txtSummerMonths" & i
SummerPForm = "UserForm2.txtSummerPercent" & i
JobForm = "UserForm2.cboJobClass" & i
NameForm = "UserForm2.txtName" & i
For i = 1 To 15
Call FormatAY((AYForm), (AYPForm), (SummerForm), (SummerPForm), (JobForm), (NameForm))
' Call FormatAY(UserForm2.txtAYMonths & i, UserForm2.txtAYPercent & i, UserForm2.txtSummerMonths & i, _
' UserForm2.txtSummerPercent & i, UserForm2.cboJobClass & i, UserForm2.txtName & i)
Next i
the sub FormatAY has six arguments; 5 textboxes and 1 combobox. Since the names of these items are the same except for a number (example UserForm2.cboJobClass1, UserForm2.cboJobClass2, UserForm2.cboJobClass3 etc...), I wanted to write a for loop and use a variable to represent the last number. Is this even possible? The couple of ways that I have tried this, I get either a member not found, or ByRef error.
I will show with an example of what I would like to do. I italized and underscored the part of the formula.
---A----B
1 24 formulaB
formulaB = "= CELL("contents",DifferentSheet!G(A1))"
I want B1 to contain the value from the cell G24 in sheet "DifferentSheet". I want to use the value in A1 to determine what row on "DifferentSheet" I should use.
I am trying to do a "getpivotdata" formula on a cell in certain file that gets info from a pivot table on another file. However the name of the second file (the one where the pivot table is found on) might be different each time so I created a variable for the second file name but I cant figure out a way to use this variable in the get pivot data formula as I am not very experience with VBA.
Template = Application.InputBox("What is the name of the file ending with (.xls)")
If Template = False Then
cont1 = MsgBox("Please write the name correctly using (.xls)!", vbOKOnly)
If cont1 = vbOK Then
Exit Sub
End If
End If
I am an architect and recently I was required to do some extensive calculation relating to water requirements and toilet requirements for a building that i was designing. i created a excel file, with a basic vlookup function to put certain values in the cells that are pre-defined for a particular building typology (mostly created by governmental bodies as guidelines). for example, when i choose a particular type in column E, column F and G fills up automatically. now this table also gives me occupant load based on what is the area i enter in column D.
The problem is that I want to automate the calculation of toilet requirements. Now, each typology will have a different formula to calculate WC/Urinal/washbasin.
1. there will be 17 different formula - one for each typology (as shown in sheet 5)
2. Column j will calculate what is the number of WC required based on 2 criteria - first id what is the typology and then looking what is the number of occupant.
The problem here is for example, if in E9, selected typology as "offices", the formula of offices typology will be used in J9 and occupant value from H9 will be used but if i have E18 also selected as offices typology, the same formula will use occupant value mentioned in H18 instead of using H9.
Hence, excel will first have to look what is the typology selected. based on that, it will load formula - out of that 17 different formula - specific to that typology and finally it will refer to corresponding row for occupant load and will use it in the formula to get the result.
I have read meanwhile dozends of articles and comments about absolute and relative cell references.
But I found nowhere an example on how to make a the column part of a cell reference variable and the row absolute.
Is
K$10
a valid expression?
In opposite to $K10 is must work this way
Excel 2003 > Attached is a small model of what I am trying to accomplish. Cells B2 and B4 contain the same formula
a formula that calls a simple function. The function has a variable passed to it
and the value of that variable changes depending on the location of the cell. Now, see the function in Module1
it is called CellCalc. If the variable passed = Jim then value = 3. If the variable passed = Jack then value = 8. That is straightforward.
Note also that when Sheet1 is activated, I calculate the cells from left to right and top to bottom
that is important. Here is the challenge. If B2 > 1 then I want to add B2 to B4 and set B2 to 1. I can set B4 properly but I cannot reset B2. You can see my 2 attempts that are commented out. Is there some way of accomplishing this
or am I simply stuck in a circular reference?
The alternative to this is to write a function that operates externally on these cells. That will work for sure but then the values of the calculations will overwrite the functions in those cells, thereby taking away the dynamic nature of this application.
I have a monitoring system that records a data point with a date/time stamp several times a day at random intervals. For each reading I want to calculate the change compared to the first reading that was more than 24 hours ago, which could be anywhere from 1 to 20 rows above the current one. Hence with the timestamp in col A and the value in col B, the formula in col C, for example cell C20, needs to read something like =B20-Bxyz, where xyz is the row number of the first reading that is more than 24 hours, i.e the first row xyz where A20-Axyz >1.
View 4 Replies View RelatedI have written the following VBA Script but am getting an error message saying -> "Compile Error. Invalid Next control variable reference".
Code:
Sub Form1() 'Change Yes/No
Dim bottomT1 As Integer
bottomT1 = Sheets("Data Sheet").Range("T" & Rows.Count).End(xlUp).Row
Dim bottomP2 As Integer
bottomP2 = Sheets("Follow-Up").Range("P" & Rows.Count).End(xlUp).Row
Dim bottomO2 As Integer
bottomO2 = Sheets("Follow-Up").Range("O" & Rows.Count).End(xlUp).Row
[code].....
