I have found the following piece of code which does almost what I want it to:
sub my_calc
range("A1:A25").calculate
end sub
However, the Range I want Excel to recalculate is, from the Active Cell, 20 Rows down and 81 Colums across.
How would this Range be defined in VBA, please?
Here is what i am trying to achieve. If the date 2/20/2010 is located at F53 & the cell next to it at H53 is populated with a number between 1 & 16, then i want the cell at J11 (42 rows further up) to auto populate with the number 1. When this occurs the cells beneath this, from J12 to J52 should also auto populate with the with consecutive numbers from 2 to 42. Would also like to see the cells with numbers 1 to 28, automatically format to orange & the cells containing numbers 29 to 42 automatically format to yellow. I plan to have this condition repeat several times later in the year, at dates that are to be decided. When these dates are decided i want to be able to enter a number from 1 to 16 & next to the date & all of the above automatically occurs.
View 14 Replies View RelatedI have a worksheet which will take a long time to recalculate. So, I want to recalculate only on selected range.
View 2 Replies View RelatedI have two sheets. Watchbill and Roster. The Roster contains a list of names that are randomly generated to the watchbill sheet into more than 60 seperate cells. My problem is the names are not being distributed fairly. If I had 20 names and 20 cells, it only uses say 12 of them and repeats 8 names, thus leaving 8 persons out of the generation completely. I did not know how to fix this so I used a system to identify when a name was repeating in the series of cells by displaying Repeat in the cell beneath it. I then had the names that were not placed on the watchbill (the other 8 not used) highlight themselves in blue on the roster sheet so I could manually insert them on a third sheet into a cell set to override the repeating name on the watchbill. This was my back door system to solve my repeating names problem. However this takes forever. I want some vba that will identify the cell with the "repeat" underneath it and automatically recalculate the cell until it doesn't repeat. I only want the cell recalculated, not the sheet. This code would have to do this for about 70 cells making sure no names repeat at all, unless there isn't enough personnel to fill the positions, then it would allow the names to repeat. Can that be done and what would the code be?
View 14 Replies View RelatedI'm trying to build a price index in which when a user changes the value in any one cells, the other cells on that row would change dynamically. In other words, if a user enters 25% in the "discount %" column, the "net price" column would reflect this discount ( attached is a sample sheet). I thought I had this covered but ran in to some problems I can't figure out on my own.
The worksheet_change function only inserts a formula in to a cell once. After a value has been added to that cell manually, the function doesn't insert the formula in it again. Also, there are empty cells inside the price index and I don't know how to take them into consideration in my code. And for last, if, and only if a discount % is entered a phrase "Z106" should appear in the last column. But if any of the net prices are manually entered, a phrase "ZSOP" should appear in the last column.
I have an interesting question for everybody here. Let me see how to best explain it.
I start in the code making sure column Action Plan completion has a value in it. Copying in the issue modified column if neccesary.
'Back fill last modified
'I need to start with the first data row (3) and the action plan completion column
Let intcurrentrow = 3
Let intcurrentcol = intactionplancompletion
Do Until intcurrentrow > intmaxrow
If Cells(intcurrentrow, intcurrentcol).Value = "" Then
Let Cells(intcurrentrow, intcurrentcol).Value = Cells(intcurrentrow, intissuemodified).Value
'Need to ensure that everything is formatted as a date
Let Cells(intcurrentrow, intcurrentcol).NumberFormat = "m/d/yy h:mm AM/PM"
End If
Let intcurrentrow = intcurrentrow + 1
Loop
Later, I place this formula in a given cell.
=If(I9="Open-Remediated",M9-K9, If(I9="Closed-Remediated and Validated",M9-K9,"" ))
The info I copied above is placed into the column K.
When I run the macro, the cell formula outputs "#value".
However, if I go double-click then enter the cell calculates just fine.
I've tried excels overall recalculate function, but that doesn't change this.
Does anybody know how to simulate a double-click on a cell?
I am trying to build a staff roster. The staff rotate over a 4 week cycle. the name of the staff member, and their shift needs to be looked up from the key then matched with the particular week. the name and shift then need to populate specific cells.
I have attached the worksheet so you can see what i am trying to achieve.
I am using Excel 2010 and basically i am trying to fill a range of cell with a green color if any value was enter in a specific cells. Example: I would like to fill range: A10:c13 with a green color (regardless of the cells content in this range) if a value was entered in cell C10 or C11 or C12 or C13.
I've tried conditional formatting but unfortunately I'll have to apply formatting for every cell and for a range of over hundred cells is not efficient.
I'm working on a project for my company. We make plastic tanks and for quality control we want to start recording the thickness of the tanks in different areas/zones of each tank.
Attached to this message is an Excel sheet that I've been working on. From "Sheet 1", it records inputted thicknesses into WorkSheet "1098". On the top of "1098", it shows all of the recordings, and just below that are the "10 Most Recent Entries".
Right below the "10 Most Recent Entries", there are formulas to calculate the Min and Max Values. Whenever a new entry is recorded, the selected cells for the Min and Max formulas change. Is there a way to force the cells to always stay the same?
Not sure if this is better suited for a macro or done via conditional formatting.
Trying to empty a standard template we use and of a certain range I need to empty all cells not containing N/A (typed not error).
I'm looking to easily drag the sum of certain cells in a different column BUT keeping a specific range, it's hard to explain so i'll show an example...
A1
A2
A3
A4
A5
A6
A7
A8
B1=SUM(A1:A4)
B2=SUM(A4:A7)
B3=SUM(A8:A11)
And so on...
Is there any way I can do this by dragging down the cell formula from B1 and it remembering the range of 4, so I don't have to manually select each range...?
I need a code to delete all duplicates (all cells) in the range A1: F100?
Exemple:
Before
31
81
43
[Code]....
I wanted to ask how to delete a specific range of data in a cell range using VBA
Here is a snipit of code I currently have:
Sname1 = Sheets("Numbers").Range("FX3").Value 'FX3 = cell F starting column letter
Range(Sname1 & Range("FR7").Value).Select 'FR7 = row number
If I added the following:
Sname2 = Sheets("Numbers").Range("FX4").Value 'FX4 = cell n ending column letter
Changed the code to the following: obviously this code is incorrect below:
Range(Sname1 & Range("FR7").Value) : Range(Sname2 & Range("FR7").Value).Select
I have a column of values of peoples initials and I want to cound how many times one persons initials occur in those cells. I can do this if the cells just contain one person i.e. "CH" or "DH" but if the cell has two or more peoples initials i.e. "CD DH" I can't do it as it only matches the exact search criteria.
How can I total the numebr of times a required set of initials appears in a specific column of cells? Not bothered is it requires a macro or a formulae, just can't seem to figure this one out.
I want to count the number of groups of 3 adjacent cells in a horizontal range (C5:EV5) in which any cell in the group of 3 has a value in it (as opposed to all the cells being blank or containing "0"). For example, if any or all of the cells in C5:E5 have a value in it, it would count as 1, and if any of the cells in the group F5:H5 have a value, it would also count as 1, and so on. Is there a way to use COUNTIF for this?
View 5 Replies View RelatedHow to check if a range of cells matches specific text.
Example:
A1 - dog
B1 - cat
C1 - mouse
I want the formula to give me "yes" if dog is present.
=IF(A1="dog", "yes", "no") is for one cell. However if I do it this way:
=IF(A1:C1="dog", "yes", "no") I get an error.
How do you select specific coloured cells from a range of data in one go, without having to scroll through the worksheet and pick them out individually?
I need a VBA to delete rows with blank cells within columns F - AZ
Columns A - E contain headers but also need to be deleted if cells in columns F -AZ are blank.
I wanted to count the number of instances that Matt's been late. If there are consecutive timestamps (ex. 600-620, 620-640) that he's been late, I wanted Excel to display 1 and then I'll just sum it up. Or if Excel can do this directly, add all the instances because what I actually need is the total per person.
In this example, I would need a result of 4 instances.
tell if the blank cells will affect the formula or if I still need to do something about them.
All, can I protect my worksheet while at the same time allow users to unhide a specific range of cells?
I'm hiding cell range FA:FK, but based on certain conditions I want to allow the user to unhide the cells but I do not want the user to be able to unhide any other hidden ranges. if there is a way to do this without using VBA. If it cannot be done with VBA
I am trying to create a formula that shows a percentage of cells within a range that contain a particular number. This percentage is based on another cell, which is the total number of all audits. Each audit has rating columns, with a numeric value indicating it's rating. I need to count those cells within the range that equal the numeric value assigned to the rating, and then divide that total by the total number of audits to obtain a percentage of calls with each particular rating in each category.
Here is an example of what I'm trying to accomplish:
A1 = 2 (Total audits)
B1 = 07/31/2009 (Date of first audit)
C1 = 3 (Rating of first category)
D1 = 1 (Rating of second category)
E1 = 2 (Rating of third category)
F1 = 08/03/2009 (Date of second audit)
G1 = 0 (Rating of first category)
H1 = 2 (Rating of second category)
I1 = 2 (Rating of third category)
The percentage of audits within the first category that have a rating of 3 would be 50%.
The percentage of audits within the second category that have a rating of 3 would be 0%.
The percentage of audits within the third category that have a rating of 2 would be 100%.
I need to populated a specific range of cells based on a value of any kind in a header cell. Attached is a sample of the end results. Please remember each header cells needs to determine what happens the range of cells below the header cells.
View 3 Replies View RelatedEssentially I need to copy the first 8 cells in a row in one sheet (for example: A3:I3) when the word "Actuals" is entered into A3 from a drop down list. Then the copied data needs to be pasted to a another existing worksheet in the same workbook in the next available row. The data includes mostly values, but there is a formula in column H that creates a hyperlink out of the content in column G, friendly name in column I.
I am not stuck on the idea of having "Actuals" entered in column A as the trigger or change event and there will be times when a new copy/paste of the same data will need to be done more than once at a later date.
For further information, column B contains a serial number/productID number.
Is there a way to prompt users to enter information in a specific range of cells before they save?
View 1 Replies View RelatedLooking to have a macro call a subroutine every time it finds a cell meeting specific criteria.
Code in plain english would look like this:
For EACH cell in range A1:BZ500 meeting the following criteria:
Cell value is a date
AND
Cell's date is at least a week or more in the future
AND
Cell background (Fill) = RGB color code: (R:191 G:191 B:191)
DO the following:
Call repeatingsub
I have a protected template and unprotected source worksheets - - - what I would like to happen is for the macro to start and if the source worksheet cell B3 equals "Report Total" then stop - otherwise copy template worksheet then copy 6 specific cells from the source to paste values to specific cells on the newly created worksheet (B_ to C7, D_ to I7, E_ to C9, F_ to K9, A_ to C11, M_ to K11 and then K13=F13-30)
After that then start all over again unless the next row’s cell (B4, B5, B6, . . .) is "Report Total" then stop - - - the row count could be from one to a couple hundred.
Here is what I have so far but I know that with each copy the name will change and as it goes down the source file each row will change and I also need help with that.
Sheets("ee template").Copy After:=Sheets(3)
ActiveSheet.Unprotect
Selection.ClearContents
Range("I7").Select
Selection.ClearContents
Range("C7").Select
Sheets("source").Select
ActiveCell.Offset(0, -11).Range("A1").Select
I have a countdown formula in a cell and I would like to set up code to recalculate the countdown timer.
View 14 Replies View RelatedI built the below udf to run a calculation on a range based off of dates. But it won't recalculate when the cell with a date changes.
I had to do the "application.volatile" at the top and press F9 for it to work.
Is there a way to recalculate without this or trick excel to do so?
Function TestRange(r As Range, q As Range, rcol As Range)
'Selects range based off two specified date ranges
'formula must reside in the same column as the data column (at the top)
Dim rlast As Range
Dim firstvalcell As Range
Dim Lastvalcell As Range
[Code] ......
I have a macro that can individually turn on or off the EnableCalculation property of each worksheet in a workbook (that is, set the property to True or False). It worked very nicely for a while, and made some of my more formula-laden workbooks much more tractable. But after a while, when I would turn the EnableCalculation back to True for any worksheet, the formulas on that sheet were still frozen (would not recalculate at all), and could only be unfrozen by going to each cell and doing [F2] [Enter]. Just to rule out the obvious, Automatic calculation is turned on, and I have made sure that the macro is successfully returning the EnableCalculation property back to True.
It seems that if I physically go into the VB Editor and manually (through the GUI, as opposed to programmatically) switch the EnableCalculation property back to False, then back to True again, then it will revive the formulas. But this is silly, and I was hoping there was a better solution... maybe someone has encountered this before? If it's just another Microsoft bug, I don't know what's the trigger is, but I imagine it's when you save/close the workbook with some of the sheets' EnableCalculation properties still set to False. I know the property is not stored/saved with the workbook, i.e. all sheets will reset EnableCalculation to True when the workbook is re-opened, but maybe it's still causing a glitch.
My excel file consists of 15 sheets, thereof 4 that are purely graphs, the others have tables with formulas. When opening the file or changing values, the entire sheet is being recalculated every time which takes a few seconds. Is this normal or can one tell to recalculate at another stage?
The activities sheet has a date macro (double click in date cell).
The workbook has a macro for the footer information.