# VBA AND Logical Operators

Mar 7, 2009

I'm trying to meet set criteria in b5 using AND the first works if both conditions are met in two cells but bur won't on one cell?

Private Sub PTIColours_Click()
If Range("b5") > 0 And Range("c5") < 0.149 Then
Range("a5").Interior.ColorIndex = 54
End If
End Sub

But this dosen't

Private Sub PTIColours_Click()
If Range("b5") > 0 And Range("b5") < 0.149 Then
Range("a5").Interior.ColorIndex = 54
End If
End Sub

I have tried searching for a good example of how to use logical operators on Mr Excel however the search is frustrated by "Small Words" in the search field ie IF, AND, NOT ,OR are too short to search.

## Logical Function: Logical Test With Three Possible Results

Apr 8, 2009

I am trying to create a logical test with three possible results:

h2 <10.2 "needs improvement"
(if false) h2=10.2 "meets standard"
if false h2>10.2 "exceeds standard"

Basically, I am referring to a cell to determine if it meets, exceeds or does not meed a specific standard. In this case 10.2.

## How To Use Math Operators In Spreadsheets

Aug 24, 2006

Can anyone list the formulas needed in Excel

+
_
x

divide

## Compare Variants With Operators

Dec 5, 2006

If UCase(TextBox1.Value & "," & TextBox6.Value) < UCase(currsheetname)

This works most of the time, but for something like:

Smith,Bob < Smith,Jim it will give erratic results. currsheetname is not declared so is a variant I guess.

## Mathematical Operators And Symbols

Jan 5, 2008

I am trying to write some code that will repair errors in a standard workbook. One of the errors is a blank cell that needs to be changed to "325¨¬F ¡Â T ¡Â 350¨¬F". How do I insert these symbols/operators into the VBA code?

## Setting Ranges For Comparison Operators

Jul 21, 2009

Is it possible to set a range using comparison operators? ie. if a number lies within x and y then such and such happens.

## Change Operators Based On Cell

Jun 26, 2006

I am trying to alter the type of operator used in one formula based on the result of an argument in a separate cell generally as follows: If Cell 1 equals something then use the ">" operator, otherwise use the "=" operator. I appreciate this sounds really simple and I'm having a major brain fade as I cannot figure out how to relate the two arguments as follows: [If(Value 1 "Operator" Value2, then do something, otherwise do something else)] where the operator is pre-defined in another formula.

## Move Data Based On Boolean Operators

Mar 26, 2008

I'm trying to figure out how to reorganize data based on boolean values in two different columns. My goal is to write something in VBA that will look to see if the boolean in the first cloumn is true then procede to check if the boolean in the next column, same row is true. If that is the case, it will take several values from the row and move them to another worksheet. If the boolean in the first column is true, but the second column is not, it will look down the rows of the second column until it finds true then move several values from rows it has passed through. Here is an example of the sample data I will be dealing with.

Date Time Duration Boolean1 Boolean2
6/22/200715:0032TRUETRUE
6/22/200718:0058TRUEFALSE
6/22/200719:0060FALSEFALSE
6/22/200720:0060FALSEFALSE
6/22/200721:0060FALSEFALSE
6/22/200722:0060FALSEFALSE
6/22/200723:001 FALSETRUE

Based on the example data my goal is to move the data in a new worksheet that would look something like this

Date Start End Duration
6/22/2007 15:28 16:00 32
6/22/2007 18:02 23:01 299

## Determine Math Signs/Operators In Cells Before Calculation

Apr 1, 2008

I have Three different combinations which are in cell A & B, These combinations are with similar signs as in A1=2- & B1 = 2- (& A1 = 2+, B1=2+). Then I have numbers with no signs which A1 =2 B1= 2. My approach is to limit to these and these combinations only and use a formula which is uniformally applied on these combination. I want A1 and B1 to take only like signs like -- or ++ or without any sign. The rest shd be ignored. IS there a way of sorting two columns which have different combinations liek -- or ++ or -+ into a area where u can apply a particular formula? Hence first i take a number and move signs on the left and then apply this

IF A7 = 2- . B7 = 3- THEN

IF(OR(RIGHT(TRIM(B7),1)="-",RIGHT(TRIM(B7),1)=" ",RIGHT(TRIM(B7),1)="+"),LEFT(TRIM(B7),LEN(TRIM(B7))-1)*3,B7*3)

## Logical OR In VBA

Feb 20, 2009

I am trying to use an OR in a VBA IF statement. The condtition finds one vale but not the other. Due to the nature of the data, a value is marked either on, off, or both.

I have two seaches in the macro: one looks for on and both, and runs a sub, the other looks for off and both and runs a different sub. However I can't get the both case to run as it should.

If (Cells(i, CurCol).Value = "On") Or (Cells(i, CurCol).Value = "Both") Then
Cells(i, CurCol).Select

## Using #VALUE! In A Logical Statement..

Oct 19, 2007

I have a spreadsheet wherein I've got a list of names, some of which say (See XXX) where XXX is a number. I've extracted the number to column 2. In column 3 I have another reference number. I essentially want to use column 3's numbers only if there is no value in column 2 (i.e. where there's no "See XXX in the list of names). When there is no value in column 2 I have #VALUE! - but I can't seem to use it in a logical statement. How can I do so? Or is there an easier way to pick between 2 cells?

## If And Constant Logical

Jun 17, 2008

i have problem with a long code.

So I divided that code in 4 parts.

So I have
module1 -> Sub Macro1()
module2 -> Sub Macro2()
module3 -> Sub Macro3()
module4 -> Sub Macro4()

I have declared

Option Explicit

Dim mdNextTime1 As Double
Dim myEnter As Integer
'Dim myK1 As Integer
'Dim myK2 As Integer
'Dim myK3 As Integer
Dim myK4 As Integer
Const myC As Integer = 82
Dim Lastrow As Integer
Dim myActualRow As Integer

all my sub have the following structure:

## Use Of Logical Functions

Jun 16, 2006

why the following code within a macro does not work

Cells(x,11).Value = If(Or(Cells(x,136)="", Cells(x,134)="","",Cells(x,136)-Cells(x,134))

## Logical Formula For Date

Jun 22, 2014

In column N I have a list of dates. In column Z I want to display either "Yes" where the date in column N has already passed or "No" if the date in column N has not passed. The formula I'm using in column Z is =IF(N2<TODAY(),"Y","N") however it just gives me "No" on every row regardless of whether date is passed or not.

## Logical Formula: Check In Q24 Such That If (P24=#DIV/0!,0,P24)

Dec 16, 2008

I need a solution for this situtaion! Iam using a french version of excel2003!
I have #DIV/0! in P24 (i.e an error value) So I need to check in Q24 such that if (P24=#DIV/0!,0,P24). I need to have a logical formula for this one!

## Under What Conditions Does A Logical Test

Feb 7, 2010

Under what conditions does a simple logical test, like

## Function Allows For 3 Logical Tests

Oct 11, 2008

I am working on a homework assignment, the books method was crap so I did my own method. I am almost done except for 1 last thing. I cannot find any function that will allow for 3 logical tests. I have a screen shot here explaining what I need to happen.

## Logical Tests For Cells E5 And E6

Nov 4, 2008

I inserted the following logical tests for cells E5 and E6. I saved the workbook with SE(B) in cell G3.

## Logical Functions Query

Nov 28, 2008

i had a query regarding a report i have to provide daily. i want the result to be in the form of a number which i pick up from another file.

now there are three colums in this file. one shows the date (i.e. in the ddmmyyyy format, e.g. 5/11/2008). another shows a region (e.g. SOUTHD) and the third shows a number (e.g. 445) of which i have to count how many instances occur for a particular day.

Hence for any given cell in which i would place this formula, i would need the number of times the number 445 occurred from the third column provided the date from the date column was, say, 5th November 2008 and the region was showing as SOUTHD.

pretty sure the COUNTIF function can be used to make such a formula when combined with some logical functions but am unsure on how to do so.

## Using Vlookup As The Logical Test

Sep 16, 2008

Here is the formula I am trying to write:

=IF(A21=(VLOOKUP(XX52,AN24:AN52,0,V38),V45,0)

The above is in cell X45

This does not work, but this is what I want it to do:

I have a list of text, if cell A21 equals a range in that text then I want the cell to return whatever is in Cell V45, if A21 does not equal within the range of text, then I want it to return a zero in cell X45.

You can see that I tried using Vlookup as the logical test, but I am not doing something right or vlookup cant be used that way.

## Use Of Logical Formula From A List

Sep 14, 2009

How to find whether a particular cell is blank or is text in a given row/column?
To elaborate more, I have some data in a row:

A B C D E
15 N 16 17 18

I am using a below formula to find whether there is any text in the row

ISTEXT(row(A1:E1)) but I am getting result as False.

## AND OR IF Function For Logical Sequence

Dec 26, 2007

How we represent AND OR in a function how many logical sequence can 1 if take
example =if((w4>u4 and v4=0) or (t4>r4 and s4=0);"yes";"no") can excel(2003) accept this , or there is an error in it what is error?

## Logical Commands Based On Criteria?

Mar 30, 2014

View attached file. I have a complexity rating that I have developed for a set of projects that I am working on. Management of my company have signed off on this matrix as they believe it is most appropriate. In my projects pipeline tab, I have created the risk and complexity ratings using logical commands.

projects.xlsxâ€Ž

## How To Do Two Logical Tests For A Conditional Statement

Feb 12, 2009

How do you do two "if" statements?

I need to have two tests before i get a final result so i need to know the protocal to get two if statements.

## Multiple Outcomes From A Logical Formula

Sep 28, 2009

I am trying to get more than one outcome from a logical function in a single cell. I am teaching a class and i want this particular cell to add up all the points from tests and quizzes and then give me an outcome of a grade (A,B,C,D, or F) Right now i can only get the cell to give me an outcome of A or B. I need the cell to be able to give me 5 possible outcomes (A,B,C,D,F).

Right now i have this formula in this particular cell......=IF(Q6>234,"A")......so if Q6 is greater than 234pts they get an A...if not they get a B. How can i also give ranges of total points to have the cell give C,D, or F?

## Logical Value :: Change Word False To 0?

Mar 13, 2006

When using a IF formula, one answer is false.
Can the word FALSE be changed to a zero?

## Complex Logical/conditional Formula

Jun 12, 2006

I am working between two worksheets. In one worksheet I have a list of
account numbers. In the second worksheet I have the same account numbers in
a different order. What I am trying to do is, If the account number from
worksheet one is found in the column with the account numbers in worksheet
two, I want to display the matching title for the account number that is in
the same row but different column in worksheet two.

So essentially (if acct # from column x in wks 1= an acct in number in
column m from wks 2, display the corresponding title in the same row of the
matching acct # found in column m). Is this possible?

## If Logical Test Multiple Criteria

Feb 17, 2012

I am trying to build a logical formula and it turns wrong values. I have four criteria in numbers, the codes are: 1 invoices, 3 credits, 7 Debits, 8 Returns, 9 Payments. All of these codes have positive numbers. I am trying to convert "credits", "returns", "payments" in negative . the rest "invoices" "debits" stay positive. In column A i have dollar values all positive, column B "codes" mentioned above, column C "i want to put the correct values.

## Logical Test For Date Ranges?

Oct 8, 2012

I have a spreadsheet where I have a list of patients who were evaluated on a certain date unique to that patient. Some of the patients have a subsequent date of death, and some do not (they're still alive).

I need to create a logical test which indicates whether or not the patient died at specific intervals after their unique evaluation date-- at 3 months, 6 months, and 12 months. Additionally, I need to measure whether the patient died at 0-6 months, or 0-12 months, or is still alive.

In other words, if Patient A was evaluated on 1/1/2008 and subsequently died on 10/31/2008, I need to do a logical test that says 3 months = false (no death), 6 months = false (no death) and 12 months = true (death). True = 1 and False = 0 (so I can do a simple column add to figure out how many died at each interval). Additionally, for the 0-6 month test it should calculate = 0, but for the 0 - 12 month test it should calculate = 1.

I'm totally stumped as to how to do this. Currently my spreadsheet reads as follows: (I've put ** next to cells that I need formulas to calculate. If no **, assume it's a value I've inputted manually)

A1 = Patient number (numerical order)
B1 = Patient Initials
C1 = Initial Evaluation date
D1 = Date of death (if any- blank if patient is still alive)
E1 = Patient death indicator (1 = dead, 0 = alive)
F1** = Indicate whether or not patient died within 3 months of evaluation (1 = dead, 0 = alive)
G1** = Actual date of death within 3 month interval
H1** = Indicate whether or not patient died within 6 months of evaluation (1 = dead, 0 = alive)
I1** = Actual date of death within 6 month interval
J1** = Indicate whether or not patient died within 12 months of evaluation(1 = dead, 0 = alive)
K1** = Actual date of death within 12 month interval
L1** = Did patient die within 0-6 months of evaluation? (1 = dead, 0 = alive)
M1** = Actual date of death within 0-6 months
N1** = Did patient die within 0-12 months of evaluation? (1 = dead, 0 = alive)
O1** = Actual date of death within 0-12 months

## Offset Formula - Logical Function Using OR

Jul 10, 2013

I have following formula that returns #value!

=IF(OR(OFFSET(LIVE_JNL,ROW(5:5),COLUMN(C:D))="",OFFSET(LIVE_JNL,ROW(5:5),
COLUMN(C:D))=0,""),OFFSET(LIVE_JNL,ROW(5:5),COLUMN(C:D)))

Where the value in the cell which is referenced is either blank or zero then blank must be returned, otherwise the value which is being referenced by the offset formula.