I have a range of data that contains can contain either a pass or fail. what I require at the bottom of this range is a formula that says 'if any one of the range = fail then "test fail", else "test pass"'
View 2 Replies
I have a test log with PASS/FAIL. These results are logged in Excel under the column heading PASS/FAIL.
How do I count the number of PASS or FAIL using VBA macro?
So far I have tried:
[Code].....
This one did not work either:
[Code] .......
the error is at the name = Range("PASS/FAIL").Select line.
My thought process: once I had selected a range, I can now freely "look" at each string in a cell in that range. Not correct I guess.
All this is currently done in the active sheet.
I looked around the forum for a answer but none are quite the same. AA2 contains a date. AN2 contains a Pass or Fail based on =IF(AH4<60,"FAIL",IF(AJ4<60,"FAIL",IF(AL4<60,"FAIL","PASS"))).
Now even if those above fields are empty and no date is in AA2 "PASS" still shows up in AN2. I used =$AA$5="" to make AN2 turn white if AA2 had no date in it. I am unable to copy the formatting along the rest of the AN column without it all refering to just AA2. Is there a way to make it copy and correct the formating like it does with formulas? I had planned to due the same thing with the AO column that contains "DUE" if the person has not taken a test in 180 days. =IF(AA2<=(TODAY()-180),"DUE",IF(AA2<=(TODAY()-150),"CLOSE",IF(AN2="FAIL","RETEST","")))
Consider two columns (these columns are part of UNIT TESTing workbook).. result column shows the result of test case executed on respective date
DAte Result
17/1/2008 PASS
18/1/2008 FAIL
18/1/2008 PASS
19/1/2008 FAIL
22/1/2008 PASS
22/1/2008 PASS
How do i calculate using VBA statements :
1. the number of PASSED TEST CASES that are excuted today.
2. Number of all PASSED TEst cases till yesterday.
Per the attached, I have a set of questions which must be answered by true or false. the answer to all is true. I need a pass/fail indicator on the userform to give a user realtime view based on percentage of true answers. threshold can be assumed as 80% where below it is reflected as fail
Capture_Excel Forum Query.JPG
It's a worksheet for testing milk tanks. The issue is as follows:
Column F = Chart Error/This can be either a positive or negative whole number
Column G = Tolerance/This is always a whole positive number
Problem is I want Column H = Status to say "Pass" or "Fail" based on meeting the Column G number. But it's only the number itself (not whether it's Positive or Negative) that matters in determining the P/F status.
Examples:
Column F= -20
Column G= 15
Column H= Fails
or
F= 20
G= 15
H= Fails
The formulas I've been coming up with will show one or the other as Failing but not both + or - numbers if they fail to meet the G Column tolerance number.
I am going to use excel for test cases. I can creat a drop down list with the selections "pass", "fail", "not run". That isn't a problem using the data validation toolbar. However I would like to basically color the cell (test case) with a certain color based on the drop down selection.
for instance:
Pass = Green
Fail = Red
Not run = Orange.
Workbook 1 has 2 spreadsheets. Spreadsheet 1 contains Item and Pass/Fail Columns. under the item column is the serial number of the item tested. the Pass/fail column has the serial number duplicated if it failed tested. what is the formula is to have spreadsheet 2 pick the items from the pass/fail column on spreadsheet 1?
View 4 Replies View RelatedI know its not monday but I'm having a monday like problem. When I try to run this code it gives me "Pastespecial method of range class fail".
View 3 Replies View RelatedI am trying to determine Long Term Gain (LTG,) Long Term Loss (LTL,) Short Term Gain (STG,) Short term Loss (STL,) or No Loss nor Gain (NGL)testing two cells (A1 and B1)and setting a third cell (C1) to the text LTG, LTL, STG, STL, or NGL depending on the results of testing cells A1 and B1.
A1 represent a number of years and B1 represent gains or losses (negative)in dolars.
The way I see the logic is as follows:
If cell A1 or cell B1 are either one of them equal to 0, then it is neither a Gain nor a Loss (NGL.)
If cell A1 is greater than or equal to 1, then it is Long Term; else, if A1 is greater than 0 and less than 1, then it is Short Term.
On the other hand, if cell B1 is greater than 0, then it is a Gain; if B1 is less than 0 (a negative number,) then, it is a Loss.
I need to find (if it is posible in Excel) one formula to test the two cells for posible outcomes:
If A1 = 0 then C1 = NGL
If B1 = 0 then C1 = NGL
If A1 >= 1 and B1 > 0 the C1 = LTG.
If A1 >= 1 and B1 < 0 the C1 = LTL.
If A1 < 1 and B1 > 0 then C1 = STG
If A1 < 1 and B1 < 0 then C1 = STL
I need to test whether an optional Range has been passed to a UDF. IsMissing(RangeName) always returns False regardless of a range being given or not. RangeName exists as an Object that shows a Value of Nothing and Type of Range. I have not found any test that will indicate if the Range was passed in or not.
View 4 Replies View RelatedI'm trying to test whether a variable, a range in this case, has already been defined. If not I want to Set it to a specific cell.
I tried:
If rng Is Nothing Then
Set rng = Range("B14")
Else
rng = rng
End If
I am trying to build a macro that test for the value of each cell of a range (in a column), and if found, then the value of the cell of the same row (another column) will be set to 1. If not, then the macro writes a formula to get some data from BBG (this part is ok). this is what I have done so far but I have an error message, telling me "Not Else with out if" .
Code:
Sub Fx()
With Worksheets("DivRelease")
Dim LastLig As Long
[Code].....
I d like to test for the existence of text in range D5 to D10000 for example
I may have this 49000897890VGN21SP. VGN21SP is text so I d like to spot it
it may also exist on its own VGN21SP can you suggest me with a macro?
like this one
Sub DeleteNAs()
Dim C As Range
For Each C In ActiveCell.CurrentRegion
If C.Text = "#N/A" Then C.ClearContents
Next C
End Sub
Is there a way to check if a named range exists before I run a piece of code? I created a new file that has need for all the old file's ranges plus a couple more, and I want to use the same macro for both. So on the first file I just want to say, if these other named ranges are there, go ahead and do his other thing.
View 6 Replies View RelatedI need to do stats for each month within a set of data. Ie take the min for all data in the month of Jan only and repeat for all other months etc. Is there some way I can set up a Min function to only consider the data belonging to jan for example and have it change and only consider Feb, then march.. etc Seems like it would be easy to just do this manually but there is a large timeframe over which data was collected so would take forever.
Have attached sample of data. rainfall.xlsx
I want to randomize a range of values stored in sheet 1 and insert them in another cell.
I am having values for sales consisting of product names and its price. there are 10 product names and its corresponding prices and is stored in cells E1:F10. I want to generate test data containing product names and its corresponding price. The generated data needs to be saved in the cells A1:B50
the product names needs to be randomized. This needs to be done in vba. Below is the screenshot of the final result that is needed. As you can see, I have randomized my 10 products and its prices into the test data column. This was done using vlookup formula and I need the same to be done in vba.
How do you write an If then statement using a range of cells? I want to construct a logic test using the range of cells A7:A19. I want the logic test to see if the any of the values =1. If the test is true then I want it to display the value in B7:19 (which ever cell corrulates to the cell in column A that has the value of 1) and display the number in the B column.
View 6 Replies View RelatedOn Sheet1, Row 2 I have my columns named January-December, which correspond to the names of the other 12 sheets in my workbook. I want to test and sum the same range of cells in each sheet by simply coping and pasting the formula. I am using INDIRECT and SUMPRODUCT, but is there a better way? It seems to have caused the sheet to run slowly. Perhaps it's just the amount of data. Here is an example of the formulas I am using:
=SUMPRODUCT((INDIRECT("'"&B2&"'!$B$3:$B$200")=$A$2)*(INDIRECT("'"&B2&"'!$C$3:$C$200")="Yes")*(INDIRE CT("'"&B2&"'!$E$3:$E$200")))
This gives me the intended result. B2 in this case is January. C2 is February and so forth.
Using Excel 2013,
It trying to see if the Month() of a date is in a Array / Range
I tried =IF(MONTH(E8)={2,6,9,11},"Yes","No") where E8 = 9/30/12
How can I test in my VBA wether a named range (with a scope of sheet) exists on the active sheet?
Code:
If "EmployeeEmail" exists on the active sheet Then,
xxxx
Else
yyyy
End If
Using Excel 2010.
According to what I've read so far in this forum, this will be cakewalk for you guys to find out of (or at least so I hope.. :-) I don't know much about Excel, and what I do know is just logic sense, and a bit of common math.
My problem is in a sheet I use for my personal economy. (I'm a neatfreak, and a nerd...) The logic of my sheet is that I seperate the different type of monthly expenses, and sum them individually. The way I do this is that the A Column identyfies an expense (where 1 = rent, 2 = food, 3 = gas etc) while the C column is the expense itself, Example:
A B C
On account 500
1 rent -200
2 food -20
3 gas -10
2 food -10
Sum expenses -240
On account 260
I then use this formula to discern them:
{=ABS(SUM(IF(A$4:A$30=X;C$4:C$30;0)))}............
If I run this in the editor sometimes it works and sometimes it does not.
If I run this in editor with F8, stepping through the code, it does work.
I can only guess that enough time is passing during the F8 presses that the page can load. But is that not the purpose of adding the wait code?
[Code] ....
My nested loop works as intended without If statements, ie it counts non blank cells in a series of 17 ranges (this loop is called the CountRangesOnEachRow loop) AND then drops down a row and repeats (this loop is called the RowNo loop). The problem is that once my first If statement occurs the RowNo loop fails and it stops at the first row.
The first If statement is designed to do the following:
1. Tests to see if A4 has data. If yes, copy cell contents (pupil's name) to Sheet(2).Range("StudentName")
2. If empty skip the countranges loop and move down a row to A5.
3. Cells A4:A35 need to be tested for data in this manner.
I've included a sample workbook & code with the IF statement comment blocked so you can see what it should do AFTER it tests the A column for data. The macro should end after row 6 as A6:A35 are all blank.
I have one workbook that im running a userform. From this form i open another form that is contained in another previous opened workbook. On this second form i can update cell values in the second workbook. During this updating macro i am saving the workbook. using thisworkbook.save. Though For some reason when it hits this line. It must be doing a Save As and putting the workbook in mydocuments. There is nowhere in the code that references mydocuments.
Though if i open the workbook on its own (withough calling from another workbook) and load the userform the code runs fine and saves the workbook in its correct place.
When I'm manually formatting a spreadsheet I'll almost always freeze the header row for ease of navigation and to keep the headers in view. However when I attempt to do this in my code it seems no matter how careful I am it inevitably freezes the page in some peculiar location which bears no relation to the instructions I give it. Even knowing this annoying fact I'll take the extra step of turning off freeze first to clear any existing situation before setting my prefered freeze. ... and it still blows up in my face
Sheets("RTN").Select
ActiveWindow.FreezePanes = False
Range("F4").Select
ActiveWindow.FreezePanes = True
Is there any fool proof way to freeze rows?
If the addition of two cells (a1 and b1) do not add up to more than 10 then I would like cell c1 to display the word FAIL
I've tried the following
If (a1 + b1 ) < 11 then c1 = "FAIL"
But it doesn't like it
I have a source data tab laid out like this:
QC Date.......Policy#......Associate.......Pass/Fail......1stError.....2ndError.....3rdError
QC'ers can enter up to 3 different error types committed on a single failed policy, thus the reason for 3 error fields, even though all 3 fields offer the same value list selections (ie ErrorTypeA, ErrorTypeB, ErrorTypeC......). A policy, whether it has one error or three errors assigned, should only count as a single fail against the associate.
My problem comes into play when attempting to pivot the data. I would ideally like to see the pivot table in this format:
(filter)Associate
(filter)QC Date
...
ErrorTypeA.......Count
ErrorTypeB.......Count
ErrorTypeC.......Count
ErrorTypeD.......Count
Where the "count" is the total number of occurances of that error type across all three error fields - 1stError, 2ndError, 3rdError.
But since 1st/2nd/3rd error fields are different columns, I am unable to do a single count.
My pivot ends up looking something like this:
1stError.....ErrorTypeA.......Count
1stError.....ErrorTypeB.......Count
1stError.....ErrorTypeC.......Count
1stError.....ErrorTypeD.......Count
Then repeated for the 2ndError and 3rdError fields.
I am using the below code to locate the last column in various worksheets before pasting information. The problem is the column selected is either: Correct, Is a blank column with many blank columns in between it and the last visible text. The code also highlights all the cells containing text in some sheets. The results are the same for each sheet the code is run in i.e it is not varying.
View 4 Replies View RelatedI am trying to test for the next available row in a sheet using a range as follows:
NextRow = Range(MyRange).End(xlDown).Row + 1
If there are no rows being populated, the formula throws an error and I assign NextRow to 0 indicating no entries present
However, and this is my question, if there is one row populated the value for next row seems to either give 0 or 65637.
After this, the code runs OK.
