Calculate Certain Cells On Two Different Worksheets Using VB Macro?
Sep 8, 2009
I have question on how to calculate the certain cells between 2 different worksheets based on the certain calculation parameters and the comparison result (the report) is put into the new worksheet on the same workbooks.
1) Worksheet1 contains: DEPTH, G1, C1, N1, D1, S1, SR1, MR1, DR1
2) Worksheet2 contains: DEPTH, G2, C2, N2, D2, S2, SR2, MR2, DR2
Expected result should be put in Worksheet3: DEPTH, G1, G2, calculation(G1 and G2), CI1, C2, calculation(C1 and C2), so on until the last columns. (find example on attached file "Test_Data.xls)
What I have done is to create a template to be filled in by copy and paste DEPTH, G1, G2 on worksheet1, DEPTH, C1, C2 on worksheet2, so on (attached file "Template_QC.xls"). Then I create a calculation macro for each of those worksheets separately to calculate the difference and color them . The calculation macro that I used is as follows:
View 5 Replies
ADVERTISEMENT
Feb 12, 2014
So I'm trying to create a macro that will simplify dealing with a file we receive at work pretty often. I've enclosed a spreadsheet that shows a very basic example of the files we receive. What I need to happen is this: On the rows that have only the Company # in column A and Total $ in column D (ie. rows 5 and 6), I need the Total $ amount moved up one row and then for the row it was previously in to be deleted (at that point that row should only have the Company $ and no other data in it so it's basically useless). The problem I'm having is that if any rows are added to the file (for instance if an row was added between rows four and five) it would throw off my macro.
View 3 Replies
View Related
Dec 18, 2006
I have been at this for days and i can't quite get it right.
I have multiple worksheets of clients all formatted the same.
I need a Macro/VBA that will take from Each sheet onto a master sheet the Name of the Client(Cell I1 from the sheet)
Under the name I need the text Authorization #: then the data(C3)
Under that I need the text Dates of Service Expiration: then the data (D5)
Then the text 90801 Balance: then the data from (C30)
Then the text 90806 Balance: then the data from (F30)
Then the text 90847 Balance: then the data from (I30)
Then the text 90853 Balance: then the data from (L30)
Then a couple Spaces then the same exact data from the next sheet...till all the work sheets are done.....
View 7 Replies
View Related
Apr 4, 2014
I want the macro to:
1. Create a new worksheet when data is entered into a cell (ie. entered text "ACC2013" into cell A5)
2. Rename that worksheet to correspond to the data in the cell (ie. change worksheet name from sheet2 to "ACC2013")
3. Create a hyperlink between cell A5 and sheet2
I'd like for this macro to loop through a range of cells so I don't have to have a long code. Let's say my data range is from A5:A23. If this requires multiple macros that is fine.
View 1 Replies
View Related
May 22, 2007
I need a Macro that deletes all cells in column D of all worksheets in a book that are equal to the word "Timeout" and to shift the cells to the right of the "Timeout" cells into the deleted cells but leaving all other rows where the word "Timeout" is not present.
So in the case of Rows 2, 4, 6 and 8 containg the word "Timeout" in column D, I would like cells D2, D4, D6 and D8 deleted and the remaining cells shifted to the left but cells D1, D3, D5 and D7 left as they are.
Here is an example line of data:
16:02:30LEXKRunningprocedure:125sth
16:32:30LEXKRunningTimeoutprocedure:X125sth
17:20:54LSXPRunningprocedure:oxford
17:56:48LSXPRunningprocedure:xoxford
18:48:24LEXKRunningprocedure:125sth
19:18:24LEXKRunningTimeoutprocedure:X125sth
View 9 Replies
View Related
Jul 19, 2012
I have a Database and every entry in the database has its own corresponding sheet with the data in a more viewer-friendly format. I want to assign a macro to a button to loop through all the sheets, copy the cells and paste to the database (to update the database).
Also, nothing prevents the user from deleting the sheets or mixing them. So is there a way to take the reference number from the database in column B, search for the corresponding sheet which has the reference number in cell B3 and then update it accordingly.
Code:
Sub Button19_Click()
Dim WS_Count As Integer
Dim I As Integer
[Code]...
View 1 Replies
View Related
Feb 18, 2008
I would like to write a macro that links cells in one workbook (working) to cells in another workbook (summary).
I don't want this to be a straight swap thought, I'd like it to contain the following formula:
=IF(Working!G8="a","a","")
The cells in the working workbook are listed vertically, while the cells in the summary workbook are listed horizontally.
ie. link G8-G18 in working to B3-K3 in summary
link H8-H18 in working to B4-K4 in summary
...etc
View 9 Replies
View Related
Dec 18, 2006
I have been at this for days and i can't quite get it right.
I have multiple worksheets of clients all formatted the same.
I need a Macro/VBA that will take from Each sheet onto a master sheet the Name of the Client(Cell I1 from the sheet)
Under the name I need the text Authorization #: then the data(C3)
Under that I need the text Dates of Service Expiration: then the data (D5)
Then the text 90801 Balance: then the data from (C30)
Then the text 90806 Balance: then the data from (F30)
Then the text 90847 Balance: then the data from (I30)
Then the text 90853 Balance: then the data from (L30)
Then a couple Spaces then the same exact data from the next sheet...till all the work sheets are done.....is it possible?
Dave responded by suggesting the following:
Sub CopyFromAllSheetsButMaster()
Dim wSheet As Worksheet
Dim wsMaster As Worksheet
Set wsMaster = Worksheets("Master")
For Each wSheet In Worksheets
If UCase(wSheet.Name) <> "MASTER" Then
With wSheet
.Range("I1").Copy _
Destination:=wsMaster.Cells(Rows.Count, "A").End(xlUp)(2, 1)
'More Copy Method code here
End With
End If
Next wSheet
End Sub...
View 3 Replies
View Related
Nov 9, 2006
I have the following code attached to a userform. It works perfectly (for what I need) although I know it may not be perfectly written. However it does seem to take for ever to execute considering it's just filling some cells on different worksheets. Have I missed something out or got it tied up doing something that takes a long time. If it's just a fact of life that it takes this time then I can live with it.
Private Sub UserForm_Activate()
Dim wsCB As Worksheet, wsLL As Worksheet, wsBond As Worksheet
Dim rngFound As Range
Set wsCB = Sheets("Current Bonds")
Set wsLL = Sheets("Landlords")
Set wsBond = Sheets("Bond")
TenancyStartTxt = Format(TenancyStartTxt, "dd-mmm-yy")
RnwlDteTx = Format(RnwlDteTx, "dd-mmm-yy")
Application. ScreenUpdating = False
10 MyBond = Application.InputBox("Bond number for renewal?", "Bond Number")
strFindMe = MyBond
With wsCB
Set rngData = .Range("c13:c490").......................
View 5 Replies
View Related
Oct 23, 2013
I have a workbook that contains worksheets. They are listed as follows:
Sheet 1ABCDEFG
In cells A1 - A49 I have text. What I would like to do is to have a macro that I can run that will basically copy and save new workbooks with sheets A - G copied over and have the new workbook saved with the file name that I have denoted in cells A1 - A49 on Sheet 1. Also, the macro would ask me where I want to save the new Workbooks.
For example, if this were Sheet 1, Column A then the cells below would be the saved name of the new workbooks and the new workbooks would have Sheets A - G in themRed
Blue
Purple
Black
White
Yellow
Orange
Green
Gray
Brown
One more piece of information, the file that is being copied and saved is large (~80MB). If there is a macro that would allow me to simply "save as' the workbook and the Saved Workbooks would be named using the data in Sheet 1, that would work as well in case copying, pasting, then saving may take more time
View 1 Replies
View Related
Feb 7, 2010
I have about 5 worksheets, and 2 of the work sheets have about 20k rows by 10 columns... everytime i change one of the other 3 worksheets a calculation takes places.. is there a way to stop these 2 worksheets from being calculated if they havent been touched?
it is taking about 30 seconds to do the calculation even though i have only changed 1 row on another worksheet.
View 3 Replies
View Related
Sep 27, 2012
I want to calculate the average for 6 cells across 6 different worksheets.
For instance in sheet 7 i want to calculate what the average of sheet 1 A1, Sheet 2 A1, Sheet 3 A1, Sheet 4 A1, Sheet 5 A1 and Sheet 6 A1 is. However some the cells contain blanks/zeros which are resulting in an error message. The overall figure still needs to be divided by 6 to get the average.
View 4 Replies
View Related
Feb 15, 2007
To start, have 1 sheet with approx 25 columns of data and i need one column to check 3 others + another sheet to complete its fields.
In column M i need to enter some prices from another worksheet (tag name 'prices') but these prices depend on whats completed in the fields in columns L, T & V.
Column L has fields completed with eg. Houses, Offices, Warehouses
Column T has some fields completed with eg. B1, D, E (these are just zonal refs)
Column V has some fields completed with numbers 1,2,3
Prices worksheet has 4 columns
A = Property (same heading and fields completed as Column L on first worksheet)
B = Standard Price (this has prices in fields that correspond to the properties in A above)
C = Cresta Price (same description as above)
D = Wind Price (same description as above)
I need column M (blank fields) to look at L (heading 'property') then add in Standard Price from B (worksheet prices) then check to see if any fields are completed in Columns T & V and if they are then it needs to addon the prices from either columns C or D from Prices worksheet.
View 7 Replies
View Related
Jul 7, 2014
Let's say I have a workbook with 7 worksheets named, for example, "Instruction", "Begin", "Worksheet 1", "Worksheet 2", "Worksheet 3", "End", and "Data". (in that order)
What I want to do is run a macro to go to whatever worksheet that is in between "Begin" and "End" and copy, for example, cells $C$1:$D$10; then paste as formula into worksheet "Data" starting from cell C1 and then down a list (i.e., copied cells from "Worksheet 1" get pasted as formula into "Data" cells C1:D10; then copied cells from "Worksheet 2" get pasted as formula into "Data" cells C11:D20, and so on and so forth).
But if I were to add more worksheets (e.g., "Recipe" and "ToDo") positioned in between "Begin" and "End" and run the macro again, it'll either 1) re-copy all the formulas from the included worksheets back into "Data" including the formulas from the newly added/placed worksheets or 2) it'll add the formulas from the newly added/placed worksheets and paste into "Data" at the end of the list.
Can create the macro to run based on the position of worksheet, and not based on the name of worksheet, since ultimately there will probably be over 10 worksheets between "Begin" and "End".
View 4 Replies
View Related
Jan 22, 2010
Worksheet 1
I am calculating group averages for the following performers - very good, good, average, low, very low - for a series of factors.
Worksheet 2
Contains the same factors with the values for which Im trying to work out the average. Each factor has a performance rating above it, either very good, good, average, low, very low.
I need a formula which will match the performance rating from worksheet 1 (I3, J3, K3, L3, M3) to worksheet 2 and then calculate the averages of each factors based on those matches.
View 10 Replies
View Related
Jul 2, 2014
I have attached a test workbook excel 2010 (ignore ref# errors, I've cut the workbook down for uploading purposes) What I would like to do is have a 'Button' on my 'information Sheet' which when clicked would clear certain cells. I have searched the forum but can't find a solution, everyone seems to want to delete rows or columns but I just want to clear certain cells. The workbook will have 11 sheets each named 'caravan 1' through to 'caravan 11' The uploaded test workbook only only has 3 sheets.
On 'caravan 1' (which is slightly different to the other 10) I want to clear the content of cells
B4 & B5
C4, C22 & C41
D4 & D5
E4,E5, E22,E23,E41 &E 42
On all other 'Caravan sheets' I want to clear the contents of cells
D4 & D5
E4, E5, E22, E23, E41 & E42
It would be icing on the cake if it could give a warning such as " are you sure you want to clear these cells" but that isn't really necessary. The worksheets will be password protected, but the cells mentioned above will not be. If it proves too difficult to clear all the cells on all the sheets with one click, then perhaps a simpler solution might be to have a button on each sheet instead
View 8 Replies
View Related
Aug 31, 2012
I have a macro which autofilters a range of cells.
I can count the number of visible rows by using the following code
Code:
lcountActive = Range("BC34:BC" & x).SpecialCells(xlCellTypeVisible).Count
What I would like to do is both calculate the Average value of the visible cells and the Sum as well. I have tried to adapt the code I have but my knowledge is just not good enough to do this.
View 1 Replies
View Related
Jan 31, 2014
1.I need to protect certain locked cells from editing and allow certain unlocked cells to be changed on multiple worksheets.
2.When all of the changes are made to the unlocked cells, I need to password protect the entire workbook (except one worksheet) from any changes. (i.e. Prevent even the unlocked cells from being edited)
3.I also need a password to un-protect the workbook and return it to the state described in # 1. above .
View 1 Replies
View Related
Mar 12, 2014
I have sheet where percent change is calculated. All of the data isn't put in at once, so i don't want it to a calculate the percent change until both cells contain a number.
Here is the formula i am using: =IFERROR((D4-B4)/B4,"")
Right now when i fill in the first value it automatically returns a percent change of -100%, i want it to wait until both cells are filled with data.
View 5 Replies
View Related
Nov 13, 2009
I have one excel sheet with columns from A to LZ and number of rows.
Each column have only letter (one of A,B,C,D,E). This if for weekly table:
COLUMN define: Mon-Sun
ROWS define: work type (A B C or D)
For example;
COLUMNS: Su-Mo-Tu-We-Th-Fr-Sa-Su-Mo-......
ROWS: A -A - B - B - B - B - B -C - C - ......till column LZ.
Issue:
I want a formula for any row, from which, I can calculate (for that particular row) number of B's on Mo-Tu right from column A till LZ.
View 18 Replies
View Related
Jun 29, 2014
creating a macro, which can, for each name's values calculate average for non-highlighted cells in the value column.
Currently I have to manually do this for each row -- [=(B12+B14+B15+B16+B17)/5], in column S. hence I am seeking a macro approach, which would make it easier to take into account larger dataset.
I have a attached example file, with sample data, which shows row1, representing the actual columns of the data layout. AVERAGE.xlsx
The desired output I am looking for, is to be able to execute the averages in column S, for each name's value (non- highlighted cells) using a macro, if possible.
View 8 Replies
View Related
Nov 9, 2011
Every time i open my workbook it recalculates and takes ages
is there anything i can press to halt the recalculations so i can go in and change the formulas that are making it take so long.
View 3 Replies
View Related
Apr 17, 2012
How do I modify this formula to calculate visible cells?
=AVERAGE(IF($U$8:$U$720="AM",$H$8:$H$720))
View 5 Replies
View Related
Sep 23, 2013
I am trying to generate a formula that will add the last 17 cells over 25 cells worth of data. If there is nothing entered in one of the cells then the formula should add the next cell.
i.e. column Z needs to add A1:Y1 but only add the last 17 cells that have a number in it so this should add I1:Y1 to give 17 cells worth of data. BUT if for example L1 and P1 are zero, then column Z needs to add G1:Y1, still giving 17 cells worth of data.
View 5 Replies
View Related
Dec 29, 2008
if there is a way (without using a macro) to calculate just a specific column or selected area in a spreadsheet. It is a colossal spreadsheet and takes a few hours to calculate even just one sheet. I know you can go through it hitting F2 then enter a lot but that is getting tedious!
View 9 Replies
View Related
Feb 4, 2010
I have conditional statement {=SUM(IF('name of sheet'!$H$2:$H$226=H8,1,0))}
When ever I try to add a 2nd condition in using the wizard, I get incorrect results.
H8 is the cell that I want the calculation based on. How do I put on there that I want the calculation based on H8 or H9. If the answer is an easy one I apologise in advance. I'm a newbie to excel.
View 9 Replies
View Related
Jun 26, 2007
in A1 I have 57 in B1 I have 75 so if I calculate percentage between those two I should get somewhere around 5% instead I'm getting 76%,
what am I doing wrong, in C1 I have =A1/B1 and then formated under percentage.
View 6 Replies
View Related
Apr 15, 2008
I really didn't want to create this thread, but this is really bugging me and I have had asked for assistance with this. I have tried to bump this in March and again last week, I have given more than 48 hours for a response, but I am seeing that the view counter is increasing so slowly, it was at 84 when I 'bumped' and I am not sure if someone is able to help me as the thread is moving further down the line.
I have not had any trouble with what I have used in over a year, I have had used Vlookup religously for 2 years, and for the last 3 months, I have encountered this problem. Didn't think anything of it the first time it happened. It was explained that there was a space in the cell, which is why the formula did not find it. What's strange is that it only happens on one cell. I haven't determined if it is the exact same cell reference, but I am researching that.
The formatting is the same, in both sheets. Is there some way to vlookup look up the value of the contents as seen?
View 9 Replies
View Related
Jul 21, 2014
excel macro and i would like to ask u if i want to calculate a median of column of data and then the result i need to put in a new column with row by row....i need how to write the coding?
View 3 Replies
View Related
Jul 7, 2009
I used the following statements to (try to) calculate an IRR in my VBA program and received a runtime application error 1004. Tried several variants of the statement but cannot seem to escape the error.
Dim IRRValues(12)
For iIRR = 65 To 74
For iTIGRcubYear = 1 To 11
IRRValues(iTIGRcubYear) = Worksheets("TIGRCub"). Cells(iIRR, 3 + iTIGRcubYear)
Next iTIGRcubYear
aIRR = Application.WorksheetFunction.IRR(IRRValues, 0.2)
Worksheets("TIGRCub").Cells(iIRR, 14 + Scenario) = aIRR
Next iIRR
View 4 Replies
View Related
