Hello folks

I have the following code :

To be used on the following sheet :

The idea :

I have a userform with a text box & a combo box for capturing inputs. The text from these two is concatenated. The result is then compared to the "cell wise" concatenation of columns A & C in order to find a match for a particular combination which a user has entered using the userform.

I hope the idea is somewhat clear, I hope it doesnt sound too convoluted Sorry! Thanks a ton for all the help I'm anticipating

I have the following code :

Code:

```
Private Sub CommandButton1_Click()
Dim strcon As String
Dim LastRow As Long
strcpycb = ComboBox1.Text
strcpytb = TextBox1.Text
strcon = strcpycb & strcpytb
MsgBox (strcon) 'for testing whether concatenation takes place correctly
LastRow = Range("A" & Rows.Count).End(xlUp).Row
MsgBox (LastRow) 'testing again for the last row, to be used in a loop
Dim i As Long
Dim strtxt As String
For i = 1 To LastRow
strtxt = Range("a[i]").Text & Range("c[i]").Text
If (StrComp(strcon, strtxt) = 0) Then
MsgBox ("congrats") 'searching for a particular record if match found
End If
Next i
End Sub
```

Excel Workbook | |||||
---|---|---|---|---|---|

A | B | C | |||

1 | DState | Status | Pname | ||

2 | X2 | Closed | NJ | ||

3 | X1 | Closed | UD | ||

4 | X2 | Closed | INTU | ||

5 | X2 | Closed | TLINE | ||

6 | X2 | Closed | CHE | ||

7 | X3 | Closed | * | ||

8 | X4 | Closed | IBU | ||

9 | X2 | Closed | FN | ||

10 | X1 | Closed | AFG | ||

11 | X1 | Closed | SPE | ||

12 | X2 | Closed | NJ | ||

13 | X3 | Closed | R2R | ||

14 | X2 | Closed | URB | ||

15 | X2 | Closed | HART | ||

Sheet1 |

The idea :

I have a userform with a text box & a combo box for capturing inputs. The text from these two is concatenated. The result is then compared to the "cell wise" concatenation of columns A & C in order to find a match for a particular combination which a user has entered using the userform.

I hope the idea is somewhat clear, I hope it doesnt sound too convoluted Sorry! Thanks a ton for all the help I'm anticipating

Last edited: