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:

DiscoPistol

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

Forum statistics

Threads
1,082,115
Messages
5,363,247
Members
400,722
Latest member
DrewPop24

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top