Search & Concatenate using Command Button

[karma]

Hello folks

I have the following 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

To be used on the following sheet :

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

 

[DiscoPistol]

I'm not really sure what you're after but this seems to work (I've used 2 text boxes but you get the idea)

Private Sub CommandButton1_Click()

Dim txtCon As String, rng As Range

Set rng = Range("C2:C15") 'This is the column of the X values
txtCon = TextBox1.Value & TextBox2.Value

For Each Value In rng
rngcon = Value.Value & Value.Offset(0, 2).Value
If rngcon = txtCon Then
MsgBox ("You have a Match!")
Exit Sub
End If
 
Next Value

End Sub

Actually I'm not sure thats what you were after.........I should take more time reading things!

 

[karma]

Hi DiscoPistol thanks a ton for responding!

I tried to make that work but nothing seems to happen. Not sure what is happening, though your code seems to be logically sound.

Any ideas?

One more thing, the range selected "C2:C15" will always be expand as more data comes in. Thats why I thought of using the "Last row" method.

 

[DiscoPistol]

Hi DiscoPistol thanks a ton for responding!

I tried to make that work but nothing seems to happen. Not sure what is happening, though your code seems to be logically sound.

Any ideas?

One more thing, the range selected "C2:C15" will always be expand as more data comes in. Thats why I thought of using the "Last row" method.

Not sure what the problem is to be honest. Are you getting values for rngcon and txtcon? That would be where I would start looking.

 

[karma]

Hey, I've somewhat succeeded in getting the code to search & match the records. I'll post that code soon, as I am stuck at a point where I need to copy/display the entire record in a text box or something in the same userform. Here goes the snippet :

For i = 2 To LastRow
        
    strcel = Cells(i, 1).Value & Cells(i, 3).Value
    MsgBox (strcel)
       If (StrComp(strcel, strcon) = 0) Then
         Cells(i, 1).EntireRow.Copy Destination:=Cells(2, 7).EntireRow
         Cells(2, 7).Value = TextBox2.Value
         Exit For
         Else: MsgBox ("Try Again")
       End If
Next i

What could be wrong with this.. ? I know the first line of code in red is a mess.. & it doesnt work. Help!!