build a macro that I can stop

[waterdamage]

Hi, I won’t to build a macro that I can stop and enter a search string to search my table, can anyone help.
I can record most off this but can’t work out how to stop the macro and get it to prompt for the search string

 

[shades]

Use InputBox to display a simple dialog box so that you can enter information to be used in a macro. The dialog box has an OK button and a Cancel button. If you choose the OK button, InputBox returns the value entered in the dialog box. If you click the Cancel button, InputBox returns False. You need to use the following line:

Dim MyText As String
    MyText = Application.InputBox(prompt:="Insert in a text", Title:="This is what I want")

________
Ernesto Brambilla

 

[waterdamage]

Thanks this works however!

I’m trying to get the data from the dial box to change the criteria (at moment “Jim smith” this was recorded)
Tried pasting your info in lots of places but can’t get data in the VB code any ware?


Range("C2").Select
Selection.AutoFilter
ActiveSheet.Range("$A$2:$E$379").AutoFilter Field:=3, Criteria1:= _
"Jim Smith"
Range("C1:E1").Select
Selection.Copy
Sheets("Sheet5").Select
Range("B1212").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Sheet1").Select
Application.CutCopyMode = False
Selection.AutoFilter
End Sub

 

[shades]

Do you want it so someone has to type in the criteria? Then use the InputBox. This will be easiest to set up, but can also lead to errors if the user types in the criteria incorrectly.

Do you want it so the user can select a cell? This eliminates the error problems of the InputBox, but requires that the user scroll to a cell that has the criteria (might involve considerable scrolling).

Do you want it so that the user can select from a list? This means creating a ListBox, and is the most complete, and least error-prone. Takes a little more to set it up, but very effective. Also, will the list change over time? Then you will need a means of changing the list in the ListBox.
________
Rg50

 

[waterdamage]

Option one is Ok the criteria is a job name and will be quite familiar to me by the time i wont to do a search for data.

 

[shades]

Okay. Try this. Your entered text becomes the criteria.

Sub XLTest0402()
    Dim myText As String
    myText = InputBox("Enter text to search")
    ActiveSheet.Range("$A$2:$E$379").AutoFilter Field:=3, Criteria1:=myText
End Sub

________
Hash

 

[waterdamage]

Thanks for this; I’m starting to think that programming in VB is the key to everything. Tried last month to set up a DB, invested about 2 weeks into it, then decided that I didn’t have the knowledge to complete it so put it to one side and went back to my spread sheet. Now I’m trying to get more advanced with this however I’m enjoying every moment of both episodes, I have come to the conclusion that the key is to have knowledge of VB! Now ware do I place this data you have kindly given me? I think I have to give you more info on what I’m doing.
I’m using office 2007
Column 1 header= date Data= long date
Column 2 header = employees name Data = job reference (probably customers name)
Column 3 Header = Time Data = 1 or .5 (this will refer to a working day
Column 4 Header= Materials Data= £00.00 (cost spend on this day by this employee)
If I calculate this above each Colum I have to filter by customer name to work out how much each job has cost. This is fine if I only have one employee but if I have 3 or 4 each employee has to be filtered separately.
My macro was suppose to auto sort each column and past the dater on a new sheet
Question do you think I’m going around this in the correct way?

 

[shades]

Can you provide a sample (dummy data) that you can post, showing how it is before and after? You will do it manually, of course). But then we can have a better view of exactly what you want.
________
CX500

 

[waterdamage]

Thanks for bearing with me I will do it tomorrow and post it up

 

[waterdamage]

OK this is the file; I hope you understand ware I’m trying to get to with it.
I just need to keep track of costs of ongoing jobs, any suggestions please.

 

[waterdamage]

Filtering information

The real problem is when you filter one column for criteria; all columns are closed up to the amount of criteria that has been found in the filtered column.
I think there must be a different way of doing this but I just can’t see it.
Help anybody

 

[shades]

Just a note. I have been on vacation and no access to internet for the past 10 days. I just returned and trying to catch up with work. I will try to look at this later.
________
ROOR BONG PICTURES