# If Statement Failing When Comparing Text Box Values On Userform

Jun 12, 2014
I am trying to compare to values that a user inputs in a user form. It's ok if they are not the same I just want the user to know it. I am only including the part where the if statement is failing:

VB:

If Me.VSLongQuant.Value <> Me.VSShortQuant.Value Then

MsgBox VerUnBal

If VerUnBal = vbNo Then

Exit Sub

End If

End If

I have VerUnBal as Long and defined as:

VB:

VerUnBal = MsgBox("The Long and Short option Qunatities are not equal." & Chr(13) & _

"Is this intended to be an unbalanced vertical spread?", vbYesNo, _

"Unbalanced Spread Alert")

I can provide more info as necessary. There are two problems 1) the message box is appearing whether the numbers are equal or not, and 2) clicking No in the message box does not cause the Sub to exit.

View 6 Replies
ADVERTISEMENT
Jun 25, 2014

How would I write a IF statement comparing 3 values. For example the field that it would be referencing would have values cell E51(1,2,3,4,5,6,7,...99). The pull back would be:

1-10 = $250,000

11-15 = $550,000

16+ = $750,000

View 2 Replies
View Related
Apr 14, 2013

I have an excel file with some data in it. I have two columns with one of four words in it. For simplicity I'll call them RED, GREEN, BLUE, and Yellow. A few columns over I have columns "Same" "Good" and "Bad". I'm trying to figure out a way to get excel to put a 1 in the appropriate column, if cell A1 is Red I need excel to look at the word in cell B2 and put a 1 in the "same" column if A1 and B1 are the same word. If the word in B2 is Yellow I need excel to put a 1 in the "Good" column. If the word in B1 is GREEN or BLUE I need it to put a 1 in the bad column. The tricky part is each of the four words RED, GREEN, BLUE, and Yellow have a different set of words that will be called "good" or "bad". So if the word in cell A1 if Green for example it will have a different set of good and bad color words.

So it seems I need four different functions depending on what word is in A1. In the column next to these lists of words I have the date which I'll need to use to give me the sums of same, good, and bad for each day. But first things first. I think I need to use a combination of IF functions and MATCH function, but I can't figure out anything that works. Below is what I'm hoping it will look like when I've got the right formulas.

First column

Second column

Date

Same

Good

Bad

[Code]....

View 9 Replies
View Related
May 29, 2009

I am using Excel 2007.

In cells A1 and B1 I have numerical values, e.g. 50 and 12 respectively. Cell C1 calculates the product A1*B1 (= 600). In another cell, say D1, I want to display a statement like “50 x 12 = 600” with the 50, 12, and 600 taken directly from cells A1, B1 and C1, without re-entering them. If I change the 50 to another value, say 70, then I want D1 to read “70 x 12 = 840”

View 2 Replies
View Related
Apr 14, 2014

I'm trying to determine if one date meets the criteria to be considered 'on time'. I have two columns: estimated completion date and completion date. I want to compare the completion date to the estimated completion date and if completion date is <= completion date but is NOT = 1/1/2099 then I met my date. I've tried this several different ways and problem is I cannot make it work.

When estimated completion date is 1/1/2099 'on time' should be 'no'

When estimated completion date is not 1/1/2099 but is greater than completion date 'on time' should be 'no'

When completion date is <= estimated completion date and estimated completion date is not equal to 1/1/2099 'on time' should be 'yes'

And I have a null date to deal with also but if I can satisfy the first 3 criteria I can manipulate the null values manually.

View 9 Replies
View Related
Aug 1, 2013

=IF(M7="","",IF(K8=J8,K8&$J$3&I8,IF(K8=I8,K8&$J$3&H8,IF(K8=H8,K8&$J$3&G8,IF(K8=G8,K8&$J$3&F8,K8&$J$3&J8)))))

I have tried several options, ISERROR(FIND(.................also EXACT(........ and they all seem to get stuck, after the second expression.

Basically, I have a ROW of Cells, with numbers inside them, like so:-

F8.....G8....H8....I8...J8....K8

27.....33....10.....4....4.....4

And looking from right to left, I am asking this question:-

If K8=J8, then I will have K8 & I8 as my result.

If K8=I8, then I will have K8 & H8 as my result.

If K8=H8, then I will have K8 & G8 as my result.

If K8=G8, then I will have K8 & F8 as my final result, Else its K8&J8 as my FALSE.

(the $J$3 = a comma, for number separation, and further LEN string capture.)

Every time I have made this formula, with other types of Logic, it only ever finds the second tier depth as the result,

and I wonder why, since they are several nested IF statements in line!!

The incomplete Excel sheet is attached : S 1_2 tracker Forum.xlsx

The result of the above formula should be 4,10

But instead I get 4,4

View 2 Replies
View Related
Apr 23, 2013

Excel 2010, I have the following list (showing part of it):

Name

T-Shirt Sizes

Nathan

S

[Code]....

The aim of the list is to see what Sizes each and every person needs and then count how many T-Shirts are required for that particular size. The list is over a 100 entries long so counting it manually isn't really an option as it's time consuming as well as errors might arise.

Thus, I've been trying to use the IF statement with the following logic but to as no avail

IF Size in Column is XS

Add 1 to a particular Cell

IF Size in Colum is S

Add 1 to a particular Different Cell

and so on for the other sizes...kind of like a counter for a for-do-loop.

View 2 Replies
View Related
Jun 26, 2008

I am looking for some examples of simple math (Adding, Subtracting, Dividing & Multiplying) using Text box values in a userform.

I have 6 textboxes. 3 of them for data entry and 3 of them for calculating from the other three.

I am not looking to work of any worksheet, it must be done in the user form.

View 9 Replies
View Related
Mar 2, 2014

I need a Macro (not formula) which compares the comma separated values present in Column "I" with individual values present in Column "D" and generate the count of unique values in Column "J".

The sample sheet has been attached for reference.

View 3 Replies
View Related
Sep 28, 2012

Got a road block on one of the trackers which I am trying to create which compares some of our agreements. Basically we need to compare current production with our contracts and then bring the tier level they are at in order to then calculate how much in overrides we receive. Belwo is an example:

Input Value = 140000

Out Should be = 1.5%

Set of Values 1

T1

T2

T3

T4

[Code]...

In a nut shell it will look for 14000 in set of values 1 and pick T3 to then go in set of values 2 and display corresponding T3 which will be 1.5% in above example as output.

View 2 Replies
View Related
Aug 20, 2013

How would I compare two text fields (old and new) and change the font in the 3rd column (Difference)?

For example:

Old: I can not figure 3 things out.

New: I can't figure 2 things out.

Difference: can not can't figure 3 2 things out.

View 1 Replies
View Related
Sep 14, 2008

on a sheet i have 2 column with values.

For e.g

DevID DevNum

123 s123

123 s234

987 sabc

987 sabc

Is there a way where i can compare the values in DevNum with the same DevID

to see if theres any duplicated values using macro?

View 10 Replies
View Related
Feb 1, 2010

I've created a userform with a text box and a command button (Enter)

On clicking the command button I'd like whatever text has been entered into the userform to be put into a cell (Say A1).

View 2 Replies
View Related
Nov 5, 2008

Comparing 2 values.

I need to compare the value's on 2 cells. I have a sheet setup for my sales I would like to compare each day of the week. So in other words I would like to compare this Monday with last Monday and know what the percentage of my sales ether up or down is.

Example:

Last Monday = sales $100.00

This Monday = Sales $200.00

100%

So If sales are up I would like the percentage amount font to be in green. But if sales are down I would like the percentage amount to be in red.

View 2 Replies
View Related
May 28, 2014

I have a football score system and wish to compare the values of various cells - 2 if possible, but more if not possible.

Essentially I have a list of predictions for the score of a game (e.g. 2-1, 1-0, 0-0, 2-2 etc) in a spreadsheet, and I have a cell where I enter the correct scoreline (e.g. 2-1). The way the points system works is that if the prediction is correct (i.e. 2-1) then that player gets 3 points. If the result/outcome is correct but the scoreline is incorrect (i.e. 1-0) then the player gets 1 point. If they result/outcome and scoreline are incorrect, then that player gets 0 points.

Ideally I would link to compare 2 cells - predicted score and actual score, where the format of the scores is entered as 2-1.

However, I feel this may be easier having to columns for the score - Column A = 2 Column B = 1. In this way the scores only need to be compared cell by cell, however they would need to be compared by value for the correct outcomes.

View 5 Replies
View Related
Jul 13, 2013

There are two columns A and B and values there in

A B

1 1

2 2

3 3

4 4

9 5

10 6

11 13

15 15

As you can see there are certain numbers that are missing between column B and A. I want to know which of the numbers that are present in column B are missing in column A ...any handy calculation ?

View 3 Replies
View Related
Dec 13, 2005

I work for a financial firm. Every morning I compare today's cash balance file (sent from our custodian) with yesterday's to see if there is anything different in each portfolio cash balance column. Right now I'm doing it manually checking row by row. It'll become tedious if more and more portfolios are added. I'm looking for an efficient way I can use the portfolio codes column (as a key) to compare with previous day's file’s portfolio codes column , and list only those portfolios(in another workbook) if the corresponding cash balance column doesn't match. This is what I started doing:

Private Sub compare_cash_bttn_Click()

Dim b1 As Workbook, b2 As Workbook, b3 As Workbook

Dim w1 As Worksheet, w2 As Worksheet, w3 As Worksheet

Dim i As Integer

i = 2

Set b1 = Workbooks("CashA")

Set b2 = Workbooks("CashB")

'Set b3 = ActiveWorkbook

Set w1 = b1.Sheets(qry4rCash).........................

View 9 Replies
View Related
May 31, 2007

I want to return the proper value from sheet 2 into column C in sheet one. I have the current formula set up as an array but I only get the value that matches the first row in Sheet 2.

HTML Column A Column B Column C

Date Number Who

3/23/2007 902-555-2596=IF($B2=Sheet2!$A$2:$A$2001,Sheet2!$B$2:$B$2001,)

3/23/2007 980-123-6621=IF($B2=Sheet2!$A$2:$A$2001,Sheet2!$B$2:$B$2001,)

3/23/2007 980-123-6621=IF($B2=Sheet2!$A$2:$A$2001,Sheet2!$B$2:$B$2001,)

3/23/2007 980-555-6621=IF($B2=Sheet2!$A$2:$A$2001,Sheet2!$B$2:$B$2001,)

3/23/2007 980-678-0352=IF($B2=Sheet2!$A$2:$A$2001,Sheet2!$B$2:$B$2001,)

3/23/2007 902-555-7958=IF($B2=Sheet2!$A$2:$A$2001,Sheet2!$B$2:$B$2001,)

3/23/2007 980-555-6621=IF($B2=Sheet2!$A$2:$A$2001,Sheet2!$B$2:$B$2001,)

3/23/2007 980-555-6621=IF($B2=Sheet2!$A$2:$A$2001,Sheet2!$B$2:$B$2001,)

3/23/2007 902-555-7958=IF($B2=Sheet2!$A$2:$A$2001,Sheet2!$B$2:$B$2001,)

3/23/2007 902-555-7958=IF($B2=Sheet2!$A$2:$A$2001,Sheet2!$B$2:$B$2001,)

~~~~~~~~~~~ Sheet 2 ~~~~~~~~~~~~~~~~~

Column A Column B

902-555-7958Big Bird

902-555-5100Daffy Duck

902-678-0352Elmo

902-555-5752Donald Duck

902-555-7420Bert

980-123-6621Ernie

902-555-2596Grover

View 12 Replies
View Related
Apr 27, 2009

I have two columns A and B. I need a count of how many times a value in B is greater than the same row in A. I don't want to insert a new column (e.g. for if statements to add up).

Can I insert a countif at the top to do that? I tried various countif variations but could not find the right one. Additionally any other option that does not involve adding new columns/rows.

View 5 Replies
View Related
Nov 4, 2009

I have a cell (F2) that is auto updated with total sales units for today (Its a simple counter of individual sales). I want to detect when there is a "batch" of sales >= 10. ie. F2 may start at zero for the day and increment by 1,2,5 or 10 units at a time. If the increment is 10 units or more I would like to detect this and store the result in cell G4.

Typical values in C2: 0, 1, 6, 7, 9, 19, 21, 22 etc as new orders arrive. The larger order between 9-19 (viz 10 or more), I need to detect and store in G4. I know that I need to use both the current value of F2 (say 19) and the previous value of F2 (say 9) to perform this, but the solution eludes me.

View 2 Replies
View Related
Feb 19, 2010

I’m attempting to find values that are within a band of .001 of the values of cells in columns L through O and searching columns B through I for values that meet the .001 criteria and copying the values to columns Q, R, S, and T respectively.

I mentioned the values in L, M, N and O are where the comparisons will be made and these values are tagged to a specific date in Column K. I need to find the dates in Column A that are in between the values of K and the cell beneath it. For example, in K4 I have a date of 10/3/99 and the next date I have in K5 is 10/5/99. I would then query Column A for the dates 10/06/99 (A4), 10/07/99 (A5), and 10/08/99 (A6). The values I need to find within the .001 band are in Columns B through I (B4:I6). Lastly, when reach the last value in Column K, query all values in Column A greater than last value in K and copy the appropriate values in B through I to Q through T.

Rules for copying data to Column Q, R, S, and T:

For Column Q-

Find values in B, C, or D that’s within plus or minus .001 of M and copy the value of B, C, or D into Column Q of the same row.

For Column R-.................

View 5 Replies
View Related
Nov 13, 2009

I'll try my best to explain what I'm trying to do here using Excel macro (i've very limited knoledge in VBA ). I am working on a project where I'm using an actuator to measure micro-switches. The values are then extracted using HyperTerminal via RS232 and transferred to Excel to filter the values and display only the ones that are important.

Here is what I'd like to do via a Macro:

Compare cells in column A (e.g A1 and A2, A2 and A3, A3 and A4 etc.)

Example:

A

0

300

550

1200

1700

1900

2200

2000

1850

[Code]....

So first, if the values increase, do nothing and keep comparing other values. If we find the values decrease, display the larger value between the two, in this case it's the value 2200 that we want to display.

Next, the values will be decreasing progressively. We'll do the comparison between 2 values but this time if the values decrease, do nothing until we find a value increases. Display then the smaller value between the 2, here it's 1500.!

View 9 Replies
View Related
Dec 30, 2006

The following formula (thanks to Barrie) determines if the difference between 2 values equals a third value, within a fourth ‘margin of error’ value, and returns either True or False.

=AND(C1-D1

View 9 Replies
View Related
Nov 13, 2009

I'm trying to do here using Excel macro (i've very limited knoledge in VBA ). I am working on a project where I'm using an actuator to measure micro-switches. The values are then extracted using HyperTerminal via RS232 and transferred to Excel to filter the values and display only the ones that are important.

Here is what I'd like to do via a Macro:

Compare cells in column A (e.g A1 and A2, A2 and A3, A3 and A4 etc.)

Example:

A

0

300

550

1200

1700

1900

2200

2000

1850

1750

1600

1500

1950

2500

4000

So first, if the values increase, do nothing and keep comparing other values. If we find the values decrease, display the larger value between the two, in this case it's the value 2200 that we want to display.

Next, the values will be decreasing progressively. We'll do the comparison between 2 values but this time if the values decrease, do nothing until we find a value increases. Display then the smaller value between the 2, here it's 1500.

View 9 Replies
View Related
May 11, 2007

I am aware this question must have been asked to death on this forum and I already searched and found some answers but for some reason I can´t get it to work. I have two worksheets (sheet1 and sheet2). In Column E of Sheet1 I have product descriptions that I want to compare cell by cell to the product descriptions in Column F of Sheet 2. If they are not the same, color in the cell in Sheet 2. There are 1810 rows in both columns. I tried this method but it gives me an error (in spanish) that says that I cannot compare ranges of data.

I have also seen some code to do something similar (http://www.ozgrid.com/forum/showpost...85&postcount=3 ):

Sub find_cell()

Range("A:E"). Find(What:=Cells(2, 6).Value, LookAt:=xlPart).Activate

ar = ActiveCell.Row

ac = ActiveCell.Column

' the two lines above store the row and column values of the cell found

' in your example ar is going to be equal to 229 and ac is 3

' now this following lines are to change the colors to red

With Cells(ar, ac).Interior

.ColorIndex = 3

.Pattern = xlSolid

End With

End Sub

I don´t quite get the "Range("A:E").Find(What:=Cells(2, 6).Value, LookAt:=xlPart).Activate" part though.

View 7 Replies
View Related
Jul 14, 2013

To make a long story short, I'm trying to compare formulas. I was able to copy the formulas as values.

There are hundreds of lines, each one with the formulas pasted as values, like '=SUM(G132:I132) / G30. What I want right now is to compare the text.

So, if for example:

the line for both parts of the pasted sum formula like '=SUM(G132:I132) / G30, I want a number 1 to be written in the adjacent cell (if '=SUM(G132:I132) / G30 is in A1, 1 should be written in B1).

However, if its is written '=SUM(G132:I142) / G30, than nothing should be written in B1.

Moreove, the line could have from 1 to characters. So it could be '=SUM(G1:I1) / G30 or '=SUM(G13200:I13200) / G30.

is the same, like of A1 is '=SUM(G1322), I want a number 1 to be written in B1

View 3 Replies
View Related
Jan 28, 2014

I need to compair 3 columns of text and return a value in a forth colum. example:

If cell D2, E2, & F2 all = â€śFailureâ€ť return â€śPASSâ€ť in cell I2

If cell D2, E2, & F2 all = â€śPassâ€ť return â€śPASSâ€ť in cell I2

If any other combination exist return â€śFailâ€ť in cell I2

View 2 Replies
View Related
Jan 26, 2005

I have a spreadsheet that has long text sentences in each cell. I have a 2nd spreadsheet which is a slightly updated version of the first spreadsheet. The slight updates consisted of editing the odd word here or there out of the first spreadsheet.

Unfortunately, I didn't keep track of the changes I made, and I need to know what they are.

It's easy to tell if a change has been made, simply by comparing the cells. But I can't find an easy way to find out exactly what change was made. Comparing the cells a line at a time is very time consuming.

View 9 Replies
View Related
Dec 3, 2008

I have two text files with huge data in each which I need to compare for difference between each

for e.g.

file 1 would have following records

12345 KKKK 3510 ABCD

file 2 would have

12345 KKKK 3210 ABCD

file 2 would have similar records but 3510 would have difference. What i need is " if 12345 and ABCD is matching then what is the difference value of 3510 and 3210 ? the result should be 300 . This i need as an output in another text file.

View 9 Replies
View Related
Oct 16, 2013

I have this line of code:

Code:

If Left(value.use_type, 1) = "D" Then

Where use_type is a field in a user form.

I am getting an "object required error"

View 2 Replies
View Related