Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Concatenate error and Date Format Error
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Concatenate error and Date Format Error
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" 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)" |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Concatenate error and Date Format Error
Jim,
Thank you very much with the concatenate error. All is working well. Thanks!! "Jim Cone" wrote: 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" 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)" |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Concatenate error and Date Format Error
Any ideas on the 'date' issue I am having??
"Jim Cone" wrote: 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" 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)" |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
date format error in cell | Excel Worksheet Functions | |||
Date format error | Excel Discussion (Misc queries) | |||
Date-error Format | Setting up and Configuration of Excel | |||
Date format error in Excel | Excel Worksheet Functions | |||
Date Format - April error | Excel Discussion (Misc queries) |