IF Skip (test If Any Cell In AZ 2:2000 Is Greater Than 200)
Jun 15, 2009
In column AX2:AX2000 there is a value(alpha numeric) MB60176685 and in column AX2:AX2000 there are various values.
I would like to test if any cell in AZ 2:2000 is greater than 200. If yes then blank AZ in that row. If less than 200 go to the next row. The value of AX will remain the same in that row.
SEE EXAMPLE of results BELOW
AX AZ
201
MB400174444 10
MB400174444 155
View 2 Replies
ADVERTISEMENT
Aug 21, 2013
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
View 3 Replies
View Related
Aug 25, 2009
I have to make a table that shows that a if someone purchases
less than 5 items they receive no discount
5-10 items they receive 2% discount
11-20 items they receive 5% discount
21-50 items they receive 8% discount
over 50 items they receive 10%
and it has to be done in a way that the discount rate can be calculated using Vlookup I am struggling to find the best way to write this table. i tried numbering 1 to 50 and writing the corresponding discount rate in the second column but this looks untidy and can't calculate greater than 50 as i am not sure how to write it in the cell so it reads as >50 and not just 50.
View 4 Replies
View Related
Dec 16, 2003
What is the command to move the active cell? For example, suppose C15 is the active cell, and I want a macro to move the active cell one to the right. Or maybe 3 to the right, and one down. The deal is, I'd like to be able to make the active cell move in a fixed pattern relative to the cell that is already active (before the macro is run), rather than moving to a final fixed destination (which would be something that even I could figure out). In other words, if I select R20, and run the macro (or whatever other mechanism you guys come up with), then R21 will be selected; likewise, I can click G12, run the macro, and G13 will be selected.
View 9 Replies
View Related
Jun 27, 2007
Sub BlankGcolumn()
Dim cl As Range
For Each cl In Range("B8", Range("B15").End(xlUp))
If cl.Value = "" Then
cl.Offset(0, 0) = cl.Offset(0, -1) & " " & cl.Offset(1, -1)
End If
Next cl
End Sub
in the "Next cl" part, i want it to skip the next cell and do the one after it.
Ive tired: ....
View 9 Replies
View Related
Nov 9, 2009
How do I test if a cell has #N/A?
View 4 Replies
View Related
Jul 1, 2014
I want to make the formula: x=SUM(A * e^(-b*d)), wherein A and b are parameters and d the distance.
I've just over 10.000 rows and 15 columns (B4 to P10013) with distances. For each column i give a formula =$S$2*EXP(-$R$2*B4) and extent that for all colums and rows, then I sum all the columns. This works fine, except for the fact that when there is an empty cell the outcome is wrong. Because I set for example A as 10. The answer for that cell is 10, because he sees an empty cell as 0, i guess.
So how do I skip these empty cells?
View 5 Replies
View Related
Apr 24, 2013
I have a spreadsheet with a lot of account numbers. I want my Macro to highlight the row A:C if certain accounts are found in the spreadsheet.
For Example:
Account numbers "10998-0000" and "18999-0000" are allowed to be in the spreadsheet so don't want them highlighted. But Account "10110-0000" should be highlighted.
I know the Macro will be big but I was hopping something like:
If ActiveCell is not accounts .... I would then list all my account numbers... Then
and at the end a loop until ActiveCell is Empty.
I have this so far.
Range("A12").Select
MyColumn = 1
i = 12
Range("A" & i).Select
Do While ActiveCell.Value ""
[Code] ........
View 8 Replies
View Related
May 18, 2014
I have a product list and the products have different levels. I want to lookup and return the the rows with level C and return the article number and price. If the row has level A or B I want to skip to the next level C.
I want to use a formula or array formula and not a Macro.
Data
Article
Price
Level
KL507
56
A
[Code] .......
Expected result in other table
Article
Price
JK879
98
FJ893
98
RT344
23
View 4 Replies
View Related
Oct 10, 2012
I need a UDF to test a single cell if it is colored. If it is the result returned should be a 1, if not the result will be blank. This is what I have so far, but I don't know much about VBA.
Function Filled(MyCell As Range)
If MyCell.Interior.ColorIndex > 0 Then
Result = 1
Else: MyCell = ""
End If
End Function
View 3 Replies
View Related
Jul 15, 2014
I have few column values to count. I don't want the hidden column value. In that formula the hidden value also counted. How to ignore that hidden column value count, using macro/formula.
eg:
col A - col B - col C - col D - col E - col F
Task -- ok1 -- ok2 -- no1 --- ok3 -- ok4
Col D should be hidden. Final count result should be: OK = 4; No=0
View 3 Replies
View Related
May 17, 2006
I m trying to bypass some code if a certain condtion is meet. If Cell A1 has a zero in it I want to skip the following code and continue one with the rest of the macro. This is the code I have.
j = 0
RowCount = Cells(Cells.Rows.Count, "a").End(xlUp).Row
cell_a = Range("A1").Value
If Not cell_a = 0 Then
Rows("1:" & cell_a * 2).Select
Selection.Insert Shift:=xlDown
Range("A1").Select
For i = 1 To cell_a * 2
Range("a" & i).Select
ActiveCell.Value = j
j = j + 0.5
Next i
I put the If Not/Then statement in there to try and figure out what do. But I don't know what to put after the THEN statement. Basically I want run the code after the THEN statement if there isn't a zero in cell A1 and skip the code if A1 contains a zero.
View 2 Replies
View Related
Aug 23, 2006
Have the marco (see Code below)that I am trying to pull only records that have a "y" in a column. The problem I am haivng is that I also get cells that are blank and rows are skipped if the cell is blank or not "y".
Have tried searching for an answer or example for what I want to do. Looked at autofilter but had no luck.
Sub Macro1()
Set rd = Sheets("ActiveHerd") 'set read data sheet as rd
Set wd = Sheets("SaleSheet") 'set write data sheet as wd
For i = 12 To Range("A65536").End(xlUp).Row ' set i to the last row in column A
If UCase(Cells(i, 1)) = "Y" Then Range("A" & i & ":c" & i).Copy Destination:=wd.Range("AA" & i)
Next i
End Sub
View 9 Replies
View Related
Dec 28, 2008
I need to take a specific action when a cell has an actual formula in it versus when it just has a "value". Is there a procedure or command which will allow me to identify if a certain cell has a "formula" (like =sum(a1: a5) ) or just a value.
View 3 Replies
View Related
Aug 19, 2009
How would I write an IF Then Statement to test if a cell is blank? (meaning it could have "Div/0!", text, 0, or "n/a".)
View 4 Replies
View Related
Feb 17, 2012
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].....
View 9 Replies
View Related
Sep 10, 2008
Is there formula to use if I want an IF statement to add a date "TODAY()" to a cell if a corresponding cell is highlighted? I would search for this in the archives if I had a clue what to search for. "Conditional formatting", maybe?
In Col J "Appt. Date", I want to add today's date if the cell in Col C of that row is green.
How would that work? My worksheet is posted below: ......
View 10 Replies
View Related
May 4, 2006
I find myself needing this often and hope there is an elegant formula that can make this easier.
Is there a formula that will test if a particular cell is referenced somewhere in another array or vector? Specifically, I find that I have to aggregate long lists into categories to fit budget formats of various lenders and investors. For example, my detail budget has separate rows for Water, Sewer, Garbage, Electrical, and Gas. These expenses have to be aggregated on one funder’s budgets as “Utilities.” Sometimes after going through this I find that my totals don’t add up, i.e. I left an item out of the aggregated budget. I would like to be able to add a column on the detail budget to test if each budget item has been referenced in the aggregated budget.
View 3 Replies
View Related
Dec 16, 2006
I want to add an IF statement to my macro that will run only if a cell on the worksheet is not blank (empty). I know how to use the if statements but can't figure out how to test a cell for contents.
View 4 Replies
View Related
Jul 10, 2007
I'm testing to see if a cell has nothing in it, and if that is true then it will execute the code. My problem is that even though the cell is blank the field does have a drop down list (list validation set for that field) below is the code i used, but doesn't work.
If Range("b3").Value = " " Then
View 2 Replies
View Related
Jan 26, 2008
I want to reference 2 cells to open a msgbox. Currently I use this formula in a cell "=IF(E4="C",IF(A4>30,"OT","")) I am trying to write a macro that will open a msgbox instead. This is what I've tried so far
Private Sub Worksheet_Change(ByVal Target As Range)
If Target(1, 1).Address = "$E$4" Then
If Target(1, 2).Address = "$A$4" Then
If Target(1, 1) = "C" Then
If Target(1, 2) > "30" Then MsgBox("1")
End If
If Target(1, 1).Address = "$E$4" Then
If Target(1, 2).Address = "$A$4" Then
Target(1, 1) = "F" Then
Target(1,2) > "38" Then MsgBox("2")
End If
View 3 Replies
View Related
May 24, 2009
if cell = null, then skip it to the lower cell.
the data is like this:
A361
A37
A38
A391
and i want to be like this:
A36
A39
without delete the row.
View 14 Replies
View Related
Aug 22, 2014
Basically I have a column (lets call it column A) whereby I manually fill the cells green once I have received some documents, another column which has a numeric value in it (column B) and I want to create a third column which basically just copies column B but ONLY if column A is filled with a colour (actual colour doesn't matter cause I only use green)
I tried using the IF function but I don't know how to use cell colour as the logical test
View 3 Replies
View Related
Feb 28, 2008
I'm trying to write a macro to test whether a cell has any neighbouring cells that match it's value.
I'd like this to be cumulative so that the more matches, the higher the value.
I'd then like the result to be written in another cell to set up a separate grid.
So far I'm getting error messages and I don't know why:
View 9 Replies
View Related
Jan 8, 2008
I have the followinf formula
=IF(A2="","",IF(C2>TODAY(),"",IF(E2="",IF(B2="1st",WORKDAY(A2,4,$H$1),WORKDAY(A2,5,$H$1)),C2)))
What I am trying to do is cause (where the formula is) to:
If A2 is empty, put nothing,
If C2 is greater than today put nothing, otherwise put C2
If E2 is empty then if b2 is "1st" add 4 workdays to A2, otherwise add 5 workdays.
I seem to have a problem with getting the formula to put the value of C2 if it is not greater than today In otherwords if the date in C2 is 6th Jan, i want 6th Jan as the result of the formula.
View 9 Replies
View Related
Jun 11, 2013
I'm looking for a way to count the cells in a specified range if the cell contains a timevalue (such as: 0:05 or 1:15). When a cell in the same range contains a normal value (such as: 1 or 20) it should not be counted.
What formula should i use for this?
I was experimenting with:
=CountIF(N4:N50,">TIMEVALUE(0:01)")
and
=CountIF(N4:N50,">TIME(0,0,1)")
But both don't seem to work.
View 3 Replies
View Related
Apr 1, 2006
I need a function that will use a column of text values and test these values
to see if one or more of the values exist in a single cell. If it does I need
the function to return true or false.
Ie. cell A1 contains the text "Jim Smith" the B column contains the test
names (column of test values ) ie. B1 is "bill" B2 is "fred" B3 is "jim".
Because Jim is in the cell A1 I would need the function in C1 to return the
value "true". If A1 contained the text "bob smith" then function in C1 would
return the value "false".
View 10 Replies
View Related
May 6, 2009
I have two different functions, first is importing website to excel and the a second is testing string according to pattern. Each one of them is working ok. I'm trying to find a word " finance" in URL and put it into cell "A1".
Sub ParseWebsite()
Application.DisplayAlerts = False
On Error Resume Next
For i = 1 To 10
SiteURL = "URL;http://www.cnn.com"
With ActiveSheet.QueryTables.Add(Connection:=SiteURL, Destination:=Range("A" & i))
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False................
View 5 Replies
View Related
Apr 28, 2006
I have a macro that takes a value of one cell ("Cells(iFoundPass, 5")) and makes another cell (Admin_Level) equal it (the valules can be wither 'Administartor' or 'Standard'). When someone runs a macro it checks to see if "Admin_Level" equals "Administrator". The problem is that when that first macro runs, even if ("Cells(iFoundPass, 5")= "Administartor" and then "Admin_Level" will be made "Administrator" , my IF/THEN statements do not work. I think this is because the value of "Admin_Level"("Administrator" in the example above) is not text. I can't seem to figure out why. I do know, though, if I manually enter in 'Administrator' in the cell name "Admin_Level" then my IF/THEN works.
Sheets("Config").Range("Admin_Level") = Sheets("Config").Cells(iFoundPass, 5)
and then this is the logical check that runs anytime someone wants to run a macro...
If wb.Worksheets("Config").Range("Admin_Level") <> "Administrator" Then
PasswordForm.Show
End If
View 2 Replies
View Related
May 18, 2009
Please refer to attached Sinking Fund worksheet. have managed to solve most of it. Just need to figure out how to match the periods out. My requirements are on the worksheet.
1. To get the schedule on sheet 1 to stop calculating further once it has reached the actual number of payment periods as shown in H12.
2. To get the interest calculation in sheet2 to stop once the actual number of payment periods as shown in sheet1 H12 is reached.
View 3 Replies
View Related
