How can I write the equivelant of an if(and( statment in VB? What I would imaging it would look like is this...
If _________ = true
AndIf ________ = true Then
____________
End if
It seems like this should be simple, but after searching, and searching the help files, the internet, and this website, I am no closer to figuring this out.
Ok ive been playing about with excel for the past week to get a spreadsheep up and running so i can see the totals of some things and now its getting a little complecated for me (ive done the easy stuff lol)
Ok here is the situation and i'll try and explain what im doing as best as i can. Im trying to make a spreadsheet that shows how much a resorce can be refined so lets say in a1 to c1 i have 400 in each but i can mix a with b and b with c at the ratio of every 20 of a and c to ever 10 of b
so somewhere in my spread sheet id have the result 20 and 20 as products ato c can make 20 of product d and e i hope that makes sence?
for every 20 + 10 of a(400)+b(400) = 20
and for every 20 + 10 of c(400)+b(400) = 20
so if i had 600 300 600 instead of 400 i can only make 30 of product d of e (id have to choose)
I need to format the background color of a row using an if statement or something similar. If have a certain cell has a specific value I want the entire row that the cell is on to have a specific background color.
View 9 Replies View RelatedI have another question with my red sox scores excel sheet. On my overview I want to be able to select any team, and view the indiviual stats against them. For the life of me I dont' know/can't find how to even do a simple yes no if statment to what the value in the drop down menu box is.
View 3 Replies View RelatedI don't even know if using the IF statement is the right function, but anyway. I am using VLOOKUP to complete a supply list, but of course if the supply name isn't on the first spreadsheet then the result is NA. Instead I want it to return 0.
View 3 Replies View RelatedI would like to check whether 2 conditions are satisfy in an if statement. One condition is of data type text and it is stored in a variable calls search1 whereas the other condition is of data type date and i intend to store into a variable calls search2. Could someone tell how to put search 2 into the below code. Basically, i want it to be like
if Cell A is equal to search1 and CellB is equal to search2 then
return value that is associated with the search in cell c
ActiveSheet.Cells(r, 13).FormulaArray = "=MIN(IF(Sheet1!$A$1:$A$20=""" & search1& """,Sheet1!$C$1:$C$20))"
why the code below is very slow with the extra if statment in it (marked with **) i have googled around and found that my for staments are correct i have tried screen updating etc. i have added a timer to see what diffrence it makes, what i have is 3 sheets 1st has about 100 rows with a number and time same as second but that only has 2 rows of the same the 3rd sheet has about 100 rows of just a number what the code does is loop through the 3rd sheet (build list) when it finds a number it looks on the first sheet to find the same number then gets the data in the second coloum and so on until it reaches a cell that says stop.
i have added a button to start this process, with the ** line in it takes 311 seconds with out it it takes 0.17 seconds
I have a list of codes. Some of them may be duplicated. I want to use the conditional formatting and the IF statement to highlight in a particular colour any codes that have appear twice or more. I used to know how to do this but now I cannot get it right.
View 2 Replies View RelatedI have been trying to get a formatting programme to work for well over a week and no matter what I try I seem destined to fail. I am trying to use an IF statement in VBA to format a cell if the result of the IF is true. The values in the cells being formatted have been generated from a VLOOKUP statment (looking at a table on another sheet of the workbook). I have tried to use conditional formatting but cannot get past the limit of 3 conditions.
Note in the code below I have values that may appear the same but to make them unique they have a single (or double) space before and after the character. Not sure if the code I have below is workable, if anyone has a better idea please let me know (its a little long winded). I am currently self learning VBA (through forums and the like) and below is the best I have come up with... however when I try to run it I only get a Run-time error 1004, application-defined or object defined error.
I would like for the If statment to hide multiple labels and buttons. How can I add more than one condition after Else:?
View 3 Replies View RelatedI am trying to use an IF statment to look at 2 values, 1 from a defined range and the second based on offsetting the the first to Format a number of cells with a worksheet. Affectivley if the First Value is "E" and the value two columns to the left is a Sat or Sun I wish to format the row slightly differently from if it is a weekday. (There will be an M and a L as well)
View 5 Replies View RelatedIf you look in the file you will see a records sheets and a form sheet. I want to see if a record has been broken. All the times are in seconds and the distances are in centimeters. How can I find a certain row, compare and act based on an if statment?
View 2 Replies View RelatedI am trying to perform a calculation in a cell that leaves the value 0 if before Feb 2014, changes to the value of another cell in Feb 2014 and leaves the value unchanged if after Feb 2014. I can get to the Feb 2014 value but after that month the value changes to 0.
The Cell formula that I am trying to use is as follows:
=IF(YEAR($A$3)<2014,"0",(IF(YEAR($A$3)>2014,H114,(IF(MONTH($A$3)<>2,H114,AA118)))))
A3 contains TODAY()
H114 is the cell being calculated
AA118 is the cell containing the calculated value for the current month.
I would like to try and achive a conditional format that will require a lookup function. when the condition is met it places Yes or No into a cell and changes that row colour to red for the NO condition. and after 10 days from now the rowcolour is removed.
View 6 Replies View RelatedI decided to try to change it into a Case Statement. Here is what I have now. But the problem seems to be this time at this line: When I have "01" in C5 the script just keeps going?
View 14 Replies View RelatedI'm having trouble with the two formulas working in conjuction with each other.
Condition 1:
=(MOD(ROW(),2)=0)*(COUNTA(3:3))
Condition 2:
=TODAY()>=A1
"Condition 1" seems to override "Condition 2"? Ever see that?
So I have one sheet that needs to pull data through to another sheet (which is a stats summary)
I have a drop down list containing 4 options all of which have to be counted separately on the stats summary sheet. However I only need them counted when a value is input in another cell in that row.
For example: I select option 1 from down down menu, but I only want this to be counted on the stats page when I enter a date in the "date" cell.
Here's a simplified example:
ColA
ColB
ColC
Row1
A
Y
A
Row2
B
N
D
[Code] .........
I'm looking to return the values in column A adjacent to the cells in Column B equal to "Y". The kicker has been returning only the cells where the condition is true. Column C displays the desired behavior.
The closest I've been able to get is with a simple IF statement but I'm pretty sure the answer is a far cry away from there and likely requires an array formula. I'd prefer not to use VLOOKUP or OFFSET but will if the alternative is very complex.
I need cell (O4) to display days overdue or days remaining on an assigned task based on subtracting due date (M4) from todays date, but only perform days subtraction function if (M4) is not blank and only if task complete cell (N4) is less than 100.
Have the conditional blank cell figured out, but nesting another condition for the less than 100 complete cell. So in other words, I don't want cell (O4) to subtract days and display any error, irrelevant data if there is not any due date entered or the task is entered as complete...
I have a rows of the following.
=IF(H1="","",IF(H1="Fail","1",IF(H1="Pass","2","3")))
=IF(H2="","",IF(H2="Fail","1",IF(H2="Pass","2","3")))
=IF(H3="","",IF(H3="Fail","1",IF(H3="Pass","2","3")))
=IF(H4="","",IF(H4="Fail","1",IF(H4="Pass","2","3")))
=IF(H5="","",IF(H5="Fail","1",IF(H5="Pass","2","3")))
The following are the outputs.
1
2
2
2
1
When I do summation on the output cells, the results returned 0. Do I have to do anything procedure in order to add up values that are generated by If-Else?
3 different condition using IF. See the attachment.
View 2 Replies View RelatedI need to find the last date when a company bought any product from my company.
So i have taken the export from the CRM, now i want if the company name collumn is same take the max function in the date collumn fro that perticular company.
I am attaching the file also with a small example.
Is thre an easy way to set up the following testing conditions:
- if A1=A2=A3= ... =A10, do something?
--
Additional information:
- I'm using Office XP
- I'm using Windows XP
I have 3 columns. Column A contains numbers, column B names and i would need columns C to contains the sum of the numbers of column A if they have the same name in column b. I have made an example to make it easier to understand so here is a printscreen of what column C would need to look like: http://img441.imageshack.us/img441/3417/57644131.jpg
View 5 Replies View RelatedBeen teaching myself VBA and I've gotten stuck, and no matter how I search I can't seem to find an answer...
Trying to set up a condition is vba where if a cell value minus another cell value is greater than a static value (elsewhere in the sheet), then to turn the font green.
Code so far:
Dim Cval As Integer
Cval = Cells(3, 6).Value
FinalRow = Cells(65536, Cval).End(xlUp).Row
For i = 10 To FinalRow
If Cells(i, Cval) - Cells(i, Cval + 1) > R6C5 Then
With Cells
.Font.ColorIndex = 10
.Font.Bold = True
End With
End If
Next i
End Sub
I can't work out the right sintax to get a if condition to work correctly.
I have gotten this to work with conditional format, but if you want to copy/paste elsewhere, it doesn't paste the format result correctly...
Need VBA code to sum total all entries that are in range F7:H70 that are 600.00 or over into a variable called lng1099sTotal. For example:
......F....G.....H...
7..125..610..850
8..900..301..425
9..984..325..600
lng1099sTotal should equal 3,944.00
(610+850+900+984+600).
There are 5 sellers and their sales are captured in an individual sheet for each one. Also there's a general sheet where all the sales from all the sellers can be seen.
Now, each seller receives a commission of 5% for each sale he makes. However, when the sum of all the sales of all the sellers is greater than a predetermined goal (let's say 1000 USD), the commissions change to 7% instead of 5%.
IMPORTANT: The seller only receives 7% of commission from the sales made AFTER the goal was reached, the ones before are paid at 5%.
So I need to be able to split the sales in the end to know which were made before and after the goal was reached, so that the commissions are paid correctly.
I am trying to arrive at the results in the yellow cells without needing the blue cells inbetween.
A sumproduct would be best but the 2nd row of blue cells has an if statement in it.
potential VLOOKUP (or possibly another function)
I have a table with about 14 columns, and need a VLOOKUP based on column A.
The problem is Column A has 2 of each unique ID - but column B is the subtype.
For example
Column A Has names (Bob, Jim, Steve, etc) Column B has Opt in or is blank (if they opt out the cell is blank if they opt in the cell says Opt in)
So essentially it's like a vlookup - as i need to read across the columns based on the name, but i also need to check column B if they have opted in.
formula using if condition. I attach the sample file about my problem.
View 14 Replies View Related