CODEWITHSUNDEEP
vbaexcel
user3126632
user3126632

Reputation: 467

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

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)

Upvotes: 3

Views: 1789

Answers (1)

A.S.H
A.S.H

Reputation: 29332

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

Upvotes: 4

Related Questions