Search & Concatenate using Command Button

karma

New Member
Joined
Nov 29, 2008
Messages
24
Hello folks :)

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
To be used on the following sheet :

Excel Workbook
ABC
1DStateStatusPname
2X2ClosedNJ
3X1ClosedUD
4X2ClosedINTU
5X2ClosedTLINE
6X2ClosedCHE
7X3Closed*
8X4ClosedIBU
9X2ClosedFN
10X1ClosedAFG
11X1ClosedSPE
12X2ClosedNJ
13X3ClosedR2R
14X2ClosedURB
15X2ClosedHART
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 :biggrin: Sorry! Thanks a ton for all the help I'm anticipating :)
 
Last edited:

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

DiscoPistol

Active Member
Joined
Jun 6, 2006
Messages
261
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)

Code:
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!
 
Last edited:

karma

New Member
Joined
Nov 29, 2008
Messages
24
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

Active Member
Joined
Jun 6, 2006
Messages
261
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

New Member
Joined
Nov 29, 2008
Messages
24
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 :

Rich (BB code):
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!! :confused:
 

Watch MrExcel Video

Forum statistics

Threads
1,102,554
Messages
5,487,527
Members
407,604
Latest member
sama9000

This Week's Hot Topics

Top