Comparing values of cells in a column with ComboBox value input by user

Question

I am not able to compare the values of a cells in a column with combobox value input.

I have 2 workbooks tests(contains ComboBox2) and test1(contains a column whose cells are compared with ComboBox2.value)

I have a for loop to achieve this.

For i = 1 To LastRow

    If wkbSource.Worksheets(sheet_no).Cells(i, 1) = ComboBox2.Value Then
         'do something
    End If
Next i

I have debugged the code and I understood that if statement doesn't execute even after a match.

How can I fix it ?

EDIT :

Also I would like to know how you can add two cell values because directly adding it is showing incorrect output. For example

wkbSource.Worksheets(sheet_no).Cells(i, 1) + wkbSource.Worksheets(sheet_no).Cells(i, 3)

Before jumping into the logic, have you checked if wkbSource works properly since you are working on different workbooks? — Tehscript, Jun 04 '17 at 15:34
Have your tried to reference ComboBox2.Value to its corresponding workbook? — Tehscript, Jun 04 '17 at 15:37
You might be victim of the *Variant curse*. Are the values you are comparing, actually numbers? If so, how were they added to the combobox? — A.S.H, Jun 04 '17 at 15:50
I don't know whether they are numbers.I guess they are stored as String in ComboBox as well as cells. — user3126632, Jun 04 '17 at 15:58
Beware, Excel will take any numeric value as a number (unless explicitly formatted or preceded by single quote `'`). You might be interested in reading [this] (https://stackoverflow.com/a/44338542/4926357). Would you try this modification to see if we are on the right track: `If wkbSource.Worksheets(sheet_no).Cells(i, 1).Text = ComboBox2.Text Then` — A.S.H, Jun 04 '17 at 16:01
Try comparing texts: `If wkbSource.Worksheets(sheet_no).Cells(i, 1).Text = ComboBox2.Text Then`. Would be even better if you `Trim` them: `If Trim(wkbSource.Worksheets(sheet_no).Cells(i, 1).Text) = Trim(ComboBox2.Text) Then` — A.S.H, Jun 04 '17 at 16:04
You might be activating the other workbook and assume that you are still working on your current workbook, which you actually not. Specify your workbook and worksheet for each object. — Tehscript, Jun 04 '17 at 16:06
@Tehscript I guess, just guess, that the code is within the worksheet's code module otherwise `ComboBox2` would not be found and would raise "object not set" error. But I might be wrong. — A.S.H, Jun 04 '17 at 16:08
@A.S.H If wkbSource.Worksheets(sheet_no).Cells(i, 1).Text = ComboBox2.Text Then Perfect.That worked.I just have one more doubt. How can I add the cell values ? — user3126632, Jun 04 '17 at 16:11
@user3126632 I will post an answer and add that requirement to it. As I am particularly interested in revealing the anomalies due to comparison of `Variant`s. — A.S.H, Jun 04 '17 at 16:14
@A.S.H *Variant curse* nice one! You should post your answer. — Tehscript, Jun 04 '17 at 16:15

A.S.H · Accepted Answer · 2017-06-04T16:50:22.500

This was due (once again) to the Variant Comparison Curse. See in particular the "UPDATE 4" of that question.

If wkbSource.Worksheets(sheet_no).Cells(i, 1) = ComboBox2.Value Then

This compares two Variants. But, when the cell contains a number, and is not explictly formatted as Text, not preceded by ' when entered. Excel will consider it as a number and so it's .Value will be a number Variant. On the other hand, Combobox2.Value retuned a text Variant, so the comparison failed!

When comparing two Variant variables, these operations will fail:

  2 = "2"    ' False
  3 > "2"    ' False

Therefore, the solution in your particular situation is to force comparing texts, using the .Text properties of the control and the cell. Here's how you would - for example - sum up cells that match your query:

For i = 1 To LastRow
  If Trim(wkbSource.Worksheets(sheet_no).Cells(i, 1).Text) =  Trim(ComboBox2.Text) Then
     'do something
      if IsNumeric(wkbSource.Worksheets(sheet_no).Cells(i, 1).Value2) Then _
        mySum = mySum + wkbSource.Worksheets(sheet_no).Cells(i, 1).Value2
  End If
Next i

++ One might spend hours to solve this because it is easy to miss the core problem. — Tehscript, Jun 04 '17 at 16:32
@Tehscript yeah, and it is recently happening frequently as I noticed. — A.S.H, Jun 04 '17 at 16:36

Comparing values of cells in a column with ComboBox value input by user

1 Answers1