I need to step out of a macro based on a user respone, and I don't know how to end this macro if the user needs to correct a cell entry.
If they respond "no", I want to sub to end and select the cell where the correction needs to be made.
I have this Formula in a excel sheet =IF(AND(COUNT(F12;G12)=2;G12<F12);G12-F12;IF(AND(COUNT(F12;G12)=2;G12>F12);ABS(F12-G12);""))
My problem is that when the result is 0 it just leaves a blank cell without a 0.
Can i correct this so that when the result is 0 it will actually show a 0 in the cell? I can´t put a 0 in the end of the formula because then it shows zeroes in all the cells without information aswell.
I made a command button on the worksheet from the Tool box. This is not made on a form. When I click on the button I would like to get focus set to a certain cell and made active on the same page. Like cell H2 for example.
View 2 Replies View RelatedI've tried to do conditional formatting for each of the "Discipline" values in this spreadsheet but it is restricted to only 3. Is there a way around this? I'm trying to setup a color code for the "Discipline" values for example RN=Red, LPN=Blue, Social Worker=Orange, Chaplain=Purple, STNA=Yellow, Volunteer=Pink & Other=Green. So when the "Discipline" cell/cells on each of the patient sheets (ie. "Doe, Jane", "Doe, John", etc.) is equal to say RN the cell would then turn Red either on the spreadsheet or only when sheet is printed.
View 5 Replies View RelatedI am using an event macro to trigger a set of calculations. In the spreadsheet, users have a choice of 3 input cells to work out an answer; these 3 cells work out the same cost from different angles depending on the variables available to the user.
Users only ever need to fill in one of the three cells to work out the answer as the event macro I designed, should (upon input by the user), work out the remaining two input cells using logical arguments.
The macro and calculation work fine, except in the event of users amending a value in an input cell previously updated; in other words, if all 3 cells contain a value and 1 of them is being changed by a user.
The issue: I need the event macro to recognise the location of the last change made by the user in order to determine which 2 of the 3 cells need to recalculate. However, users will invariably press enter or tab etc... upon making changes and this has caused me issues so far.
What I have tried already:
1) running another event macro "on selection change" to record elsewhere in the model all cell selections as and when the user interacts with the model
EFFECT: it made the "on change" event macro very slow and clunky; so I need to avoid
2) declaring a range as set r = activecell.address
EFFECT: this did not work as the active cell's address would in fact be the address on the last cell; e.g. the one selected further to pressing enter (often the one directly below the cell recently changed)
A solution to add to the existing "on change" event macro that identifies the last edited cell
I am using Excel 2003, my problem is that I have cells D4 & E4 with red text, when an entry is made in B4 (say $640, the amount can vary) I want the text in D4 & E4 to turn black. I have attached a section of the document in question.
View 5 Replies View RelatedI am preparing a daily report available in sheet1 with the actual input in sheet2 generated through a tool.
The report contain three columns i.e. total cases, Total correction done and Not corrected. From the total cases on a specific date, I need to calculate how many correction have been done and how many are pending.
E.g. 01 January 2013=5 cases are uploaded in the tool.
on 02 January 2013, it was observed that 3 cases were corrected and 2 are pending But 9 cases cases are uploaded on the same day in the tool .
So Total correction done (C3)=3
Not corrected(D3)= 11
I have different dates in column A, let's say:
10/26/12
11/1/12
11/15/12
2/12/13
I want to say that if the date is <= 12/31/12, put "LEASED IN 12", othwerwise put "LEASED IN 13".
My formula:
=IF($A1<="12/31/12",'LEASED IN 12","LEASED IN 13")
I get only 'LEASED IN 12".
It show error, how to correct it .
[Code] ......
=COUNTIFS($K$54:$K$84,">25
View 9 Replies View RelatedSub startrd3_click()
Range("B5:AY64").Select
Selection.Sort Key1:=Range("B5"), Order1:=xlDescending, Key2:=Range("C5") _
, Order2:=xlDescending, Key3:=Range("E5"), Order3:=xlDescending, Header _
:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom _
, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
' ERROR CHECKING HAPPENS HERE TO MAKE SURE PEOPLE HAVE NOT PLAYED EACH OTHER BEFORE
Columns("J:W").Select
Selection.EntireColumn.Hidden = True
Range("I4").Select
End Sub
If any of you gamers out there are familiar with the Swiss Pairing System, the goal of this sheet is to perform the pairing functions based on results. The goal of swiss pairing is to partner folks up based on win loss records, however, the players can never play each othe more than once. What i am wondering is how to create the vb code required to make sure the pairings are unique while keeping the rules of swiss pairing together.
Here's how it works:.............
I am making a form with drop down boxes and auto fill to make things easier. I have one Box for the Company selection another box for the occupation selection and I need a formula to find a $ value based on the selections made in these two cells. If I could establish the actual cell reference of the selected data in the second drop down list. The data will be much larger than this example and will live on a separate sheet.
View 2 Replies View RelatedIn A1 have two words, say "first" and "second" as two lines entered by pressing Alt+Enter after typing the first word. The requirement is remove this formating OR to get these two words separately in single cells. ie B1=First & C1=second.
View 3 Replies View RelatedI am trying to enter the last name "Teh" in a cell. How do I turn off auto spell correction that turns "Teh" into "The"?
View 3 Replies View RelatedI need a hand with a worksheet that we use.
We do a penetration test on soil, write down the values from the machine, then enter into excel and it plots a line graph.
Depending on the values, sometimes a correction is required. This is manually done at the moment.
I would like to have excel do it for us.
I have attached an example of a manual correction I have done as well as the excel calculation worksheet.
There are 2 results given (one at 2.5mm penetration & one at 5.0mm penetration), we calculate both, then use the highest result for the report.
the x value is a constant and the y is a variable.
Attached Files :
Copy of Master WA CBR Worksheet Soaked.xls‎
DOC230714-002.pdf‎
I have the following VBA code and try to sum the range from B2 to CY2 for DA2; then B3 to CY3 for DA3, etc. Therefore I use the for loop. It results in "Application defined or Object defined error".
View 4 Replies View RelatedI'm trying to calculate the sum of rank vlaues in an array formula (required for a Mann-Whitney U-test calc). For example, I have the results of a survey quesiton (1-5 rating) with particpant groups of Sales, Marketing & Other. I want to sum the Ranks of the data points that come from Sales or Marketing (but not Other). The added complexity is in the need to add in the Rank correction value to account for ties.
The conditional arrays are the tricky part. I'm very close, but the array formula is still including the Other values. If I delete those data points the formula works great. For those with strong stomachs, I've copied my latest formula below.
$C$% = "Sales" and $F$5 = "Marketing"...
={SUM(IF('Survey Data'!$D$3:$D$30=$C$5,RANK('Survey Data'!$W$3:$W$30,IF(OR('Survey Data'!$D$3:$D$30=$C$5,'Survey Data'!$D$3:$D$30=$F$5),'Survey Data'!$W$3:$W$30))+(($M115+$N115)+1-RANK('Survey Data'!$W$3:$W$30,IF(OR('Survey Data'!$D$3:$D$30=$C$5,'Survey Data'!$D$3:$D$30=$F$5),'Survey Data'!$W$3:$W$30),0)-RANK('Survey Data'!$W$3:$W$30,IF(OR('Survey Data'!$D$3:$D$30=$C$5,'Survey Data'!$D$3:$D$30=$F$5),'Survey Data'!$W$3:$W$30),1))/2))}
I want to put a range select statement to select a cell and count down 10 cells and copy.
View 9 Replies View RelatedI have column A with various values in cells.
For instance, DG, GS, HG etc
I wanna do if a cell in column A is equal to DG then select the entire row that contains the cell. then call other sub.
I have a master spreadsheet being updated by several people, how can I/we, get a notification set up for when ever someone updates the master spreadsheet.....
View 1 Replies View RelatedI have made some changes to a workbook and now every time I want to close it I'm asked if I want to save the changes, even when I have made no changes. If I click 'Yes' to save changes the problem still occurs the next time it's opened & closed.
I have checked external connections and there are none. I have done a 'find' to check that there are no formulas looking at another sheet, which I cannot find.
This document has to have a live calculation function - I have read some suggestions around turning off auto calculations but this isn't a possibility and I'm not sure that this is where the problem lies.
The previous version does not have this problem, so it is something I have done between v2.2 and v2.3. I have attached both for reference. Note that they were originally xltm's not xlsx's - I could not upload macro-enabled templates, but the same problem occurs in v2.3 in this format.
I have an excel spreadsheet that when opened and closed prompts to save changes, even when no changes have been made.
The spreadsheet is a copy of another spreadsheet which has then been modified. The original spreadsheet doesn't have this quirk.
There are no 'links' in the spreadsheets. Which was my first thought.
Is it possible to write a macro that will run if a user enters data into any cell in the worksheet that has a formula in it.
View 9 Replies View RelatedI am using Excel 2003, with Windows XP. I am monkeying around with a form to allow my boss to randomly assign employees to a daily task. There is a list of 15 names, but I'm just using the numbers 1 through 15 until I get the hang of this formula.
I am using MRAND, and the formula is working perfectly when I specify a set # of values to deliver. I wanted to see if I could allow him to enter the # of employees he wants to task in Cell A1 so that the number of random employees that are assigned varies with his selection in A1.
In other words, one day he could specify he wants 3 randomly assigned names to work on the task and the next day he could randomly assign 6 names/people, etc...
I thought I could just plug $A$1 in "maximum" in the formula below:
{=MRAND(maximum,1,15)}
But that's giving me a #NUM! error.
I am trying to analyse, in a set of data that how many followup made by a salesman between a particular set of dates. I've attached a sample. In this example I differentiate between people if the name and age are same. I know that it is possible with formula 'countifs' but how?
View 3 Replies View RelatedI've tried for a while on my own but no luck. I'm looking to delete cells D21 and 22 when any changes are made to the sheet.
I've tried using Private Sub Worksheet_Change(ByVal Target As Range) but no luck.
The workbook I have has a tab for Master List of Transactions - there is data in columns A through J. In column E, when a year is entered, the data in columns A through D is copied to a tab for the specific year entered in column E. When the data from columns A through D is copied to the specific year's tab, it does not change anymore. If the information is changed at all on the Master tab, the macro does not currently work to automatically update the information on the specific year's tab but I would like it to. Is this possible?
Here is the macro I currently have:
Private Sub Worksheet_Change(ByVal Target As Range)
' Code goes in the Worksheet specific module
Application.ScreenUpdating = False
Dim rng As Range
Dim row_copy As Integer
[Code] ......
How to automatically update the year tabs for changes on the Master List of Transactions tab.
I have created the following in an effort to automatically goal seek whenever changes are made to the cell C2.
Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Row = 3 And Target.Column = 2 Then
Range("D19").GoalSeek Goal:=Range("C2").Value, _
ChangingCell:=Range("D3")
End If
End Sub
As far as I know, the Macro is running all of the time? When I use goal seek manually it works fine. Assuming the above is running, when I change the value in C2 nothing happens. Does the fact that I have a three sheet workbook make any difference? I am sort of lost. By changing the value in C2 I want Excel to change the value in D19 to match C2 by adjusting D3.
Application.EnableEvents = False
Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Row = 3 And Target.Column = 2 Then
Range("D19").GoalSeek Goal:=Range("C2").Value, _
ChangingCell:=Range("D3")
End If
End Sub
Application.EnableEvents = True
Doesn't seem to work either. However, I don't really know if other settings in the workbook are correct.
Is there a way of eliminating un-used rows & columns to bring your file size down.
I already know about eliminating unnecessary pages, and I've cleaned up defunct modules, etc., and anything else that's just clutter.
The Row & Column thing would be a big help.
If nothing is typed in and the OK button is pressed, it changes the entire sheet to values. I'd like the macro to stop if nothing is selected.
Dim sCol As String
sCol = InputBox("Select Column")
Set UserRange = Range(sCol & "9:" & sCol & "35")
UserRange.Value = UserRange.Value