Concatenate error and Date Format Error

[Guest]

I posted a question a few days ago but had no answer. Soooo, I tired to
create this macro myself (i am not a programmer nor do i pretend to be one).
The macro runs through but I have the following problems:

1) The filter is not working properly. I am trying to filter for data
between the beginning and ending dates. I am getting no data. Guessing a
formatting problem?
2) My concatenate idea is not working. Simply returning #name?

Here is my code (as I said, I am no expert but just a simple person trying
to make these easier)

Sub WorkOrdersCompletedByDate()

' Print Work Orders Completed For a Specific Date
' Macro recorded 4/18/2006 by Tom Hasenstab
'
' Beginning Date Prompt
Prompt = "Please Enter Beginning Date (mm/dd/yy). Hitting 'Cancel' Will
End the Macro."
Title = "Print Completed Work Orders for a Specific Date Range"
newname1 = InputBox(Prompt, Title, "mm/dd/yy")
' In case user clicks the Cancel button.
If newname1 = "" Then
MsgBox ("Ending Macro. Click 'OK' to Continue")
End
End If
' Ending Date Prompt
Prompt = "Please Enter Ending Date (mm/dd/yy). Hitting 'Cancel' Will
End the Macro."
Title = "Print Completed Work Orders for a Specific Date Range"
newname2 = InputBox(Prompt, Title, "mm/dd/yy")
' In case user clicks the Cancel button.
If newname2 = "" Then
MsgBox ("Ending Macro. Click 'OK' to Continue")
End
End If
' In case ending date is before beginning date.
If newname2 < newname1 Then
MsgBox "Ending Date was Before Beginning Date. Re-Start Macro."
WorkOrdersCompletedByDate
End If
'
Sheets("Maintenance Log").Select
ActiveSheet.Unprotect
Range("B2").Select
' Sort By Completed Date then By Unit Number
Range("B2:M65000").Sort Key1:=Range("M3"), Order1:=xlAscending,
Key2:=Range _
("E3"), Order2:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Offset(0, 0).Select
' Filter
Selection.AutoFilter

' THIS IS PROBLEM #1
Selection.AutoFilter Field:=12, Criteria1:=">=Newname1", Operator:=xlAnd _
, Criteria2:="<=newname2"

' Add Header in Row 2
Rows("2:2").Select
Selection.insert Shift:=xlDown
Rows("2:2").Select
Selection.insert Shift:=xlDown
Range("B2").Select
ActiveCell.FormulaR1C1 = "Completed Work Orders"
Range("B2:M2").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
' Add Header in Row 3
Range("B3").Select

'THIS IS PROBLEM #2'
ActiveCell.FormulaR1C1 = "=CONCATENATE(newname1,""to"",newname2)"

Range("B3:M3").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
' Finish Up
Rows("2:3").Select
Selection.Delete Shift:=xlUp
Range("B2").Select
Selection.AutoFilter
Range("B2").Select
ActiveSheet.Protect
Sheets("Panel").Select
Range("A1").Select
End Sub

 

[Jim Cone]

Question 2.

This uses two methods to add sufficient quotation marks.
Chr$(34) is used for the variables and a double set of quotes is used around
the word "to".
It is just to illustrate, one method is not necessarily any better than the other...

ActiveCell.FormulaR1C1 = "=CONCATENATE(" & Chr$(34) & newName1 & _
Chr$(34) & ",""to""," & Chr$(34) & newName2 & Chr$(34) & ")"
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"Coal Miner"
<[email protected]>
wrote in message
I posted a question a few days ago but had no answer. Soooo, I tired to
create this macro myself (i am not a programmer nor do i pretend to be one).
The macro runs through but I have the following problems:

1) The filter is not working properly. I am trying to filter for data
between the beginning and ending dates. I am getting no data. Guessing a
formatting problem?
2) My concatenate idea is not working. Simply returning #name?

Here is my code (as I said, I am no expert but just a simple person trying
to make these easier)
- trimmed-

'THIS IS PROBLEM #2'
ActiveCell.FormulaR1C1 = "=CONCATENATE(newname1,""to"",newname2)"

 

[Guest]

Jim,

Thank you very much with the concatenate error. All is working well.
Thanks!!

 

[Guest]

Any ideas on the 'date' issue I am having??