how to program this?

[sam]

I have a sheet with 3 columns: A, B, C
A is a dropdown with following values
compile, edit, audit

B is a dropdown and has the same values as A
compile, edit, audit

C is a text box that auto populates with either "Yes" or "No" depending on
what we select for A and B

If I select same values for A and B then I want a Yes for C
If I select different values for A and B then I want a No

Also, I have seen an IF statement in a cell that has text values.

The IF statement compares two cells with text values but has a '>' and a '<'
comparison made. For eg, If 'lion' < 'cat' then display '1' and so on.
My question is, How can we make such a comparison of > or < with a text value?

Thanks in Advance.

 

[Jacob Skaria]

Use String Compare StrComp()

Msgbox strcomp("a","z",vbBinarycompare)

returns 0 if equal
returns 1 if the first string is great
returns -1 if the first string is less

If this post helps click Yes

 

[sam]

Thanks a lot for your help Jacob, I am new to excel programming and am not
able to get it to work.
Can you show me how I can Compare the text values in A and B and populate C
accordingly?

What I am thinking should be done is:
Compare the text strings in A and B, It will return a value -1,0,1
Then write an If statement to populate the value in C.

But, How does excel compare the text values in A and B?
For eg, I want Discrepancy to be populated in C if audit is selected in B
and edit is selected in A. How will excel know this? I cannot manualy write
down what text value preeceds what as there are a LOT of values.

I hope I made it clear.

Thanks in Advance

 

[future_vba_expert]

Sam,

For your first question, I would just put something like this in col C:

=(IF(A1=B1,"yes","no"))

You can copy and paste into as many cells as you need in col C.

As for your second question, the '>' and '<' use the alphabetical order to
determine what is greater or less than for text. So 'aa' is less than 'ab',
just like in the phone book. The formula in C1 would look like this:

=(IF(A1<B1,"1"," "))

ps. In your example, 'lion' is not less than 'cat', so cell C1 would display
spaces.



=

 

[sam]

Thanks a lot for your help,

In my case I have two columns A and B as dropdown menus and both have same
values as follows:
edit, audit, compile.

Now, If B has 'audit' as its value and A has 'edit' as its value I want to
populate C with 'Descripancy', But if B is audit and A is also audit then C
has to be blank.
Also if B is edit and A is audit leave C as blank.

The values in A and B i gave above are just a few values from the list that
I have, But this is the way how it works. A and B have criterias listed in a
drop down menu and selecting, certain value for A and B would display
'Discrepancy' in C.

I hope it is clear now.

Thanks in Advance

 

[future_vba_expert]

Are you saying anytime the values in A and B are different you want C to say
"discrepancy"? That is very simple. However, that doesn't seem to be the case
in your second example. It may be that you will have to define every case
individually, if there is no alphabetic heirarchy, or other simple rules for
comparing A and B. That would probably require a macro, as it would be too
complicated to put in the formula box. Are you familiar with macros?

 

[keiji kounoike]

Do you mean 'Discripancy' shows in C only if the string in B sorts ahead
of the string in A? if so and all is in UserForm, Does this one works?

Private Sub ComboBox1_Enter()
ComboBox1.List = Array("audit", "edit", "compile")
End Sub

Private Sub ComboBox1_Change()
If ComboBox1.Value > ComboBox2.Value Then
Me.TextBox1.Text = "Discrepancy"
Else
Me.TextBox1.Text = ""
End If
End Sub

Private Sub ComboBox2_Enter()
ComboBox2.List = Array("audit", "edit", "compile")
End Sub

Private Sub ComboBox2_Change()
If ComboBox1.Value > ComboBox2.Value Then
TextBox1.Text = "Discrepancy"
Else
TextBox1.Text = ""
End If
End Sub

Keiji

 

[sam]

Thanks for the help. I dont want C to say Discrepancy every time the values
are different. There are certain combinations that will show discrepancy.

For Eg:
Lets say Values in A and B are: One, Two, Three

So if we select One for A and Two for B then C is blank
if we select Two for A and Three for B then also C is blank
But iif we select Two for A and One for B then C is populated with
Discrepancy.

Certain values in A and B dropdown have priority over other, If a better
value is selected for A then C is left blank, But if B has better value then
C is populated with Discrepancy.

How should I distinguish between such text strings? as there is no
alphabetical heirarchy. Also, I do know what macros are, but have never
written one.

Thanks a Lot in Advance

 

[sam]

Thanks for your help keiji, Yes i think you are rite, if string in B has a
higher importance then discrepancy will be populated in C.
I dont want C to say Discrepancy every time the values are different. There
are certain combinations that will show discrepancy.

For Eg:
Lets say Values in A and B are: One, Two, Three

So if we select One for A and Two for B, then C is blank
if we select Two for A and Three for B, then also C is blank
But iif we select Two for A and One for B then C is populated with
Discrepancy.

Certain values in A and B dropdown have priority over other, If a better
value is selected for A then C is left blank, But if B has better value then
C is populated with Discrepancy.

Thanks a Lot in Advance

 

[future_vba_expert]

Sam,
If you are using drop down boxes, I think Keiji is on the right track,
except that what needs to be compared is the "linecount", not the value. If
you put the items that you want to compare into your dropdown boxes, in the
order of their "importance", then the following code would show a discrepancy
when the linecount (order of importance) of the first item is higher than
that of the second item. If you use "value" instead of "linecount", you are
just comparing the values alphabetically, which you said you don't want.

Private Sub ComboBox1_Enter()
ComboBox1.List = Array("audit", "edit", "compile")
End Sub

Private Sub ComboBox1_Change()
If ComboBox1.LineCount > ComboBox2.LineCount Then
Me.TextBox1.Text = "Discrepancy"
Else
Me.TextBox1.Text = ""
End If
End Sub

Private Sub ComboBox2_Enter()
ComboBox2.List = Array("audit", "edit", "compile")
End Sub

Private Sub ComboBox2_Change()
If ComboBox1.LineCount > ComboBox2.LineCount Then
TextBox1.Text = "Discrepancy"
Else
TextBox1.Text = ""
End If
End Sub

 

[future_vba_expert]

I have to make a correction! Although the below code seemed to work fine when
I first tested it, I am now getting "focus" errors when I run it. Upon
further experimenting, I find that it works OK if I change the ".linecount"
to ".listindex".

 

[keiji kounoike]

Copy every line below into your UserForm module and look whether it work
or not. CBlist = Array("One", "Two", "Three") is a list ordered by priority.


Dim CBlist

Private Sub UserForm_Initialize()
CBlist = Array("One", "Two", "Three") '<<==Change to your data
ComboBox1.List = CBlist
ComboBox2.List = CBlist
End Sub

Private Sub ComboBox1_Change()
If MyStrCmp(ComboBox1.Value, ComboBox2.Value) > 0 Then
TextBox1.Text = "Discrepancy"
Else
TextBox1.Text = ""
End If
End Sub

Private Sub ComboBox2_Change()
If MyStrCmp(ComboBox1.Value, ComboBox2.Value) > 0 Then
TextBox1.Text = "Discrepancy"
Else
TextBox1.Text = ""
End If
End Sub

Function MyStrCmp(ByVal Fstr As String, ByVal Nstr As String) As Integer
Dim F1, N1
On Error GoTo ex:
If Fstr <> "" And Nstr <> "" Then
F1 = Application.Match(Fstr, CBlist, 0)
N1 = Application.Match(Nstr, CBlist, 0)
If N1 < F1 Then
MyStrCmp = 1
ElseIf N1 = F1 Then
MyStrCmp = 0
Else
MyStrCmp = -1
End If
Else
MyStrCmp = -2
End If
Exit Function
ex:
MyStrCmp = -3
End Function

Keiji