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 RepliesI am trying to calculate the number of forecasted hours worked per week for each contractor based on their allocation to the project and also based on a 37.5 hour standard week.
I am using a formula which was previously provided to me for another problem but theoretically should share the same logic. The formula works only when a contractor is forecasted to work for a full week. If there is a contract which finishes mid week the formula returns a weird value. Also another problem is that if a contract is starting on the 12th of January, it still forecasts a full week for the week commencing the 7th of January. I have attached a spreadsheet for everyone's reference.
Using Excel 2010.
I extracted data from .pdf to Excel using Able2Extract. Now I need to scrub the output a bit.
I see commonalities in the data for the start and stop of each set of data that I can key in on.
Once I find the start and stop points for each set of data I would like to fill all rows in-between the points and then discard anything that remains outside of these boundaries.
I have something wrong in my logic, way too many rows are deleted.
Code:
Option Explicit
Sub GetLineSets()
'Purpose: Identify relevant line sets, delete all other rows
Dim wbBook As Workbook
Dim wsData As Worksheet
Dim strFormula As String
Dim lngRows As Long
Dim C As Range
Dim blnFlag As Boolean
[Code] ......
I'm trying to use the following IF formula:
=IF(J2>$J$143 and J2<$J$144,"1","0")
But it doesn't work - I obviously have the formula wrong. The problem arises with the inequality. how to have two conditions within the "logic test".
I 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
On 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.
i really want to make it into a range formula but cant work it out.
View 2 Replies View RelatedI'm working with Excel 2003 on a peice for work that requires users to enter their current grade and, if they have one, a temporary grade. Another sheet in the workbook needs to add together all those in a certain grade and who answered a certain way to a drop-down table, so that I can use it to figure out what percentage of staff are of each grade. At the moment it looks like this:
=SUMPRODUCT(--(Work_Data!F2:F9827="Grade 1"),--(RIGHT(Work_Data!K2:K9827,3)="Top 25%"))
Which only draws information from the current grade (column F) and the answer they give (Column K). What I can't come up with is a formula that will only take column F as long as column G hasn't got a temporary grade, but if it does have a temporary grade will take column G instead.
how to apply this function to my sheet given below. In this budget sheet, I want to create a graph which would show all the items percentage (item by item) with the values given in columns C & H only, leaving out the lines which are empty or bear 0 values.
View 2 Replies View RelatedI am wondering if using an "if" statement or a case statement would be good ways to check two worksheets in two different workbooks to see if their names match? I could use some help correcting my syntax too:
For 1 to ws.count
If Worksheet("one").Name = Worksheet("two").Name
Worksheet("one").Range("A1:G84").Copy
Worksheet("two").Range("A1:G84").Paste
Endif
Next ws.Count
I have a list of data for each year dating back 10 years and some of lists had entries that weren't available so the cell showed up #N/A. I searched and replaced them with 0 but when I graph the data it skews my graph. I was wondering if it was possible to take a logic (IF) statement that says if the cell = 0 then take the average of the next and previous data points so the graph looks smooth. i.e. =IF(G444=0,(G443+G445)/2,G444) This gives me the correct number i want, but i want to apply it to all my lists like a format if possible.
View 1 Replies View Relatedconstruct a SUM formula. Have a problem with this macro:
View 5 Replies View RelatedI 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
My question is about the If-Else Construct.
I often write If-Else statements that require an action be taken only if something is true. If that something is false, no action is to be taken.
My question is, how do you code "no action".
The following is what i usually
I've got a formula in my spreadsheet that is quite lengthy. I could use an array formula to replace this formula...but I don't know how to construct it. Below is an example of my current formula:
View 4 Replies View RelatedI want to construct a calendar in 7 columns; all Mondays of the year to be in the first column headed "Monday", all Tuesdays of the year in the second column headed "Tuesday" and so on till the last column headed "Friday" containing all Fridays [ignoring the month titles].
I use the applicaiton timer but it said time is 5 so what's the problems? Below i want to change to case construct.
Sub GreetMe()
If Time < 0.5 Then
MsgBox "Good morning!" & " It’s now " & Time, , “The Time”
ElseIf Time >= 0.5 And Time < 0.75 Then
MsgBox "Good afternoon!" & " It’s now " & Time, , “The Time”
Else
MsgBox "Good evening!" & " It’s now " & Time, , “The Time”
End If
End Sub
this is what i did so far but still having problems with it with case construct
Sub GreetMe()
Dim morning As Integer
Dim afternoon As String
Dim goodevening As Integer
Select Case morning
Case Application.AutoRecover.Time < 0.5.......................
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 RelatedHow can I construct a polar plot in excel 2007, I would like to plot the following:
Gain (modulus) è° (argument) 1.688 -35.94 1.43 -86.4 1.125 -130.6 0.95 -156.76 0.747 -191.86 0.607 -223.24
I am using the formula below to return a blank cell if either logical test returns true but apparently the OR operator returns a #VALUE error when applied to a non numeric value in a cell - in this case cell E40. =IF(OR(E40="N",P40=""),"", SUM(P40,S40,V40,Y40,AB40,AE4))+F40. My goal is to return a blank cell if E40 contains the letter N or P40 is blank, otherwise perform SUM(P40,S40,V40,Y40,AB40,AE4))+F40.
View 4 Replies View RelatedI 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 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.
I am trying to find a script where I can see information more clearly. I am trying to copy certain cells and have them into a single row. For example, everytime the word USER comes up, I want that in a single row and all the information that comes after it and below to be in the same row. So I would want all this information in a single row. And it would start a new row when the word USER comes up. I will attach an excel table for an example. Working progess.xlsm
1READY
LU*
USER=irrcertaNAME=CERTAUTHAnchorOWNER=irrcertaCREATED=01.097
DEFAULT-GROUP=PASSDATE=00.000PASS-INTERVAL=N/APHRASEDATE=N/A
ATTRIBUTES=REVOKED
REVOKEDATE=NONERESUMEDATE=NONE
LAST-ACCESS=UNKNOWN
CLASSAUTHORIZATIONS=NONE
NO-INSTALLATION-DATA
[Code]...
The below code tests a particular cell before displaying the userform. If Range ("Bal_BF") contains an error (such as "#N/a"), a message box is displayed and the form should NOT display.
However, the code is still trying to display the userform, and gives me a error message "Object variable or With Block variable not set".
When Bal_BF contains a value, the form displays correctly.
Private Sub UserForm_Initialize()
If Employee = "" Then Call Retrieve
If IsError(Range("Bal_BF")) Then
Call MsgBox("Your name doesn't appear in the Holiday & Absence file." & vbCr & vbCr & _
"Please see Leila Fretton or Neil Jimack about this.",.........
In the past I have used VBA script to change column with size based on cell values as per follows:
[Code]....
Now I wish to change row size in a similar fashion only the criteria will be a maths function rather than a string in a single cell. The maths function will simply be a sum of cells in a row. I will need to test whether or not the row ads up to a number greater than zero.
To illustrate what I am trying to achieve I have (poorly) written out a rough attempt at the code below:
[Code] ....
I am unsure how the syntax works for this, I considered defining the range as a string first so I could test the value of a single variable (by defined name) rather than directly referencing the row of cells in the formula. As this code needs to be repeated many time for many rows the simplest solution would be b.
Collumn AZ 2:2000 should always have a value of MB5017 in the first 6 positions on the left, with an additional four digits following. I need to test if the value of cells AZ2:AZ2000 are MB50017 - leaving the variable last four digits. If not than replace them with "MB50017"
Column AZ
MB500174443
MB500174446
NB500176554
VB401038789
The first 5 characters should always be MB50017 plus the last four digits
The column should then look like:
MB500174443
MB500174446
MB500176544
MB500178789