Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old April 21st 06, 07:05 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 21
Default 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   Report Post  
Old April 24th 06, 02:21 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 3,290
Default 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   Report Post  
Old April 24th 06, 04:05 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 21
Default 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   Report Post  
Old April 26th 06, 10:30 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 21
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
date format error in cell pat67 Excel Worksheet Functions 1 March 23rd 10 03:14 PM
Date format error mahesh Excel Discussion (Misc queries) 1 October 22nd 08 08:26 AM
Date-error Format Mia Setting up and Configuration of Excel 7 July 1st 08 07:10 AM
Date format error in Excel Richy Rich Excel Worksheet Functions 6 February 2nd 07 10:36 PM
Date Format - April error Zane Lassiter Excel Discussion (Misc queries) 1 February 1st 06 02:45 PM


All times are GMT +1. The time now is 09:56 PM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017