I have a vba script that matches a date on two sheets then copies a formula based on the location of the date, the problem I am having is how can I refer to the activecell and active column without typing them manually as the range will change each day. See vba script below:
VB:
Sub find_date()
Dim FindString As String
Dim Rng As Range
[Code]......
I have a number of worksheets and need to move data from the same cells of each work sheet to a summary worksheet. I manually entered the cell id's for the first sheet.
Example =Jan!M6, =Jan!N7, =Jan!O9 etc.
I know I should be able to automatically enter these same cells for the remainder of my worksheets with out manually going in and typing each one (there are a lot of entries.) I just can not remember how to do it. The next sheet is named "Feb" and "Mar" etc.
I have been looking for a way to enter the folder where the excel file is located into a cell.
I have seen you can add the whole path (=CELL("filename") ), however I am only wanting the 'last' folder it is in. For Example:
C:docsfolderdataClass D4John WayneProfile.xlsx
Would enter "John Wayne" into cell. The cell is namely the "name" cell.
I work in a school and a teacher is wanting a couple of different excel files for each student (600 students) I am hoping that I can put the files in a folder with the students name as the folder. That way at least its only 600 names and not 600x7! The other details are similar on a lot of the files such as class (this is also one of the folders in the path).
I am about to take a corporate finance course. The textbook uses a ton of basic (complex to me) equations to calculate interest rates, bond premiums, dividend reinvestment scenarios, etc.
I know that Excel has a bunch of specific functions to calculate interest, NPV, etc. but I am NOT interested in using those because I would like to have Excel "do the math" once I enter the formulas (or parts of formulas) correctly.
how to enter the equations below into Excel without using built-in formulas (I'm also not looking to use the Equation editor because I actually to calculate the answers, not just have the visual look/feel of the equation).
1R2 = [(1 + .0194)(1 + .03)]1/2 - 1
If done correctly, 1R2 in the above should equal 2.47%. I am totally fine doing parts of this in separate cells if necessary.
And something more complicated like the below. I wrote out the word "delta" because I don't know how to create the "triangle" symbol for delta on this forum post. According to the text, for securities with semiannual receipt (compounding) of interest, it is represented as:
-D = (delta P / P) / ((delta r sub b) / (1 + (r sub b)/2)))
Or in English, I believe that can be expressed as "negative D equals the quotient of delta P divided by P (in the numerator) divided by the following: delta r sub b divided by the quantity 1 plus r sub b divided by 2"
And finally
V sub b = 1000 / [1 + (.10/2)]2(4) + 50 times the following compound bracketed expression [1 - (1 / [1 + (.10/2)]2(4)] divided by (.10/2)]
I have a column of dates that are not being recognized as dates unless if I manually select each cell and press enter. For example the cell value is "Jul/13' and isn't recognized as "01/06/2013" until I select it and hit enter. How can I get around this?
View 8 Replies View RelatedI'm trying to make the below two column selections into a selected range to clear out formulas/values in the range.
Current code segments:
ActiveCell.EntireColumn.Select
Columns(ActiveSheet.UsedRange.Columns(ActiveSheet.UsedRange.Columns.Count).Column).EntireColumn.Select
Is there a way to combine the two code segments into one selected range?
setup:
Columns: c)6:00 AM, d)6:15 AM, e)6:30 AM
Rows: 4)tech1 Phone, 5)Tech2 Phone, 6)Tech3 Phone
What I am tying to do is to select the column that matches the time, for instance anything before 6 AM would select Column C, betweeb 6 and 6:14 still selects column C, 6:15 to 6:29 selects column D, Etc
i have a sheet for monitoring sickness. i have used a macro before which automatically selects a range and emails it to recipients when i press a button. what i would like to do is have excel automatically choose the range for me based on today's day. in the attached example i would like column "A" copied along with the 5 columns before today's date, today's date column and also the 5 columns after today's date. paste the lot into an email and send. (i have highlghted the parts i would like copied and pasted into an email based on today's date (21st july).
View 4 Replies View RelatedThe first line of the code chooses the columns to select; all columns until there is no value. From there I need to have it sort those columns based on row 1. The problem is that the columns chosen are variable. It could be columns I:N (as shown below) or column G:Z or any other combination. (The code below was recorded if that matters at all.)
View 2 Replies View RelatedI have excel sheet it contains 200000 records.I want to delete records from 50000 to 200000.Is there any shortcut to delete data.
View 2 Replies View RelatedIn the code below how do I get the Current ROW number the "For Each Cell in Rng" is processing
I tried "cRow = rng.row" but it's only showing the starting row not the current row of CELL
VB:
Sub Process Range()
Dim startCol As String
Dim startRow As Long
Dim lastRow As Long
Dim lastCol As Long
Dim myCol As String
[Code]...
I am trying to sum a dynamic range of values, clear the range (ie. clear the numbers on the spreadsheet), then place the sum of all the numbers in the last cell in the user selected range. (essentially adding all values selected, placing final sum in the last row of the range, and leaving no trace of performing the routine)
I want the range to be selected during the userform running (using showmodal = false). I can sum the selection and write the value to a cell, but I can NOT write the cell to the last value in the selection.
The below formula is in Col F6, how do i get this formula to adjust to data range without manually filling down, my data changes everyday therefore i need the formula to auto fill according to data range.
=IF(ISBLANK(E6),"",Summary!$I$2-E6)
i have this excel sheet that am using as a monitoring device to track the login and log out patterns of a group of 20 agents. i am able to monitor their current state by using an external link to the local intranet and vlookup.
the status of agent can be either Logged In or Logged Out which populates in E6 to E25.
the cells f6 to f25 again running vlookup shows the last login time of the agent.
what i'm looking for is this:
every time the agent status changes from Logged In to Logged Out or visaversa, the corresponding blank cell in the row needs to populate the the time by value.
I am currently writing a macro that does a variety of things, one of these being that it automatically sums up columns I,J and K. The dilemma I have is that I need to be able to see the sum of the data in a cell at the bottom of the column, after manually filtering it according to what I want to see.
View 4 Replies View RelatedIs there any way i can display the columb number without actually manually counting across.The columbs are A to Z,AA to AZ,BA to BZ,CA to CZ & DA to DN....about 120 columbs.Is there a way to have the columb number diplayed by just placing the mouse cursor on the columb letter (not press though as it will select the whole columb..going to use for VLookup function
View 3 Replies View RelatedI have a sheet where I have a column of cells where I manually enter in a date. the format is d-mmm-yy. Before the end of the year rolled around I was able to enter in say only 12-31 and it would automatically change that to 12-dec-09, now when I do that it spits out 12-dec-10. It's not a big deal to type in the extra -09 to get it right but was wondering if there was a quick/easy fix to get it to know that I don't want a date in the future?
All dates I type in will be in the recent past...never more than 3 months or so old and never later than today's date.
file storage
1. Look at the "Days so far" section
2. Ignore cell C1
3. All the other cells in that row are shaded blue. Look at these.
4. Look at the "Volunteer" row - all the cells in that row are shaded blue. Look at these.
5. Wherever a name - any name - appears in the "Volunteer" row, 1 is added to the previous number in the "Days so far" section and the result is displayed in cell from the "Days so far" row above that Volunteer's name.
6. For example, we begin in C1 with a count of 12 days so far - this was manually entered
7. In cell H5, we see Henry has volunteered 1 day. The total no. of days so far should now be 12+1. Therefore, the number in H1 should be 13.
8. In cell G47, we see Joseph has volunteered 1 day. The total number of days volunteered BEFORE Joseph volunteered is 15 (see cell D43). But now, with Joseph volunteering 1 day, the total no. of days so far should now be 15+1. Therefore, the number in G43 should be 16.
9. what formula l must put in the "Days so far" row (excluding cell C1, which is manually input) to give me the "should be" results predicted in that row? I'm guessing it will be a formula which looks at each row fragment of the "Days so far" row, row by row, right up to the previous cell in that row, all within one formula.
P.S. I just want to leave the "Days so far" row blank, for any columns where there are no volunteers in the "Volunteer" row, so please don't give a formula which inserts zero for days with no volunteer, and then sums the cumulative total.
When I'm moving around in an Excel sheet, I want to have a keyboard shortcut that Autofits only the column I am "in", and only to the width of the cell I'm "in". So I can arrow-key into a cell, hit ctrl-shift-W or whatever, and the column will autofit.
I try Cells.EntireColumn.AutoFit but that's not what I want, it autofits every column. Sometimes this is not appropriate and I want to do it manually for the columns I want auto-fit.
I guess the more general question is how do I write VBA code for the column I am in and not every column or a specific column - not Columns("A:I") or whatever, it's going to be a different column every time depending on what cell I'm editing.
I would like to select a range of cells relative to the current cell and move them to the right two cells. Basically, if I were in cell A1 I want to be able to have a macro select A1 - A8 and move them over by two cells leaving A1 and A2 blank.
Excel 2003
trying to find the value of column A (date field) if it exists on the row that the cursor is presently on. I'm trying to check for the value before performing the next function to make sure the user is on a valid row. Cursor could be on any column A to F. Columns are always 6 but the rows can vary.
View 7 Replies View RelatedI have the following data:
column a: column B:
1
7
9
25
I need a formula to make column B equal to the current month adding the day in column A. so that column B equal the following:
column a: column B:
1 09/1/2009
7 09/7/2009
9 09/9/2009
25 09/25/2009
I have a spreadsheet with 6 hidden columns (B to G). Rows 1 to 14 are frozen and have data that comes from another work book and these rows will be protected. The user will enter data from H14 to U14 and then H15 to U15 for the next row and so on down the sheet. The number of rows of data over a week is variable, a minimum of 21 rows (3 per day) but no maximum.
When the user starts entering new data in the column range H to U I would like to automatically populate the 6 hidden cells on that row with information from particular cells in protected rows 1 to 14. For example B14 would populate with the value from I4, C14 would populate with the value from I5, D14 from I6, E14 from I7 and so on whenever someone enters a value anywhere from H14 through to U14.
I can understand that a worksheet change event would be useful to do this but I guess then it wouldn't want to run every time each cell is populated so I think if it had to activate on a single cell change then the cell in column L would be best. Also, as I understand it, there can be only one block of code per sheet that operates on a worksheet change event, have I got that right? If that is the case then I assume all the code to populate the hidden cells on each row will need to be in this block of code.
let's say cell A1 is colored blue manually, how can i format cell b1 to be colored red? is it possible in conditional formatting?
View 7 Replies View RelatedI have the bellow code, I'd like enter the input on the next available row on column "A" instead than on "A2"
Dim strResponse1 As String
strResponse1 = InputBox("First Name", "Hey you !")
If strResponse1 = "" Then
MsgBox "You have chosen not to participate!", vbInformation, "What happened?"
Exit Sub
End If
Range("A2").Value = strResponse1
I have a spreadsheet that records water consumption in L/s at 5 minute intervals. I've attached an example of the data collected during January 2012.
Is there any formula that will calculate the total amount of water consumed between a user-entered date range? For example:
From: 01/01/12 00:00 (User enters 'from date and time' in an allocated cell)
To: 20/01/12 23:55 (User enters 'to date and time' in an allocated cell)
Consumption: ______ (Formula generated result in Litres)
I want the user of my excel sheet to define a certain area For example range 1:
In cell A1 he enters = 5
In cell B1 he enters 10
Beginning with C1 excel should colour the cells C1, D1,...,H1, because the area has the lenght of 6. Colour could be yellow.
For range 2:
The same in the cells A2, B2, ...
I would like to be able to put manually a formula in a specific cell (like a "requested date" cell) and then have a table that use this date for the headers and calculate +1 year for each columns.
For exemple:
=2012-01-01
=2013-01-01
=2014-01-01
=2015-01-01
requested date:
2012-01-01
Would you know a way to easily do it? when I try to do it manually (+365) it doesn't consider leap years. I thought about a =DATE formula but the requested date have to be manually entered...
I have two tabs BIN QTY and ORDERS. On the ORDERS tab Column A is the order number and is duplicated several times depending how many line items are on the order. The second key is Column H which is the part number which is unique per sales order. I'm trying to use these two values to look up or find the matches on the Bin Qty tab.
-The data in Column A (order tab) gets compared to Column B (BIN QTY tab)
-The second match is the Item no. Data Column H (order tab) to Column C in (BIN QTY tab).
-Once both values match it needs to pull the value from column G (BIN QTY tab) and enter it in column N (ORDERS tab).
My example is highlighted in blue with the result in green. I've tried several different index and match formulas with no luck. my Last was
=IFERROR(INDEX('BIN QTY'!$G$1:$G$7957,MATCH(ORDERS!$A1,'BIN QTY'!$B$1:$B$7957,0),
MATCH($H2,'BIN QTY'!$C$1:$C$7957,0)),0)
MY ORIGINAL SHEET IS MUCH LONGER
I have an array, let’s say B1:D20. I would like to be able to manually select any cell within this array and then run a macro which will place the contents of the selected cell into the cell in column A in the same row as my selection.
View 3 Replies View Related