PDA

View Full Version : A couple of "easy" questions



ukdane
12-22-2008, 02:24 AM
Hi, I have a couple of easy to solve questions for you guys.

1) What code can I add to "ThisWorkbook" so that no matter where a user inputs data, it will always be capitalised.

2) What VBA do I use to call the "Find" box, and how can I default it?
I've tried
Sub find()
'Find anything, anywhere
Cells.find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
End Sub
but that only initializes the search, and I need the user to input a word to look for.

3) Column L (which is the 12 column) contains a series of dates. I want to create a macro which will automatically filter out anything except the days date +1 (tomorrows date) but the following code doesn't work:
Sub pudatosort()
Dim dagsdato As Date
Dim pudato As String

dagsdato = Date
pudato = dagsdato + 1
Selection.AutoFilter Field:=12, Criteria1:="=" & pudato
End Sub
When I run this, it only shows empty cells. (I've also tried "<", ">", "<=", "=>", and "<>" as the criteria, to obtain different results, but none of them work. Is it something to do with the format of the cells in column L or is it not possible to filter variables?

Benzadeus
12-22-2008, 05:37 AM
1) Try using code:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Cells.Count = 1 Then
Target = UCase(Target)
Else
End Sub

2)
Sub mFind()
'Find anything, anywhere
Dim strSearch As String
strSearch = InputBox("What to search?", "Search", vbYesNo)

Cells.Find(What:=strSearch, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
End Sub

3)Try

Dim dagsdato As Date
Dim pudato As Date

ukdane
12-22-2008, 05:46 AM
Thanks for your reply.

1) works, once I add an End If line.

2) In principal it works, however the user can't then utilise the find next function, which is why I wanted to call the excel find box- and set defaults.

3) Tried that, by and a few other things too, none of them seem to work. Dim dagsdato As Date
Dim pudato As String
Dim mydato As Date

dagsdato = Format(Date, "dd-mm-yyyy")
pudato = dagsdato + 1
mydato = Format(pudato, "dd-mm-yyyy")
doesn't work.
Is there some way I can turn all the dates into numbers, and then compare the numbers instead?

MaximS
12-22-2008, 06:02 AM
try this





Sub pudatosort()


Dim dagsdato As Date


dagsdato = Format(Date + 1, "dd/mm/yyyy")


Selection.AutoFilter Field:=1, Criteria1:=dagsdato

End Sub

ukdane
12-22-2008, 06:22 AM
Maxim S, that works, however I've now decided I need it to include every date after tomorrow. Which presumably would include ">=" but I can't get it to work.
Any ideas?
Sub pudatosort()
Dim dagsdato As Date
dagsdato = Format(Date + 1, "dd-mm-yyyy")
Selection.AutoFilter Field:=12, Criteria1:=">=" & dagsdato
End Sub

Doesn't work

Cheers

mikerickson
12-22-2008, 07:46 AM
For #2 you could use Excel's built in dialog.
Application.Dialogs(xlDialogFormulaFind).Show

Benzadeus
12-22-2008, 09:11 AM
This command of Mike's much better.

Benzadeus
12-22-2008, 09:13 AM
For 2), try this: Dim dagsdato As Date
Dim pudato As Date
Dim mydato As Date

dagsdato = Format(Date, "dd-mm-yyyy")
pudato = DateSerial(Year(dagsdato), Month(dagsdato), Day(dagsdato + 1))
mydato = Format(pudato, "dd-mm-yyyy")

ukdane
12-22-2008, 03:42 PM
Benzadeus: MaximS code works ok for 2) (I think you mean 3. ), the problem is that I need it to be >=, and not just = (for Criteria1)

1) Solved.

2) Solved. using:

Application.Dialogs(xlDialogFormulaFind).Show
(although it can't search the entire workbook, and I'm unsure how to set defaults)

3) see above: Criteria1 needs to be equal to or greater than tomorrow.

mikerickson
12-22-2008, 04:07 PM
The VBEditor help, keyword "built in" (the space is important) should give you a list of Built In Dialog Arguments. Look in the list for Formula Find, it will give you the arguments avaliable for setting defaults.

mikerickson
12-22-2008, 07:49 PM
The field argument is the nth column that has an auto filter, not the nth column of the worksheet.

Sub pudatosort()
Dim dagsdato As Date
Dim pudato As String

dagsdato = Date
pudato = CStr(CDbl(dagsdato))
Selection.AutoFilter Field:=12, Criteria1:=">" & pudato
End Sub