I am trying to implement a simple macro. Here is my
Function Age(DOB)
Age = Int((Now - DOB) / 365)
End Function
Sub Macro1()
Age (Selection)
ActiveCell.Offset(0, 1).Select
******
End Sub
The function basically calculates the persons age using their date of birth. What I need to know is how to copy the result of the function into the cell next to the selected one. (The code above only selects the adjacent cell).
I have a reasonable grasp of Excel and this has me completely stumped. Three cells hold numeric values (format General though the format seems not to change the result)
C4 = 4082322.25
D4 = 4046689.05
E4 = 35633.2
Another cell contains the simplest of formulae ...
F4 = "=C4-D4-E4"
Clearly the result should be zero.
In fact the result is (to 15 decimals) ...
0.000000000189175
HOWEVER
If I change F4 formula to "=C4-E4-D4" the result is zero.
I created a simple auto numbering function whereby Cell A7 contained =Row()-6, and Cell A8 contained =(A7+1). I then shift, and pasted the contents of cell A8 until cell A600. My aim is to simply copy the increments of 1 - 600 into another column. However when i copy and paste i'm also copying the initial underlying formula ie: =( A?+1), Is there a way to copy the results, not the formula?
View 2 Replies View RelatedI need a macro that can copy a simple formula
I have 2 columns one for the number of items and a column next to it that cumulatively add the totals down the sheet e.g. =E12+G13 etc.
A B
2 2 (=A1)
4 6 (=A1+B2)
1 7
3 10
2 12
If I copy the formula all the way down the page my graph becomes distorted
Is there a macro that will only copy the formula when there is data in the first column and run in the background without needing to be activated
I have a really large excel file (90 Megs) And i want to copy the values and formats of certain sheets to another workbook. Ive tried using a copy/paste macro i had from work but the sheets have alot of groups and info so it kept freezing
View 9 Replies View RelatedIs there a way to copy a formula result as the actual value and not the formula,
I can do copy and paste using "value" option but would like an automated formula or code option if possible.
In the example, D4 copies C4, would like D4 to show "AB" and not the formula "=C4"
I have a spreadsheet containing 2 sheets. In cell A3 of sheet 1 I have a formula that generates either a blank or "1". In sheet 2 I have a macro called Index which I would like to run when the formula in A3 displays "1".
Having done some research I know that the worksheet change event does not work with formula changes so have tried the worksheet calculate function but to no avail. I have done a lot of searching but cannot find what the problem is.
I am testing the code below...
Sub applyCombination()
Dim shtP As Worksheet
Dim j, k As Integer 'j = row; k = col
On Error GoTo errorhandler
Set shtP = Sheets("Sys-Year")........
k = k + 1
Next k
j = j + 2
Next j
Exit Sub
errorhandler:
Exit Sub
End Sub
and, as it is a bit slow to paste a formula in each cell, I was wandering:
Is there any way I can place the resulting value ONLY?
(I don't want to do copy and paste special just values as it takes even more time)
I have created a visual schedule for my team using Excel (2003, SP3 if that matters). Essentially, the user puts in pre determined 1-3 character codes in individual cells, and the macros I have act like a complex Conditional Formatting to keep the formatting neat and consistent throughout the sheet. The actual values are inputted directly into the cells though (this is pertinent to my question), and are things like "A" "M" "$" and "TR"
I have a sheet for every day in a week. Since there are multiple team members on any given day, I have recently made another sheet which pulls a single Team Member's schedule Sunday through Saturday and displays their schedule for the entire week. I have used formulas (specifically VLOOKUP) to do this.
The problem that I am having though, is that the macros that I made to format the days of the week sheets, do not seem to recognize the result of the formulas in the individual Team Member's sheet, and thus do not format them as desired.
My macros are written to evaluate a cell's value via [ Range("example").value ] and will act upon it accordingly with more code. I am assuming that a [ Range("example").value ] would see a cells value as the text of the inputted formula, and not the result of that formula. Is there any way around this? or do I need to avoid the formulas all together and write in code to just copy over what I need?
(I hope this makes sense)
Excel 2003.
I have been struggling for an hour how to hide a number to text macro in a way that only macro result is displayed in a cell.
(I have one 2-3 years old xls, where I have managed with task, but now can't figure out how and how to unhide the macro
Can it be password protected somehow?
Am trying to get the sheetcalculate to be triggered and execute VBA code when a formula changes its result from positive to negative. The code works fine if I trigger it with a command button click. Auto Merged Post Until 24 Hrs Passes;
View 2 Replies View RelatedI'm looking for a VBA/Macro for delete the entire row if a particular column formula result is zero/blank.
View 1 Replies View Relatedhow to run a macro from an IF function, if the function is true macro 1 runs if the function is false macro 2 runs.
View 2 Replies View RelatedI currently have an excel file set up which is refreshing imported data every minute from a server. This data is located on " sheet 1".
This data is the result of another system constantly monitoring (pulling data from) the process at my factory. It consists of temperatures, speeds and other settings.
When a temperature, speed or setting changes then it will be captured by the monitoring system and therefore my excel sheet will load it into the imported data within the next minute.
I would like to be able to capture the change in temperature, for example, if it goes into alarm and automatically send an email reporting this alarm. This alarm is captured in my imported data in a specific column with a 1. If it is out of alarm it is 0. There will not be a report of an item, at any one time, with both a 0 or 1... so there is only one case of any one monitored process.
I have a pivot table set up to filter all the uneeded data out and I currently refresh it manually to show the current status of all alarms. If an alarm occurs on the process, I can refresh my pivot table and it will be indicated with a 1 until it is refreshed again (and the alarm is gone).
So the question:
1. How can I refresh the pivot table automatically every x seconds?
2. How can I automatically send an email on the value change from 0 to 1 in case of an alarm?
When I drag my VLOOKUP formula down a column in Excel 2010, the return value copies the formula result from the original VLOOKUP formula result. For example, if the first VLOOKUP returns a value of 0.5, I expect to see 0.5 or 1 in the cell below that one. However, I get 0.5 which is not the expected result for the cell below.
When, I click the fx on the cells below, the expected return values appear in the formula result. After I click OK, the expected formula results updates and now appears in the cell.
I'm not sure what is causing this issue. My computer was updated recently from an old machine to a new one. I have never experienced this issue before.
I have a macro that imputs data from an external database and puts it into a temporary worksheet. This data has 3 columns (ID, Date, Amount). I am then making another sheet which has X number of tables (one for each ID), with the years being the column headings, and months being the row headings. ie.
| ID X
| +------+------+------+--->
| | 1999 | 2000 | 2001 |
+------+------+------+------+--->
| Jan | $100 | $250 | $300 |
+------+------+------+------+--->
| Feb | $200 | $300 | $200 |
+------+------+------+------+--->
| Mar | $300 | $250 | $100 |
+------+------+------+------+--->
| Sum | $600 | $800 | $600 |
| +------+------+------+--->
| ID Y
| +------+------+------+--->
| | 1999 | 2000 | 2001 |
+------+------+------+------+--->
| Jan | $100 | $250 | $300 |
+------+------+------+------+--->
| Feb | $200 | $300 | $200 |
+------+------+------+------+--->
| Mar | $300 | $250 | $100 |
+------+------+------+------+--->
| Sum | $600 | $800 | $600 |
| +------+------+------+--->
Currently I have a few hidden fields for the DSUM Criteria. I start making the tables. And then filling in table based off of the month and year. Doing so I need 3 criteria:
>= First Day of the Month
<= Last Day of the Month
= ID #
The problem is it takes Excel too long to fill in the 3 criteria fields, calculate the result, copy the result, and place it into the correct place on the table. Is there another way to get this data into the correct tables faster? Instead of using DSUM?
I have a named range on one sheet, and I want to show this as a reference on other sheets. I thought this would be simple, but maybe it's just not the way named ranges are used.
Attached is a sample spreadsheet. First tab shows the table defined; in practice it would be much larger. Second tab shows how I want it to appear. I see that I can do this by copying each cell reference. But what would be nice is to simply say "Put the named range block of cells right here."
I am trying to grab the value of a cell every time it updates and copy that value and store in a new cell.
So I have a cell (A2) and it updates every 60 seconds, I need that value for t=1 (first time) to be copy and pasted into a new cell (B2). Then when t=2 (second time, 60 seconds later) I need the value to be pasted into the same column but a new row, namely cell C2. Then when t=3 (third time), I need it to be copied to cell D2 and so on.
I'm assuming it just a macro that is called every 60 seconds and grabs the value of A2, but then needs to find the next cell in column B that is empty and paste it there.
Using Excel 2003. I have a very basic SUM formula I am using to show how many forms are still in-house. Pretty simple.
Column D is how many came in.
Column F is how many were completed.
Column E is what is left.
To get the daily count I take the previous day's Column E, add to today's Column D and subtract today's Column F.
=(E2+D3-F3)
Puts the results in E3.
This formula is copied on down the worksheet which results in:
=(E3+D4-F4), with the results in E4. And so on down Column E.
The problem is that if there is no data entered in Column D or F, the results from E are carried (copied?) as far down as the forumla. i.e., if today's column E results in 4, then that is displayed for as far down as I have copied the formula. What I'd like to do is set the formula so that if there is no data in columns E or F, then there is nothing displayed in Column E.
Trying to work out array formula.
basically if B2 = list of figures in range(F1:f22) then "G14" otherwise "".
{=if(b2=(F1:F22),"G14","")}
doesn't work.
So I have a list of names, and I need to keep track of whose paperwork I have. There's a column that says Yes/No.
I would like to link everything to another sheet in the file. So for every name that says "No," I would like that name to appear in the next sheet. And when someone updates the first page to put in a "yes," I want it automatically taken off the second sheet.
So.
I have Sheet 1:....
I'm working on simple array formula between two different sheets.
I'm working on Sheet 1 and sheet 2
Right now I want the result on sheet 2 C9
Sheet 2 C6 has MTR
Sheet 1
c6 MTR
c7 MAR
c8 MTR
E6 2
E7 4
E8 6
Now on Sheet 2 C7 has 10
Now the result Sheet C9 has to get 8
=ARRAYFORMULA(SUM(IF(C6="+Sheet1!$C$6:$C$8",Sheet1!$E$6:$E$8,Sheet2!C7)))
I want this formula to work on both excel and Google Drive but now this formula doesn't work on any one.
I would like to insert a formula in a column, the formula is in column D and must be something like "=C1-B1" for row 1, "=C2-B2" for row 2,...etc. I would like to do it without using a loop cycle in VBA...I have attached an example of the data I'm using..
View 3 Replies View RelatedCode:
fma_rng = .Range("C" & fma_top & ":C" & fma_btm)
rows_blnk = Application.CountBlank(fma_rng)
why I would be getting a 'type mismatch' error with the line in red?
I've got a list of marks (col T), and I want to create a new column subtracting 5 from each value.
Obviously, the formula in this new column is
=T1-5
=T2-5
etc...
But I enter that formula and excel doesn't do any calculating, it just displays "T1-5"
It even seems to recognize that it's a formula, highlighting T1 in the formula and the T1 cell itself, but no resulting value...
I've tried changing the format of the cells (in both columns) to Number and back to General again, but without success.
I've also tried copying and pasting (values only) the whole T column to another column, and tried there, also without any success.
I am using this formula in Cell J12: =AVERAGE($G$12:$G$85)
It will always be $G$12 but $G$85 needs to be done so that it goes until the last empty row on the worksheet.
I'm trying to write a basic macro which will increase the value in a cell by 1 when I click on the button I've made. I've searched for hours on the Internet, including this site, and found several websites which give a formula needed but none of them work, I always get an "Object variable not set" error or something about sub-functions which I can't recreate so I can't give the full error message. Sorry.
Based on what I've seen on the Internet, I currently have:
i need a way for a simple input box to come up and request a single generic password prior to runing a macro
View 9 Replies View RelatedUsing this formula for a bank account,
=IF(SUM(C2-A1+B1)=C1, "match", "NO match")
down a column of about 3000 rows, yields only one cell with an unexpected and probably incorrect response in G2 with a "NO Match" - where the math is correct ?
A B C D
63.58 0.00 -54.97 match
64.57 0.00 8.61 NO match
128.21 0.0073.18 match
201.39
Details:
Attachment is included;
temp2.xlsx
in a bank account file, where "C2" is the previous balance, "C1" is the current balance, "A1" is any check written, "B1" is any deposit applied.
I have used Excel for a number of years and have gotten on ok on my own working out simple formulas myself but I have been stuck on this one for a while. Is it possible to enter a value into a cell and have a predefined formula that works with the data that I enter?? I would like to enter a value between 1-10 and the cell then takes this number and multiplies it by a value in another cell. Eg.
I have a cell that displays £10 (let's say D6)
In another cell I enter 8
I would then like the cell to do the formula =8*D6 automatically, as this value can be different each time (1-10)