Hi Friends,
I am newbie to this forum and VBA. I am developing an application and I need your help. Here is the situation...
1. I have few worksheets
2. I have a Search Form (One Text Box to input the string and Ok Button to search and Cancel Button to cance the search)
3. I have a Target worksheet where the results will be displayed.
Could somebody guide me how can I achive this result.
I have tried without a UserForm and It did worked. That is If I enter the search string in the worksheet(Mysearch) and when I
click on the search button, It worked for me.
Now I wanted to achive the same result when I enter the search string in the Form (Input Text Box).
Please guide me guys.
Here is the code which is working.
[vba]
Private Sub Worksheet1earchButton_Click()
Sheets("MySearch").Activate
nextrow = Application.WorksheetFunction.CountA(Range("A:A")) + 1
'Transfer the name
'Cells(nextrow, 1) = SearchTextBox.text
'If SearchTextBox.text = "" Then
' MsgBox "Please Enter Server Name in the Text Box"
' Exit Sub
'End If
Worksheets("MySearch").Range("C11:G65536").Select
Selection.ClearContents
Worksheets("MySearch").Range("C3").Select
Worksheets("MySearch").Range("C8") = "Seached string contains: " & Worksheets("MySearch").Range("C3").Value
Dim i As Integer
Dim text As String
Dim ServerName As String
Dim AccountName As String
Dim SubAccountNames As String
Dim myCell As Range
Dim myNode As Range
Dim Rng As Range
text = Worksheets("MySearch").Range("C3")
i = 0
For Each myCell In Worksheets("Worksheet1").Range("Worksheet1") 'Look in First worksheet
SubAccountNames = ""
If InStr(1, myCell.text, text, vbTextCompare) > 0 Then 'Look in the string for the text
Worksheets("MySearch").Range("C12").Offset(i, 0) = myCell.text
ServerName = myCell.text
Worksheets("MySearch").Range("C12").Offset(i, 1) = myCell.Offset(0, 1).text
Worksheets("MySearch").Range("C12").Offset(i, 2) = myCell.Offset(0, 2).text
AccountName = myCell.Offset(0, 2).text
Worksheets("MySearch").Range("C12").Offset(i, 3) = myCell.Offset(0, 6).text
For Each myNode In Worksheets("Worksheet2").Range("Worksheet2") ' Look in the second worksheet
If StrComp(myNode.Offset(0, 4).text, ServerName, 1) = 0 Then ' Find node entry containing selected server
If StrComp(AccountName, myNode.Offset(0, 6).text, 1) <> 0 Then
If InStr(1, SubAccountNames, myNode.Offset(0, 6).text, vbTextCompare) = 0 Then
SubAccountNames = SubAccountNames & myNode.Offset(0, 6).text & ", "
End If
End If
End If
Next myNode
Worksheets("MySearch").Range("C12").Offset(i, 4) = SubAccountNames
i = i + 1
End If
Next myCell
End Sub[/vba]
Thanks,
Suresh