revised this code to reflect the "not equal to". I want the user to only enter "In" or "Out." Therefore, If cells b19:B26 does not have either word, then the message box will prompt the user to enter the correct word again. I think my code needs to be revised into some kind of loop.
View 9 RepliesI have on occaision seen -- within a formula. e.g =SUMPRODUCT(--(PMonth=$A3),--(PName=$A$1),PLTime). Could someone explain the relavance/effect or use of this as it seems outside of standard operators.
View 9 Replies View RelatedHow to use the LIKE operator? I have tried:
mytable.field like '098'
mytable.field like '098'
mytable.field like '&098&'
mytable.field like '*098*'
This is in A SQL string connecting to a MSSQL db over ODBC
Excel has operators for Less than, Greater than, Equal to, etc., etc. Does anyone know if there is a Between operator? (Maybe an undocumented or little-known feature like DATEDIF)?
There is a Between Feature in the Conditional Formatting Dialog Box - it would sure be nice to be able to use it in a non-formatting formula, or maybe you could explain how you pros achieve a "between" result without resorting to VBA.
MOD Operator gives one result when this is used as worksheet function and the different one when I use that in VBA.
Cell A1 = 01-Jan-08
Cell A2 = 02-Jan-08
In Worksheet
=MOD(A1-A2,8) Gives 7
In VBA
Reminder = Range("A1")-Range("A2") Mod 8 Gives -1
I'm using the below code to get only those rows which contains 'Deploy' or 'Pre-prod' but it gives less rows than its supposed to do.
View 11 Replies View RelatedWhile proof-reading my code, I've noticed that these two true select cases ("S" and "W") shown below wouldn't "yield" what I want. I've tried to replace AND (highlighted in red) with "," (comma) but I still get the same result - it still returns a value evaluating either one of two conditions but not BOTH:
Code:
sChar = Split(Waypoint, ",")
For j = LBound(sChar) To UBound(sChar) Step 3
Lat = sChar(j)
Lon = sChar(j + 1)
Elev = Val(sChar(j + 2))
Next j
[code].....
Iīm trying to find a way to get a formula which can look if there is a character (letīs say it is "-") in a formula which is, by the way, variable (letīs say each cell has different values like: =899+59-6 and so it goes and goes), and brings me a true or false value.
Specifically it is like this:
I have formulas to be evaluated like the following:
=899+59-6
I need a formula which can look into the formula and if it finds the character "-" or a "+", brings me a true or false message.
Can you use a variable as an operator? For example:
Dim op
op = ">"
If Var1 op Var2 Then MsgBox "It Works!"
I have parameters stored in a database and each parameter includes an operator. When I compare a given value to the parameter, I was hoping to do that dynamically by extracting the operator from the parameter and using that in the comparison.
Otherwise, the only way I can see to do it is create a Function with a Select Case list for all the operators to check the parameter, do the comparison, then return the value to the Function.
filter a pivot table that contains these 3 column fields: User 1, User 2, and User 3.
However, when I want to see the results for "John" in all those fields, I need to use one filter at a time. Is there any other way to see all results of the Pivot Table where "John" is a User, no matter if he is the 1, 2 or 3 user?
I have three columns named "Type" , "start_date" and "end_date".
Type column can hold only "A","B" and "C".
start_date and end_date columns are time stamps. ex: 9/14/13 9:35 AM
I want to do conditional formatting like,
If type = "A" and (end_date - start_date) value between 30 and 40 (in hrs) then it should be amber.
I did for greater than and lesser than values, but I am facing problem while doing the above one.
When I run this code it does the exact opposite of what I want it do (if not contained in that character list then delete). So basically if it not in the character list then delete, but it if is in the character list it deletes....
[Code] .....
I need to switch between less than or greater than in my formula based on user selected drop down that gives the user two options "" My formula has "
View 9 Replies View RelatedI dont have much experience with VBA and im looking for an operator that helps me find a word (or part of a word) in a sentence located in a cell. I have to do this to create a user friendly form. I cant use the Find option in excel because i need to do this in a form.
View 3 Replies View RelatedI'm having trouble with a small vba macro. At the end of the macro I test to see if two variables are equal and then print out true or false. However, for some reason even though the variables are equal vba is not treating them that way. I have put the values that represent the variables on a spreadsheet and used the if(x1=x2) formula and it says it is true, also, when I debug the macro and watch the values when it comes to test the logical expression the numbers are the same. I don't understand why vba does not say that the two variables are equal. I have attached a screenshot of the breakpoint where I double check the values are equal.
View 2 Replies View RelatedHow the like operator works with brackets and # tags etc...
View 9 Replies View RelatedI'm just trying to add the "=" operator to the below "" operators, as this line of code presently doesn't account for any numeric matches, of which I have plenty, and DO need to account for! Gosh, might someone here know how to integrate such a thing into this code?
If Range("I3") Temp Then Range("I1") = Temp
I'm using Excel 2003.
I have a Userform where you can enter a date and select a comparison operator from a combo box. Any one of these (= < <= > >= <>)
In VBA I then load a range into a dictionary object and then loop thru it to compare the dates in the dictionary to the date selected in the userform but I can't get the dates to compare correctly. I think it has something to do with the Evaluate function comparing the dates as strings but I'm not really sure.
VB:
For Each strKey In dictValid.Keys()
If Not Evaluate(CDate(dictValid(strKey)) & cboDteOperator.Value & CDate(txtDteSel.Value)) Then
We have just been upgraded to Excel 2010 with it's hideous menus. tell me where to find =, +,-,* and / signs in the menus before I go mad!
View 6 Replies View RelatedHow to correct my formula because it does not work?
Here the formula: =COUNTIFS(Data!C2:C24005,A17,Data!M2:M11149,"<=0")
I need to insert the formula in column C (Findings tab) which counts the rows in Column M (tab named "Data") that equal A17 (Column A in "Findings" tab) and which are less or equal zero. In addition if I drag the formula down I want to only the values be entered in highlighted in blue cells in column C (SKUs With Zero Sales in "Findings" tab) and empty cells in not highlighted cells. I attached images of these two tabs: Data and Findings.
I have a date in cell A1. I have an operator in cell A2. This info is then merged in to cell A3 with the following formula: =A2&TEXT(A1,"dd/mm/yyyy"). My data range is called "wc_date_of_loss". I want to use this info in a SUMPRODUCT formula (as the dates and operators can be changed by the end user). What's the correct syntax for that?
=SUMPRODUCT(--(wc_date_of_loss&A3))
=SUMPRODUCT(--(wc_date_of_loss=A3))
=SUMPRODUCT(--(wc_date_of_loss=TEXT(A2&A1,"general,dd/mm/yyyy")))
How Can I Copy All My Formulas In Worksheet With Out Operator "="
Like Copy Paste Special
I have a database function in which I cannot use an "OR" operator. I can add "+" on to another (Ex. Sum(a1:b1)+sum(b2:c3)). The function itself is 80-90 characters long. So after I add a few together the cell runs out of capacity. I have a analysis that simply does not lend itself to listing out individual functions and then summing them.
View 3 Replies View RelatedI'm trying to count if there are situations where column a is equal to a specific number and column b is equal to another number.
both columns are numbers. Does anybody have a good idea how to do this, maybe countif, but i am not sure how to do more than one item with that.
I am using excel 2002. I want a cell to use conditional formating, whereby say IF CELL A1 CONTAINS "ORDER" THEN A2 WILL TURN A SPECIFIC COLOR.
I can easily figure this out if A1 EQUALS "ORDER", but can't figure out the operator or trick for CONTAINS ...
I've tried =A1="*ORDER*" but it doesn't work . . .
What does this operator do?
View 2 Replies View RelatedI have a quite complex list of variables to put into a formula to come up with the correct number and I need some help to figure this one out. I don't know if it would be easier to set up a chart and then index the chart to do a lookup or how I need to go about doing this to achieve the most efficent solution.
I need the following to go into one formula:
1. =< 24 needs to show the number 24
2. =< 30 but > 24 needs to show 30
3. =< 32 but > 30 needs to show 32
4. =< 36 but > 32 needs to show 36
5. =< 40 but > 36 needs to show 40
6. =< 48 but > 40 needs to show 48
7. =< 50 but > 48 needs to show 50
8. =< 60 but > 50 needs to show 60
9. => 60 needs to show 60
I am trying to set up a spread sheet to figure square footage of plastic laminate countertops, but the plastic laminate only comes in several stock sizes : 3x8, 4x8, 5x8
3x10, 4x10, 5x10
3x12, 4x12, 5x12
So if I have a countertop that is 25 sq ft., I cant use a 3x8 (24 sq. ft), so I need to go up to the next size which would be a 4 x 8.
I might need to use a ROUNDUP function with this formula too? I already have cells set up to figure the depth, backsplash height, and front edge height and then give me the squage footage of that.
Example If Selection.Value *Does not equal* ActiveCell.Offset (0,1) Then .....
View 1 Replies View RelatedI am trying to set a scorecard up where it will give points depending on the % actual. For example:
if less than 60%, then 0,
if equal to or greater than 60% but less than 75%, then 1
if equal to greater than 75% but less than 80%, then 2 and so on.
I have been playing around with the example below, but can't seem to get it working.
[php]=IF(A1<60%,0,IF(A1>A1<75%,1,))[php]
Maybe it 's because I pickled my brains last night.
I've created the following function that chooses the maximum value from a set of cells then inserts theappropriate row number (within a table) into a new cell.
=IF(J27=0,"?",IF(J27=J19,1,IF(J27=J20,2,IF(J27=J21,3,IF(J27=J22,4,IF(J27=J23,5,IF(J27=J24,6,IF(J27=J 25,7,IF(J27=J26,8)))))))))
It's working fine until I have 2 cells with the highest value. The above statement is entering the first cell that meets the criteria in the new cell but ignores the fact there maybe 2 (or more) of the same value.
How can I get both (or all) to be entered in the same cell? Is there a better way, maybe highlighting all the rows in the table that equal the max figure?