CODEWITHSUNDEEP
excelvba
Salman Khan
Salman Khan

Reputation: 77

Code to Match Value of Colum 1 against Column 2

I have a small userform with one combobox, textbox and command button whose image is attached.enter image description here

There is one table on sheet 1 whose image is attached below. enter image description here

What I want is when I press the command button in the userform, it has to check each time a value in column 1 against value in column 2.

For example, if column 1 has value "Non Current Assets" and against it column 2 has value "PPE", then msgbox will appear "Value Exist".

The code has to check each time the value1 of column1 against value1 of column2 and if its not there then msgbox will appear "Value not Exist".

Here is my code:

Blockquote

Private Sub CommandButton1_Click()
'Declaring the Variables
Dim ws As Worksheet, tbl As ListObject, row As ListRow
Dim value1 As String, value2 As String
Dim rng1 As Range, rng2 As Range

Set ws = Sheets("Sheet1")
Set tbl = ws.ListObjects("Table1")

ws.Unprotect Password:="google"

Set rng1 = tbl.ListColumns(1).DataBodyRange
Set rng2 = tbl.ListColumns(2).DataBodyRange

value1 = LCase(Me.ComboBox1.Value)
value2 = LCase(Me.TextBox1.Value)

'Loop to Check for the Group Head Existence
If Not rng1 Is Nothing Then
For Each rng1 In rng1
    If LCase(rng1.Value) = value1 Then
        For Each rng2 In rng2
            If LCase(rng2.Value) = value2 Then
            MsgBox ("Value Exist.")
            Exit Sub
        Else
            MsgBox ("Value not Exist")
            Unload Me
            Exit Sub
            End If
    Next rng2
End If
Next rng1
End If
End Sub

The code is not checking the value1 of column1 against value1 in column2.

Kindly advise.

Upvotes: 0

Views: 57

Answers (2)

D. O.
D. O.

Reputation: 626

You cannot do 

For Each rng1 In rng1

instead, you have to loop throw cells in rng1, like

dim cel as range

for each cel in rng1

Same thing for 

For Each rng2 In rng2

Try to modify and see what it will give you

Upvotes: 0

RAJA THEVAR
RAJA THEVAR

Reputation: 421

Use the following code within If Not rng1 Is Nothing Then statement

For Each cell In rng1
        If LCase(cell.Value) = value1 and LCase(cell.offset(0,1).Value)=value2 Then
                MsgBox ("Value Exist.")
                Exit Sub
        Else
                MsgBox ("Value not Exist")
                Unload Me
                Exit Sub
        End If
    Next

Upvotes: 2

Related Questions