PDA

View Full Version : Find a string through textbox and display the result in worksheet



suresh
02-05-2006, 04:58 PM
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.

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

Thanks,
Suresh

Jacob Hilderbrand
02-05-2006, 06:10 PM
text = Worksheets("MySearch").Range("C3")


So you will need to change this to refer to the text box on the user form.


text = Me.TextBox1.Text


Is that what you want to do?

suresh
02-06-2006, 11:16 PM
Hi DRJ,
Thank you somuch for your quick reply.
Yes you are correct. I was looking for the same kind of code. I have tried with your code but getting unknown error.

I have found one more issue with me code:
When enter Search string in the text box and when I selecting the option button, it is immediately starting the search (Without clicking the Search Button). I want to start the search only when I click the search button.
Please send me your advices.

Thanks,
Suresh

Zack Barresse
02-06-2006, 11:35 PM
Hi suresh, welcome to VBAX!! :yes

What is the error you are getting? Which line? What is the number/description?

Rembo
02-08-2006, 06:05 AM
Hello suresh,


..I have tried with your code but getting unknown error.

If you placed this code outside the Userform code page (for example in a Module) you will get an error on the Me object. Try replacing it with the Userform name, for example:

text = UserForm1.TextBox1.Text

Btw, shouldn't Worksheet1earchButton be Worksheet1SearchButton ?


When enter Search string in the text box and when I selecting the option button, it is immediately starting the search (Without clicking the Search Button).

Then the option button initiates the routine that does the searching. Most likely you called the procedure from an event that fires. Check the events of the 'option button'.


I want to start the search only when I click the search button.

If your button is called 'Worksheet1earchButton' then your code should work just fine. But as mentioned earlier, shouldn't that be Worksheet1SearchButton?

As an alternative to your code I created a routine that uses the Find and Findnext method which is faster then looping through all the cells individually.
This routine will loop through all your worksheets (except the worksheet used to store found restuls) and searches for a specified value (string) in them. When found it copies cells A to F from the matching row to a results worksheet.

To use it copy this routine to a Module:

Sub SearchAndStore(sWksResults As String, sWhat2search4 As String, sResultAddress As String)
'This routine loops through all the worksheets in the active workbook,
'searches for a value and copies cells A to F of the row of the found match
'to worksheet sWksResults

'Turn screen updating off for faster processing
Application.ScreenUpdating = False

Dim wks As Worksheet
Dim rFoundResult As Range, rResultCell As Range
Dim sFirstFound As String, sNextFound As String
Set rResultCell = Worksheets(sWksResults).Range(sResultAddress)
For Each wks In ThisWorkbook.Worksheets
If wks.Name <> sWksResults Then
wks.Activate
wks.Range("G1").Select
Set rFoundResult = wks.Cells.Find(What:=sWhat2search4, after:=wks.Range("A1"), LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False)
If Not rFoundResult Is Nothing Then
sFirstFound = rFoundResult.Address
sNextFound = ""

'Copy cells A to F of the row of the found match to worksheet sWksResults
wks.Range("A" & rFoundResult.Row & ":F" & rFoundResult.Row).Copy
rResultCell.PasteSpecial xlPasteValues, xlPasteSpecialOperationNone
Set rResultCell = rResultCell.Offset(1, 0)

'Look for other matches and copy cells A to F of the row
'of the found match to worksheet sWksResults
Do While Not rFoundResult Is Nothing And sNextFound <> sFirstFound
Set rFoundResult = wks.Cells.FindNext(after:=rFoundResult)
If Not rFoundResult Is Nothing Then
sNextFound = rFoundResult.Address
If (sNextFound <> sFirstFound) Then
wks.Range("A" & rFoundResult.Row & ":F" & rFoundResult.Row).Copy
rResultCell.PasteSpecial xlPasteValues, xlPasteSpecialOperationNone
Set rResultCell = rResultCell.Offset(1, 0)
End If
End If
Loop
End If
End If
Next wks

'Undo the last selection for copying
Application.CutCopyMode = False

'Select the starting cell of the results
Worksheets(sWksResults).Activate
Range(sResultAddress).Select

'Turn screen updating back on
Application.ScreenUpdating = True

End Sub

Now let's assume you are looking for the text as entered in TextBox1 on UserForm1, and would like the matches (cells A to F from that row) to be copied to the worksheet 'Sheet1' starting in cell C5.
If your search button on UserForm1 is called CommandButton1 then copy this routine to your Userform code page:

Private Sub CommandButton1_Click()
SearchAndStore "Sheet1", TextBox1.Value, "C5"
Me.Hide
End Sub

If you click the button CommondButton1 it should give you the results you are looking for.

Hope that helps,

Rembo

suresh
02-08-2006, 03:28 PM
Hi Rembo,
I dont know how to say thanks to you for helping in crucial situation.
Yes your code did worked perfectly but it is pulling the complete details of the worksheets when I search for any string. I wanted to pull certain fields(like in my above code) Here is the situation:

For example I have 2 worksheets one has Emp#, EmpName, EMP_DOB, EMP_Salary
and another worksheet has EMP_Exp, EMP_Address, Emp_Sex

Suppose when I search for a string called Rembo, I would like to get the Following Details:
Emp_Name, Emp_Salary, Emp_sex.
How Can I achive the above result with your code. My Code in the above example does this job but the problem in my code is The movement I select the Radio button[I have 3 radio buttons to filter the search], It is starting the search(Before CLICKING the search button).

I have observed one thing from your code and my code:
1. your code execution is faster than mine.

Please give me some advice to achieve the result. Let me know if you need me to send the sammple xlsheets.

Thanks,
Suresh

Rembo
02-09-2006, 02:41 AM
Hello Suresh,

Glad I could help.


..I wanted to pull certain fields(like in my above code) Here is the situation:

For example I have 2 worksheets one has Emp#, EmpName, EMP_DOB, EMP_Salary
and another worksheet has EMP_Exp, EMP_Address, Emp_Sex
.. How Can I achive the above result with your code

Each of the fields correspond with a fixed column right? For example, Emp# is column B, EmpName is column C, EMP_DOB is column D etc.

The Find method sets the variable rFoundResult to the cell with the match. So if it finds 'Rembo' cell C6 then the address of rFoundResult is also C6.
Just for arguments sake, let's say the fields (columns) you want to copy for that row are columns C,D and F. (rFoundCell, rFoundCell.Offset(0,1) and rFoundCell.Offset(0,3))

First add this line to include the variable sRange in the subroutine;

Dim sRange As String

This line has to be placed in front of the following lines.
Now replace this line (two occurrences in the routine!):

wks.Range("A" & rFoundResult.Row & ":F" & rFoundResult.Row).Copy

With these two lines:

sRange = rFoundResult.Address & ", " & rFoundResult.Offset(0, 1).Address & ", " & rFoundResult.Offset(0, 3).Address
wks.Range(sRange).Copy


..problem in my code is The movement I select the Radio button[I have 3 radio buttons to filter the search], It is starting the search(Before CLICKING the search button).

For some reason the search routine is triggered, I suspect there's some rudimentary code somewhere in an event routine. If you upload your workbook I can check that for you. If you want you can strip out any sensitive data, I'll be just looking at the VBA side of things.

Rembo

Zack Barresse
02-09-2006, 10:13 AM
Suresh, please read this: http://www.excelguru.ca/XLKBA/XLKBA05.htm

Cross-posted: http://www.accessvba.com/forum/showthread.php?t=19089

suresh
02-10-2006, 11:24 AM
Hi Rembo,
Thanks somuch for your continuous help. Yes I can send you the spreadsheet. Could you give me your email id?

Thanks,
Suresh.

Rembo
02-11-2006, 08:42 AM
Hello Suresh,


Thanks somuch for your continuous help. Yes I can send you the spreadsheet. Could you give me your email id?

You have to watch out with cross posting because it can result in you being booted from a forum. If you haven't read the link (http://www.excelguru.ca/XLKBA/XLKBA05.htm) firefytr posted you might want to have a look at it to read why there are rules for.

That said I'll be happy to help you with your VBA questions. You can attach a sample workbook to your post here. Just post or reply, scroll down a little and click on the Manage Attachments button. It makes sense to keep it along with the information in this thread so that everybody that wants to can learn from it or perhaps add to it. That way everybody keeping track of this thread can benefit.
You can (and should) strip out sensitive or confidential information. If needed just replace it with fictional data.

Cheers,

Rembo

suresh
02-12-2006, 12:56 PM
Hi Rembo,
Thanks somuch for your advice. I was not aware of how to send the attachments over here. Here I am sending the sample spreadsheet.
Could you please advice on the following:
1. What kind of interface will be best for Search functionality.(I want it to be user friendly)
2. I am in the process of building a db for searching any kind of data in the worksheet and wanted to display the specified fields(what we mention in the code)
3. I am wondering why the search is starting before we click on the search button?? The search is starting as soon as I click on the radio Button (Node Search, server Search).
Please advice me for further development.

Thanks,
Suresh.

Rembo
02-16-2006, 06:27 AM
Hello Suresh,


1. What kind of interface will be best for Search functionality.(I want it to be user friendly)

I would create a userform for this task. Depending on what you want to look for and what restrictions you want to be able to apply the complexity of the userform will vary.


2. I am in the process of building a db for searching any kind of data in the worksheet and wanted to display the specified fields(what we mention in the code)

In your target worksheet right? You can simply use the code I posted earlier and tie it to the userform. To give you an example, please look at the attached workbook sample_2.xls. You can make it as fancy as you want.



3. I am wondering why the search is starting before we click on the search button?? The search is starting as soon as I click on the radio Button (Node Search, server Search).

You stripped all the code from the example, including the userform so I can't examine the problem. If you repost your workbook with code I will have a look at it.

Rembo

suresh
02-17-2006, 09:36 AM
Hi Rembo,
Thanks somuch for your continues help.
I was able to build the GUI from your sample2 spreadsheet. Thanks for your guidelines.
I have resolved the other issue too (Option buttion action). earlier I gave the action loop in wrong place. Now I fixed it. My search Form is working now. Have few things left in the form.
1. Is there any way to create minimize, Maximize buttons in the form (Like any browser Min,Max, Close Icons).
2. By Default when I click on the form, It popsup in the Middle of the computer screen. How can we open that in the leftmost of the screen??
Once again thanks somuch Rembo.

Thanks,
Suresh.

Rembo
02-18-2006, 05:07 AM
Hello Suresh,


1. Is there any way to create minimize, Maximize buttons in the form (Like any browser Min,Max, Close Icons).

You can using an API call. Ivan Moala (http://www.xcelfiles.com/Userform_TaskBar.html) and Colo (http://puremis.net/excel/code/063.shtml) have some example code on their site, you might want to check it out. Here is the gist. Copy this code in the userform codepage to see it work:

Private Declare Function FindWindow _
Lib "user32" _
Alias "FindWindowA" ( _
ByVal lpClassName As String, _
ByVal lpWindowName As String) _
As Long

Private Declare Function GetWindowLong _
Lib "user32" _
Alias "GetWindowLongA" ( _
ByVal hWnd As Long, _
ByVal nIndex As Long) _
As Long

Private Declare Function SetWindowLong _
Lib "user32" _
Alias "SetWindowLongA" ( _
ByVal hWnd As Long, _
ByVal nIndex As Long, _
ByVal dwNewLong As Long) _
As Long

Private Declare Function DrawMenuBar _
Lib "user32" ( _
ByVal hWnd As Long) _
As Long

Private Const GWL_STYLE As Long = (-16)
Private Const WS_SYSMENU As Long = &H80000
Private Const WS_MINIMIZEBOX As Long = &H20000
Private Const WS_MAXIMIZEBOX As Long = &H10000


Private Sub UserForm_Activate()

Dim Frmhdl As Long
Dim lStyle As Long

Frmhdl = FindWindow(vbNullString, Me.Caption)

lStyle = GetWindowLong(Frmhdl, GWL_STYLE)
lStyle = lStyle Or WS_SYSMENU
lStyle = lStyle Or WS_MINIMIZEBOX
lStyle = lStyle Or WS_MAXIMIZEBOX

SetWindowLong Frmhdl, GWL_STYLE, (lStyle)
DrawMenuBar Frmhdl

End Sub



2. By Default when I click on the form, It popsup in the Middle of the computer screen. How can we open that in the leftmost of the screen??

Look under the properties of the form. You want to set the 'Left' and 'Top' property of the form. You can also do it from VBA. Just add these lines to the UserForm_Activate event code:

Me.Left = 100 '<- Change value as needed
Me.Top = 50 '<- Change value as needed

Good luck,

Rembo

suresh
02-20-2006, 09:57 AM
Hi Rembo,
Thanks somuch. Your tid did worked for me. I made change and now the window is opening in the left most corner.

Minimize, close buttons are also working with your sample. But when I minize the form, the spreadsheet also minimizing along with the form. I did not change the code. Should I need to do something to minimize ONLY the form??

Also when try to embed an image icon, I am getting the follroing error:
Member or data not defined. I am getting the error at the following line of code:
hIcon = Image1.Picture.Handle

In the above code, I have imported a gif image.
I think I missing something here??
I appriciate your continues help.

Thanks,
Suresh.

suresh
02-20-2006, 10:12 AM
Hi Rembo,
I forgot one thing..
When I was trying to open another form from Search form, I am getting following error:
Can't Show non-model form when model form is displayed.
How can I elimate this error?

Here is the situation:
Initially We will open a Search form. From there I have created a Button and gave the link to open another form when we click on that button.

Thanks,
Suresh.

Rembo
02-21-2006, 01:28 PM
Hello Suresh,


..when I minize the form, the spreadsheet also minimizing along with the form. I did not change the code. Should I need to do something to minimize ONLY the form??

It seems the focus is moved towards the next window if you press the minimize button on the form. If you minimize all windows except Excel and your form it will work. I don't know how to fix that right now but maybe a little google search can help you out.


I am getting the error at the following line of code:
hIcon = Image1.Picture.Handle

In the above code, I have imported a gif image.
I think I missing something here??

This must be code from Colo his site. To be able to apply an image to a userform, as shown in Colo's code example, you must create an image suitable for usage on a form. The icon you see in the worksheet is in fact an 'Image' (=control element). The picture itself is applied by setting the 'Picture' property of the Image object. See the attached picture for a visual explanation.



When I was trying to open another form from Search form, I am getting following error:
Can't Show non-model form when model form is displayed.
How can I elimate this error?

The problem is that your first form is set to Modal and your second form is set to non-modal. Modal means that when the form is activated it has to be hidden or removed from memory before the rest of the code can continue.
If you set the 'ShowModal' property of your second form to True you problem should be solved.

Rembo

suresh
02-23-2006, 12:00 PM
Hi Rembo,
Thanks somuch for your your help. Your suggestions did worked for me. I was able to resolve the above issues with your help (May if you were not there then I might need to break my head for loong time).

Regarding minimize window as you said focus was moved towards the forum. I was able to solve this wen i set showmodal=false in the search form.

Now I am good to go for the next step.
Now I would like to import few text files in to my worksheet named "UpdateSheet". Here is the sample text files:
File1.txt:
ADCPRDCML1M,WinNT,ADCPRDCML,127.0.0.3,STANDARD,TIME0215
ADCPRDCML2,WinNT,ADCPRDCML2,127.0.0.2,STANDARD,TIME0215
DCCPRDDB1,WinNT,KCCPRDDB1,127.0.1.71,STANDARD,TIME0215

File2.txt:
DCBCPRDCM,AIX,DCBPRDCMT,127.0.0.4,STANDARD,TIME0900
XYZCPRDCML,AIX,ADCPRDCML2,127.0.0.5,STANDARD,TIME0815
TKCPRDDB10,Linux,KCCPRDDB1,127.0.1.6,STANDARD,TIME0700

How could I achive this?? I have tried couple of threads in our group. but failed to get the result. Could you please advice me?

Thanks,
Suresh

suresh
02-23-2006, 04:23 PM
Hi Rembo,
I have found the sample code in our group which is working for a single file. How can I do the same for many text file and display them in a single xlsheet?
Here is the code I have found from
http://www.vbaexpress.com/forum/showthread.php?t=6682&highlight=import+text+file

Option Explicit
Sub ImportTextFile()
Dim AutomateWS As Worksheet
Dim ImpRng As Range
Dim r As Long, c As Long, i As Long
Dim txt As String, char As String
Dim vData As Variant
Set AutomateWS = ActiveWorkbook.Sheets("Automate")
Set ImpRng = AutomateWS.Range("A1")
Open ThisWorkbook.Path & "\bld1seb002.txt" _
For Input As #1
r = 0
c = 0
txt = ""
Application.ScreenUpdating = False
Do While Not EOF(1)
Line Input #1, vData
For i = 1 To Len(vData) + 1
char = Mid(vData, i, 1)
If char = "," Or i > Len(vData) Then
ImpRng.Offset(r, c) = txt
c = c + 1
txt = ""
Else
If char <> Chr(34) Then txt = txt & Mid(vData, i, 1)
End If
Next i
c = 0
r = r + 1
Loop
Close #1
Application.ScreenUpdating = True
End Sub

Please advice me to modify this for a text files in a folder.

Thanks,
Suresh.

suresh
02-25-2006, 08:25 PM
Hi Rembo and team,
Thanks somuch for supporting me in this project. I was able to convert all text files in to one single xlsheet (copied all text files in to one text file and converted in to one big xlsheet).
So Now I have the situation like below:
I have 2 xlsheets, one with old values (like server name, os, platform,client version, server primary, secondary) and the new sheet contains with the updated values.
How can I update the new values?? (Like for example If Row1 values in old sheet is are same as new sheet values, it should not update them. If there are any new values (any column like OS changed,..etc), it needs to update them. Please guide me a macro to update them. Sorry to bug you guys with too many questions in a single thread(This is the whole project that is the reason I wanted to keep in a single thread).
Once again thanks to Rembo for his continues support for my project.
Please let me know if you guys need to see the sample sheets?

Thanks,
Suresh.

XLGibbs
02-25-2006, 08:37 PM
How many columns in the sheets need to be compared?

Having a macro to search one sheet and compare to other sheet is not too tough....

Do you have a sanitized sample of a couple rows of data to assist?

And keeping it one thread is fine...

suresh
02-26-2006, 11:55 PM
Hi Gibbs,
Thanks for your reply. Here I am attaching the sample xlsheets.
Here is the situation:
Sample_Old.xls : This file contains the old values (may be previous month values)
and sample_UpdatedValues.xls contains the new values.
If you looks at Sample_UpdatedValues.xls, we have deleted the NODE5 and NODE6. When I run a macro, it should update in the
sample_old.xls sheet.
And one more thing is we have may be 10-15 column values in Old sheet and we have only 5 columns in Updated xl sheet.
So when we run the macro, It should not change any values for the other columns other than the columns which we specify in th
code. Please guide me to achive this.
Example: If we have any
Note1:Node#, Server#, Account# are reference keys and we are getting these values from Servers, Accounts tables.
Note2: We need to update the values in sample_old.xl sheet for Nodes table only.

Gibbs,
Looks like I am anot able to attach 2 xlwork books at a time. I will attach the other file after this mail. Please bare with me.

Thanks,
Suresh.

suresh
02-26-2006, 11:56 PM
Here is the second attachment.

Thanks,
Suresh

XLGibbs
02-27-2006, 01:53 PM
Okay Suresh. As promised I will take a look for you tonight. I don't imagine it will be anything too overwhelming to sort out for you. Will post back after I get a look and see if there are any ancillary issues to contend with.

suresh
02-28-2006, 03:34 PM
Hi Gibbs, Rembo and Team,
Thanks somuch for all your help. Here I am attaching the complete spreadsheet(I have stripped the confidential information).
I have achieved the search (MySearch) with the help of Rembo and Stephen.
Here is what I am looking for now:
1. MySearch is very slow. Is there any way to speedup search?? (may be by
deleting unwanted code or with any shortcut code ???)
2. I would like to add a progress bar to show the status of my search.
3. Everytime I am pulling the all clients updated information to a text file(with comma delimitted)
Now what I want is when ever I import the information for all clients(Initially all the info willbe stored in UPdateList table), and then
.....it need to compare the info with NODES table(when I run the macro). If there is a new node present in the
updatelist table, it needs to add in nodes table. and If the node is not
available in nodes table, that node info need to be deleted from nodes table.
Here is few example scenarios:
example1:
I have the below new information in updatelist table
NODE7_SERVR5 127.0.0.1 node7_server5.
When I ran the macro, the above information need to updated in nodes table.
Example2:
In the Nodes table I have the below information:
NODE1_SERVER1 NODE1_SERVER1 127.0.0.1 1 SERVER1
In my updateList table I have the below i nformation
NODE1_SERVER1 NODE1_SERVER1 127.0.0.2 1 SERVER3
When I ran he macro, the IP and the server name fields(cells) need to updated.

Thanks,
Suresh.