Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Macro not returning correct data
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 10
Default Macro not returning correct data
Hi This Macro

Manipluates the data from a csv file then saves itself in a location ready
to be imported into SQL. The finished table looks like this - note that the
DATE column has returned #N/A. When this was recorded it returned a DATE.
Can anyone help or suggest if I could go about this task in an easier
fashion?

DATE TIME INBOUND AVG INBOUND TIME ABAND AVG ABAND TIME AVG TALK TIME
TOTAL INTERNAL AVG STAFF % IN SERV LEVEL
#N/A 08:00 11 00:05 0 00:00 01:07 258:52:00 7 100
#N/A 09:00 76 00:07 0 00:00 01:13 367:35:00 11 82
#N/A 10:00 61 00:07 2 00:03 01:18 383:06:00 11.8 76
#N/A 11:00 91 00:07 1 00:05 01:35 337:08:00 11 85
#N/A 12:00 70 00:06 1 00:09 01:56 337:54:00 11.1 87
#N/A 13:00 44 00:10 1 00:23 01:40 539:20:00 12 67
#N/A 14:00 85 00:08 1 00:10 01:42 413:56:00 12 71
#N/A 15:00 66 00:07 3 00:06 01:46 360:15:00 11 80
#N/A 16:00 61 00:07 0 00:00 01:50 397:56:00 10.7 84
#N/A 17:00 8 00:07 0 00:00 02:15 144:50:00 3.7 75
#N/A 18:00 0 00:00 0 00:00 00:00 00:00 0
#N/A 19:00 0 00:00 0 00:00 00:00 00:00 0
#N/A 20:00 0 00:00 0 00:00 00:00 00:00 0
#N/A 21:00 0 00:00 0 00:00 00:00 00:00 0
#N/A 22:00 0 00:00 0 00:00 00:00 00:00 0




Sub bcms()
'
'
'

Set NewSht = ThisWorkbook.ActiveSheet
ChDir "Y:\"
Set CSVFile =
Workbooks.Open(Filename:="Y:\report_list_bcms_skil l_1_.csv")
CSVFile.ActiveSheet.Range("A1:U20").Copy _
Destination:=NewSht.Range("A1")
Application.CutCopyMode = False
CSVFile.Close
Cells.Select
Cells.EntireColumn.AutoFit
Columns("A:B").Select
Range("B1").Activate
Selection.Delete Shift:=xlToLeft
Columns("B:G").Select
Selection.Delete Shift:=xlToLeft
Columns("C:E").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Rows("19:20").Select
Selection.Delete Shift:=xlUp
Range("B4:B18").TextToColumns Destination:=Range("B4"), _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=True, _
Semicolon:=False, _
Comma:=False, _
Space:=False, _
Other:=True, _
OtherChar:="-", _
FieldInfo:=Array(Array(1, 1), Array(2,
1)), _
TrailingMinusNumbers:=True
Columns("C:C").Select
Selection.Delete Shift:=xlToLeft
Columns("C:D").Select
Selection.Delete Shift:=xlToLeft
Rows("1:3").Select
Range("A3").Activate
Selection.Delete Shift:=xlUp
Rows("1:1").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("B:G").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.ColumnWidth = 10
Range("A2:A16").Select
Selection.Replace What:=",", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.TextToColumns Destination:=Range("B4"), _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, _
OtherChar _
:="-", FieldInfo:=Array(Array(1, 1), Array(2, 1)), _
TrailingMinusNumbers:=True
Columns("A:C").Select
Range("C1").Activate
Selection.Delete Shift:=xlToLeft
Range("A2").Select
Sheets("Sheet2").Select
ActiveCell.FormulaR1C1 = "JAN"
Range("A3").Select
ActiveCell.FormulaR1C1 = "FEB"
Range("A3").Select
Selection.ClearContents
Range("A2").Select
ActiveCell.FormulaR1C1 = "FEB"
Range("A3").Select
ActiveCell.FormulaR1C1 = "MAR"
Range("A1:A3").Select
Selection.AutoFill Destination:=Range("A1:A12"), Type:=xlFillDefault
Range("A1:A12").Select
Range("B1").Select
ActiveCell.FormulaR1C1 = "1"
Range("B2").Select
ActiveCell.FormulaR1C1 = "2"
Range("B3").Select
ActiveCell.FormulaR1C1 = "3"
Range("B1:B3").Select
Selection.AutoFill Destination:=Range("B1:B12"), Type:=xlFillDefault
Range("B1:B12").Select
Sheets("Sheet1").Select
Columns("A:C").Select
Range("C1").Activate
Selection.ColumnWidth = 8
Range("D2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-3],Sheet2!C[-3]:C[-2],2,FALSE)"
Selection.AutoFill Destination:=Range("D2:D16"), Type:=xlFillDefault
Range("D2:D16").Select
Columns("D:D").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Columns("A:A").Select
Selection.Delete Shift:=xlToLeft
Columns("D:D").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("D2").Select
ActiveCell.FormulaR1C1 = "=DATE(RC[-2],RC[-1],RC[-3])"
Range("D2").Select
Selection.AutoFill Destination:=Range("D2:D16")
Range("D2:D16").Select
Columns("D:D").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Columns("A:C").Select
Range("C1").Activate
Selection.Delete Shift:=xlToLeft
Columns("A:M").Select
Selection.ColumnWidth = 12.86
Range("A1").Select
ActiveCell.FormulaR1C1 = "DATE"
Range("B1").Select
ActiveCell.FormulaR1C1 = "TIME"
Range("C1").Select
ActiveCell.FormulaR1C1 = "INBOUND"
Range("D1").Select
ActiveCell.FormulaR1C1 = "AVG INBOUND TIME"
Range("E1").Select
ActiveCell.FormulaR1C1 = "ABAND"
Range("F1").Select
ActiveCell.FormulaR1C1 = "AVG ABAND TIME"
Range("G1").Select
ActiveCell.FormulaR1C1 = "AVG TALK TIME"
Columns("H:J").Select
Selection.Delete Shift:=xlToLeft
Range("H1").Select
ActiveCell.FormulaR1C1 = "TOTAL INTERNAL"
Range("I1").Select
ActiveCell.FormulaR1C1 = "AVG STAFF"
Range("J1").Select
ActiveCell.FormulaR1C1 = "% IN SERV LEVEL"
Cells.Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Cells.EntireColumn.AutoFit
Range("A2").Select
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:="Y:\bcms_skill1.xlsm", FileFormat:= _
xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
Application.DisplayAlerts = True
End Sub





Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default Macro not returning correct data
Most of your code is formating which cannot be eliminate or made easier. You
don't need to use the select method liike in your original code. The
Selection method takes longer to run and makes the code harder to understand
and longer to write. I made changes the way I would write the code. Compare
the differences.


Sub bcms()
'
'
'

Set NewSht = ThisWorkbook.ActiveSheet
ChDir "Y:\"
Set CSVFile = Workbooks.Open( _
Filename:="Y:\report_list_bcms_skill_1_.csv")
CSVFile.ActiveSheet.Range("A1:U20").Copy _
Destination:=NewSht.Range("A1")
Application.CutCopyMode = False
CSVFile.Close
With Sheets("Sheet1")
.Cells.EntireColumn.AutoFit
.Columns("A:B").Delete
.Columns("B:G").Delete
.Columns("C:E").Select.Insert CopyOrigin:=xlFormatFromLeftOrAbove
.Rows("19:20").Delete Shift:=xlUp
.Range("B4:B18").TextToColumns Destination:=Range("B4"), _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=True, _
Semicolon:=False, _
Comma:=False, _
Space:=False, _
Other:=True, _
OtherChar:="-", _
FieldInfo:=Array(Array(1, 1), Array(2, 1)), _
TrailingMinusNumbers:=True

.Columns("C:C").Delete
.Columns("C:D").Delete
.Rows("1:3").Delete
.Rows("1:1").Insert CopyOrigin:=xlFormatFromLeftOrAbove
.Columns("B:G").Insert CopyOrigin:=xlFormatFromLeftOrAbove
.Columns("B:G").ColumnWidth = 10
.Range("A2:A16").Replace What:=",", _
Replacement:="", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False

.Range("A2:A16").TextToColumns _
Destination:=Range("B4"), _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=True, _
Semicolon:=False, _
Comma:=False, _
Space:=False, _
Other:=True, _
OtherChar:="-", _
FieldInfo:=Array(Array(1, 1), _
Array(2, 1)), _
TrailingMinusNumbers:=True

.Columns("A:C").Delete
End With

With Sheets("Sheet2")
.Range("A1") = "JAN"
.Range("A2") = "FEB"
.Range("A3") = "MAR"
.Range("A1:A3").AutoFill _
Destination:=.Range("A1:A12"), _
Type:=xlFillDefault

.Range("B1") = "1"
.Range("B2") = "2"
.Range("B3") = "3"
.Range("B1:B3").AutoFill _
Destination:=.Range("B1:B12"), _
Type:=xlFillDefault
End With

With Sheets("Sheet1")
.Columns("A:C").ColumnWidth = 8
Range("D2").FormulaR1C1 = "=VLOOKUP(RC[-3],Sheet2!C[-3]:C[-2],2,FALSE)"
.Range("D2").Copy _
Destination:=.Range("D2:D16")

.Columns("D:D").Copy
.Columns("D:D").PasteSpecial _
Paste:=xlPasteValues

.Columns("A:A").Delete
.Columns("D:D").Insert
.Range("D2").FormulaR1C1 = "=DATE(RC[-2],RC[-1],RC[-3])"
.Range("D2").Copy _
Destination:=.Range("D2:D16")
.Columns("D:D").Copy
.Columns("D:D").PasteSpecial _
Paste:=xlPasteValues

.Columns("A:C").Delete
.Columns("A:M").ColumnWidth = 12.86
.Range("A1") = "DATE"
.Range("B1") = "TIME"
.Range("C1") = "INBOUND"
.Range("D1") = "AVG INBOUND TIME"
.Range("E1") = "ABAND"
.Range("F1") = "AVG ABAND TIME"
.Range("G1") = "AVG TALK TIME"

.Columns("H:J").Delete
.Range("H1") = "TOTAL INTERNAL"
.Range("I1") = "AVG STAFF"
Range("J1") = "% IN SERV LEVEL"

With Cells
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
.EntireColumn.AutoFit
End With
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs _
Filename:="Y:\bcms_skill1.xlsm", _
FileFormat:=xlOpenXMLWorkbookMacroEnabled
Application.DisplayAlerts = True
End With
End Sub

"Jon Dibble" wrote:

Hi This Macro

Manipluates the data from a csv file then saves itself in a location ready
to be imported into SQL. The finished table looks like this - note that the
DATE column has returned #N/A. When this was recorded it returned a DATE.
Can anyone help or suggest if I could go about this task in an easier
fashion?

DATE TIME INBOUND AVG INBOUND TIME ABAND AVG ABAND TIME AVG TALK TIME
TOTAL INTERNAL AVG STAFF % IN SERV LEVEL
#N/A 08:00 11 00:05 0 00:00 01:07 258:52:00 7 100
#N/A 09:00 76 00:07 0 00:00 01:13 367:35:00 11 82
#N/A 10:00 61 00:07 2 00:03 01:18 383:06:00 11.8 76
#N/A 11:00 91 00:07 1 00:05 01:35 337:08:00 11 85
#N/A 12:00 70 00:06 1 00:09 01:56 337:54:00 11.1 87
#N/A 13:00 44 00:10 1 00:23 01:40 539:20:00 12 67
#N/A 14:00 85 00:08 1 00:10 01:42 413:56:00 12 71
#N/A 15:00 66 00:07 3 00:06 01:46 360:15:00 11 80
#N/A 16:00 61 00:07 0 00:00 01:50 397:56:00 10.7 84
#N/A 17:00 8 00:07 0 00:00 02:15 144:50:00 3.7 75
#N/A 18:00 0 00:00 0 00:00 00:00 00:00 0
#N/A 19:00 0 00:00 0 00:00 00:00 00:00 0
#N/A 20:00 0 00:00 0 00:00 00:00 00:00 0
#N/A 21:00 0 00:00 0 00:00 00:00 00:00 0
#N/A 22:00 0 00:00 0 00:00 00:00 00:00 0




Sub bcms()
'
'
'

Set NewSht = ThisWorkbook.ActiveSheet
ChDir "Y:\"
Set CSVFile =
Workbooks.Open(Filename:="Y:\report_list_bcms_skil l_1_.csv")
CSVFile.ActiveSheet.Range("A1:U20").Copy _
Destination:=NewSht.Range("A1")
Application.CutCopyMode = False
CSVFile.Close
Cells.Select
Cells.EntireColumn.AutoFit
Columns("A:B").Select
Range("B1").Activate
Selection.Delete Shift:=xlToLeft
Columns("B:G").Select
Selection.Delete Shift:=xlToLeft
Columns("C:E").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Rows("19:20").Select
Selection.Delete Shift:=xlUp
Range("B4:B18").TextToColumns Destination:=Range("B4"), _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=True, _
Semicolon:=False, _
Comma:=False, _
Space:=False, _
Other:=True, _
OtherChar:="-", _
FieldInfo:=Array(Array(1, 1), Array(2,
1)), _
TrailingMinusNumbers:=True
Columns("C:C").Select
Selection.Delete Shift:=xlToLeft
Columns("C:D").Select
Selection.Delete Shift:=xlToLeft
Rows("1:3").Select
Range("A3").Activate
Selection.Delete Shift:=xlUp
Rows("1:1").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("B:G").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.ColumnWidth = 10
Range("A2:A16").Select
Selection.Replace What:=",", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.TextToColumns Destination:=Range("B4"), _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, _
OtherChar _
:="-", FieldInfo:=Array(Array(1, 1), Array(2, 1)), _
TrailingMinusNumbers:=True
Columns("A:C").Select
Range("C1").Activate
Selection.Delete Shift:=xlToLeft
Range("A2").Select
Sheets("Sheet2").Select
ActiveCell.FormulaR1C1 = "JAN"
Range("A3").Select
ActiveCell.FormulaR1C1 = "FEB"
Range("A3").Select
Selection.ClearContents
Range("A2").Select
ActiveCell.FormulaR1C1 = "FEB"
Range("A3").Select
ActiveCell.FormulaR1C1 = "MAR"
Range("A1:A3").Select
Selection.AutoFill Destination:=Range("A1:A12"), Type:=xlFillDefault
Range("A1:A12").Select
Range("B1").Select
ActiveCell.FormulaR1C1 = "1"
Range("B2").Select
ActiveCell.FormulaR1C1 = "2"
Range("B3").Select
ActiveCell.FormulaR1C1 = "3"
Range("B1:B3").Select
Selection.AutoFill Destination:=Range("B1:B12"), Type:=xlFillDefault
Range("B1:B12").Select
Sheets("Sheet1").Select
Columns("A:C").Select
Range("C1").Activate
Selection.ColumnWidth = 8
Range("D2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-3],Sheet2!C[-3]:C[-2],2,FALSE)"
Selection.AutoFill Destination:=Range("D2:D16"), Type:=xlFillDefault
Range("D2:D16").Select
Columns("D:D").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Columns("A:A").Select
Selection.Delete Shift:=xlToLeft
Columns("D:D").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("D2").Select
ActiveCell.FormulaR1C1 = "=DATE(RC[-2],RC[-1],RC[-3])"
Range("D2").Select
Selection.AutoFill Destination:=Range("D2:D16")
Range("D2:D16").Select
Columns("D:D").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Columns("A:C").Select
Range("C1").Activate
Selection.Delete Shift:=xlToLeft
Columns("A:M").Select
Selection.ColumnWidth = 12.86
Range("A1").Select
ActiveCell.FormulaR1C1 = "DATE"
Range("B1").Select
ActiveCell.FormulaR1C1 = "TIME"
Range("C1").Select
ActiveCell.FormulaR1C1 = "INBOUND"
Range("D1").Select
ActiveCell.FormulaR1C1 = "AVG INBOUND TIME"
Range("E1").Select
ActiveCell.FormulaR1C1 = "ABAND"
Range("F1").Select
ActiveCell.FormulaR1C1 = "AVG ABAND TIME"
Range("G1").Select
ActiveCell.FormulaR1C1 = "AVG TALK TIME"
Columns("H:J").Select
Selection.Delete Shift:=xlToLeft
Range("H1").Select
ActiveCell.FormulaR1C1 = "TOTAL INTERNAL"
Range("I1").Select
ActiveCell.FormulaR1C1 = "AVG STAFF"
Range("J1").Select
ActiveCell.FormulaR1C1 = "% IN SERV LEVEL"
Cells.Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Cells.EntireColumn.AutoFit
Range("A2").Select
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:="Y:\bcms_skill1.xlsm", FileFormat:= _
xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
Application.DisplayAlerts = True
End Sub





Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default Macro not returning correct data
To correct the data I need to see a sample of the original CSV file. OPen
the CSV file with NOTEPAD and copy a few of the rows onto the posting. I
also need to know what coutry youo are processing the data from. Dates in
excel expect different formats depending on the courty. If you are in
England and the Data comes from the US the Month and Day are reversed which
can cause errors like the one you are seeing.

"Joel" wrote:

Most of your code is formating which cannot be eliminate or made easier. You
don't need to use the select method liike in your original code. The
Selection method takes longer to run and makes the code harder to understand
and longer to write. I made changes the way I would write the code. Compare
the differences.


Sub bcms()
'
'
'

Set NewSht = ThisWorkbook.ActiveSheet
ChDir "Y:\"
Set CSVFile = Workbooks.Open( _
Filename:="Y:\report_list_bcms_skill_1_.csv")
CSVFile.ActiveSheet.Range("A1:U20").Copy _
Destination:=NewSht.Range("A1")
Application.CutCopyMode = False
CSVFile.Close
With Sheets("Sheet1")
.Cells.EntireColumn.AutoFit
.Columns("A:B").Delete
.Columns("B:G").Delete
.Columns("C:E").Select.Insert CopyOrigin:=xlFormatFromLeftOrAbove
.Rows("19:20").Delete Shift:=xlUp
.Range("B4:B18").TextToColumns Destination:=Range("B4"), _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=True, _
Semicolon:=False, _
Comma:=False, _
Space:=False, _
Other:=True, _
OtherChar:="-", _
FieldInfo:=Array(Array(1, 1), Array(2, 1)), _
TrailingMinusNumbers:=True

.Columns("C:C").Delete
.Columns("C:D").Delete
.Rows("1:3").Delete
.Rows("1:1").Insert CopyOrigin:=xlFormatFromLeftOrAbove
.Columns("B:G").Insert CopyOrigin:=xlFormatFromLeftOrAbove
.Columns("B:G").ColumnWidth = 10
.Range("A2:A16").Replace What:=",", _
Replacement:="", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False

.Range("A2:A16").TextToColumns _
Destination:=Range("B4"), _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=True, _
Semicolon:=False, _
Comma:=False, _
Space:=False, _
Other:=True, _
OtherChar:="-", _
FieldInfo:=Array(Array(1, 1), _
Array(2, 1)), _
TrailingMinusNumbers:=True

.Columns("A:C").Delete
End With

With Sheets("Sheet2")
.Range("A1") = "JAN"
.Range("A2") = "FEB"
.Range("A3") = "MAR"
.Range("A1:A3").AutoFill _
Destination:=.Range("A1:A12"), _
Type:=xlFillDefault

.Range("B1") = "1"
.Range("B2") = "2"
.Range("B3") = "3"
.Range("B1:B3").AutoFill _
Destination:=.Range("B1:B12"), _
Type:=xlFillDefault
End With

With Sheets("Sheet1")
.Columns("A:C").ColumnWidth = 8
Range("D2").FormulaR1C1 = "=VLOOKUP(RC[-3],Sheet2!C[-3]:C[-2],2,FALSE)"
.Range("D2").Copy _
Destination:=.Range("D2:D16")

.Columns("D:D").Copy
.Columns("D:D").PasteSpecial _
Paste:=xlPasteValues

.Columns("A:A").Delete
.Columns("D:D").Insert
.Range("D2").FormulaR1C1 = "=DATE(RC[-2],RC[-1],RC[-3])"
.Range("D2").Copy _
Destination:=.Range("D2:D16")
.Columns("D:D").Copy
.Columns("D:D").PasteSpecial _
Paste:=xlPasteValues

.Columns("A:C").Delete
.Columns("A:M").ColumnWidth = 12.86
.Range("A1") = "DATE"
.Range("B1") = "TIME"
.Range("C1") = "INBOUND"
.Range("D1") = "AVG INBOUND TIME"
.Range("E1") = "ABAND"
.Range("F1") = "AVG ABAND TIME"
.Range("G1") = "AVG TALK TIME"

.Columns("H:J").Delete
.Range("H1") = "TOTAL INTERNAL"
.Range("I1") = "AVG STAFF"
Range("J1") = "% IN SERV LEVEL"

With Cells
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
.EntireColumn.AutoFit
End With
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs _
Filename:="Y:\bcms_skill1.xlsm", _
FileFormat:=xlOpenXMLWorkbookMacroEnabled
Application.DisplayAlerts = True
End With
End Sub

"Jon Dibble" wrote:

Hi This Macro

Manipluates the data from a csv file then saves itself in a location ready
to be imported into SQL. The finished table looks like this - note that the
DATE column has returned #N/A. When this was recorded it returned a DATE.
Can anyone help or suggest if I could go about this task in an easier
fashion?

DATE TIME INBOUND AVG INBOUND TIME ABAND AVG ABAND TIME AVG TALK TIME
TOTAL INTERNAL AVG STAFF % IN SERV LEVEL
#N/A 08:00 11 00:05 0 00:00 01:07 258:52:00 7 100
#N/A 09:00 76 00:07 0 00:00 01:13 367:35:00 11 82
#N/A 10:00 61 00:07 2 00:03 01:18 383:06:00 11.8 76
#N/A 11:00 91 00:07 1 00:05 01:35 337:08:00 11 85
#N/A 12:00 70 00:06 1 00:09 01:56 337:54:00 11.1 87
#N/A 13:00 44 00:10 1 00:23 01:40 539:20:00 12 67
#N/A 14:00 85 00:08 1 00:10 01:42 413:56:00 12 71
#N/A 15:00 66 00:07 3 00:06 01:46 360:15:00 11 80
#N/A 16:00 61 00:07 0 00:00 01:50 397:56:00 10.7 84
#N/A 17:00 8 00:07 0 00:00 02:15 144:50:00 3.7 75
#N/A 18:00 0 00:00 0 00:00 00:00 00:00 0
#N/A 19:00 0 00:00 0 00:00 00:00 00:00 0
#N/A 20:00 0 00:00 0 00:00 00:00 00:00 0
#N/A 21:00 0 00:00 0 00:00 00:00 00:00 0
#N/A 22:00 0 00:00 0 00:00 00:00 00:00 0




Sub bcms()
'
'
'

Set NewSht = ThisWorkbook.ActiveSheet
ChDir "Y:\"
Set CSVFile =
Workbooks.Open(Filename:="Y:\report_list_bcms_skil l_1_.csv")
CSVFile.ActiveSheet.Range("A1:U20").Copy _
Destination:=NewSht.Range("A1")
Application.CutCopyMode = False
CSVFile.Close
Cells.Select
Cells.EntireColumn.AutoFit
Columns("A:B").Select
Range("B1").Activate
Selection.Delete Shift:=xlToLeft
Columns("B:G").Select
Selection.Delete Shift:=xlToLeft
Columns("C:E").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Rows("19:20").Select
Selection.Delete Shift:=xlUp
Range("B4:B18").TextToColumns Destination:=Range("B4"), _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=True, _
Semicolon:=False, _
Comma:=False, _
Space:=False, _
Other:=True, _
OtherChar:="-", _
FieldInfo:=Array(Array(1, 1), Array(2,
1)), _
TrailingMinusNumbers:=True
Columns("C:C").Select
Selection.Delete Shift:=xlToLeft
Columns("C:D").Select
Selection.Delete Shift:=xlToLeft
Rows("1:3").Select
Range("A3").Activate
Selection.Delete Shift:=xlUp
Rows("1:1").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("B:G").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.ColumnWidth = 10
Range("A2:A16").Select
Selection.Replace What:=",", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.TextToColumns Destination:=Range("B4"), _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, _
OtherChar _
:="-", FieldInfo:=Array(Array(1, 1), Array(2, 1)), _
TrailingMinusNumbers:=True
Columns("A:C").Select
Range("C1").Activate
Selection.Delete Shift:=xlToLeft
Range("A2").Select
Sheets("Sheet2").Select
ActiveCell.FormulaR1C1 = "JAN"
Range("A3").Select
ActiveCell.FormulaR1C1 = "FEB"
Range("A3").Select
Selection.ClearContents
Range("A2").Select
ActiveCell.FormulaR1C1 = "FEB"
Range("A3").Select
ActiveCell.FormulaR1C1 = "MAR"
Range("A1:A3").Select
Selection.AutoFill Destination:=Range("A1:A12"), Type:=xlFillDefault
Range("A1:A12").Select
Range("B1").Select
ActiveCell.FormulaR1C1 = "1"
Range("B2").Select
ActiveCell.FormulaR1C1 = "2"
Range("B3").Select
ActiveCell.FormulaR1C1 = "3"
Range("B1:B3").Select
Selection.AutoFill Destination:=Range("B1:B12"), Type:=xlFillDefault
Range("B1:B12").Select
Sheets("Sheet1").Select
Columns("A:C").Select
Range("C1").Activate
Selection.ColumnWidth = 8
Range("D2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-3],Sheet2!C[-3]:C[-2],2,FALSE)"
Selection.AutoFill Destination:=Range("D2:D16"), Type:=xlFillDefault
Range("D2:D16").Select
Columns("D:D").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Columns("A:A").Select
Selection.Delete Shift:=xlToLeft
Columns("D:D").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("D2").Select
ActiveCell.FormulaR1C1 = "=DATE(RC[-2],RC[-1],RC[-3])"
Range("D2").Select
Selection.AutoFill Destination:=Range("D2:D16")
Range("D2:D16").Select
Columns("D:D").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Columns("A:C").Select
Range("C1").Activate
Selection.Delete Shift:=xlToLeft
Columns("A:M").Select
Selection.ColumnWidth = 12.86
Range("A1").Select
ActiveCell.FormulaR1C1 = "DATE"
Range("B1").Select
ActiveCell.FormulaR1C1 = "TIME"
Range("C1").Select
ActiveCell.FormulaR1C1 = "INBOUND"
Range("D1").Select
ActiveCell.FormulaR1C1 = "AVG INBOUND TIME"
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 10
Default Macro not returning correct data
Hi Joel

I recorded the Macro again and added in my additonal code to ensure it
overwrites the saved file and calls macro on opening and has fixed the date
issue. Have attached the working code below. I am in the UK. Many thanks for
taking the time to look at this for me.

Sub skill1()
'
' skill1 Macro
'

'
Set NewSht = ThisWorkbook.ActiveSheet
ChDir "Y:\"
Set CSVFile =
Workbooks.Open(Filename:="Y:\report_list_bcms_skil l_1_.csv")
CSVFile.ActiveSheet.Range("A1:U20").Copy _
Destination:=NewSht.Range("A1")
Application.CutCopyMode = False
CSVFile.Close
Cells.Select
Cells.EntireColumn.AutoFit
Columns("A:B").Select
Range("B1").Activate
Selection.Delete Shift:=xlToLeft
Columns("B:G").Select
Range("G1").Activate
Selection.Delete Shift:=xlToLeft
Rows("1:3").Select
Range("A3").Activate
Selection.Delete Shift:=xlUp
Columns("C:D").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Rows("16:17").Select
Selection.Delete Shift:=xlUp
Range("B1:B15").Select
Selection.TextToColumns Destination:=Range("B1"), DataType:=xlDelimited,
_
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar
_
:="-", FieldInfo:=Array(Array(1, 1), Array(2, 1)),
TrailingMinusNumbers:=True
Columns("C:D").Select
Selection.Delete Shift:=xlToLeft
Columns("A:M").Select
Range("M1").Activate
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("B:E").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.ColumnWidth = 10
Columns("D:E").Select
Range("E1").Activate
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A1:A15").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited,
_
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, FieldInfo
_
:=Array(1, 1), TrailingMinusNumbers:=True
Selection.TextToColumns Destination:=Range("A1"),
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(5, 1), Array(8, 1), Array(13,
1), Array(17, 1), _
Array(20, 1)), TrailingMinusNumbers:=True
Columns("A:C").Select
Range("C1").Activate
Selection.Delete Shift:=xlToLeft
Range("B1:B15").Select
Selection.Replace What:=",", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("D1").Select
ActiveCell.FormulaR1C1 = "=DATE(RC[-1],RC[-3],RC[-2])"
Range("D1").Select
Selection.ClearContents
Sheets("Sheet2").Select
ActiveCell.FormulaR1C1 = "JAN"
Range("A2").Select
ActiveCell.FormulaR1C1 = "FEB"
Range("A3").Select
ActiveCell.FormulaR1C1 = "MAR"
Range("A1:A3").Select
Selection.AutoFill Destination:=Range("A1:A12"), Type:=xlFillDefault
Range("A1:A12").Select
Range("B1").Select
ActiveCell.FormulaR1C1 = "1"
Range("B2").Select
ActiveCell.FormulaR1C1 = "2"
Range("B3").Select
ActiveCell.FormulaR1C1 = "3"
Range("B1:B3").Select
Selection.AutoFill Destination:=Range("B1:B12")
Range("B1:B12").Select
Sheets("Sheet1").Select
ActiveCell.FormulaR1C1 =
"=VLOOKUP(RC[-3],Sheet2!RC[-3]:R[11]C[-2],2,FALSE)"
Columns("D:D").Select
Selection.NumberFormat = "0"
Range("D1").Select
Selection.AutoFill Destination:=Range("D1:D15")
Range("D1:D15").Select
Range("D1").Select
Selection.AutoFill Destination:=Range("D1:D15"), Type:=xlFillDefault
Range("D1:D15").Select
Range("D1:D15").Select
Selection.ClearContents
Range("D1").Select
Sheets("Sheet2").Select
Range("A1").Select
Sheets("Sheet1").Select
ActiveCell.FormulaR1C1 =
"=VLOOKUP(RC[-3],Sheet2!RC[-3]:R[11]C[-2],2,FALSE)"
Selection.AutoFill Destination:=Range("D1:D15"), Type:=xlFillDefault
Range("D1:D15").Select
Range("D1").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-3],Sheet2!R1C1:R12C2,2,FALSE)"
Range("D1").Select
Selection.AutoFill Destination:=Range("D1:D16"), Type:=xlFillDefault
Range("D1:D16").Select
Range("D16").Select
Selection.ClearContents
Columns("D:D").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Columns("A:A").Select
Selection.Delete Shift:=xlToLeft
Columns("D:D").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("D1").Select
ActiveCell.FormulaR1C1 = "=DATE(RC[-2],RC[-1],RC[-3])"
Range("D1").Select
Selection.AutoFill Destination:=Range("D1:D15"), Type:=xlFillDefault
Range("D1:D15").Select
Columns("D:D").Select
Selection.Copy
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Columns("A:C").Select
Range("C1").Activate
Selection.Delete Shift:=xlToLeft
Columns("A:A").Select
Selection.NumberFormat = "dd/mm/yyyy;@"
Range("A1").Select
Columns("A:A").ColumnWidth = 11.71
Rows("1:1").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A1").Select
ActiveCell.FormulaR1C1 = "DATE"
Range("B1").Select
ActiveCell.FormulaR1C1 = "TIME"
Range("C1").Select
ActiveCell.FormulaR1C1 = "INBOUND"
Range("D1").Select
Columns("C:C").ColumnWidth = 10.14
Columns("D:D").Select
Selection.ColumnWidth = 11.29
Columns("E:M").Select
Selection.ColumnWidth = 20.57
Range("D1").Select
Columns("D:D").ColumnWidth = 13.43
Range("D1").Select
ActiveCell.FormulaR1C1 = "AVG INBOUND TIME"
Range("E1").Select
ActiveCell.FormulaR1C1 = "AVG TALK TIME"
Range("E1").Select
Selection.ClearContents
Range("D1").Select
Selection.ClearContents
Range("D1").Select
ActiveCell.FormulaR1C1 = "AVG SPEED ANS"
Range("E1").Select
ActiveCell.FormulaR1C1 = "ABAND"
Range("F1").Select
ActiveCell.FormulaR1C1 = "AVG ABAND TIME"
Range("G1").Select
ActiveCell.FormulaR1C1 = "AVG TALK TIME"
Range("H1").Select
ActiveCell.FormulaR1C1 = "TOTAL AFTER CALL"
Range("I1").Select
ActiveCell.FormulaR1C1 = "FLOW IN"
Range("J1").Select
ActiveCell.FormulaR1C1 = "FLOW OUT"
Range("K1").Select
ActiveCell.FormulaR1C1 = "AUX / INTERNAL"
Range("L1").Select
ActiveCell.FormulaR1C1 = "AVG STAFF"
Range("M1").Select
ActiveCell.FormulaR1C1 = "IN SERV LEVEL %"
Cells.Select
With Selection.Font
.Name = "Calibri"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontMinor
End With
Cells.EntireColumn.AutoFit
Range("A2").Select
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:="Y:\bcms_skill1.xlsm", FileFormat:= _
xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
Application.DisplayAlerts = True
End Sub



"Joel" wrote in message
...
To correct the data I need to see a sample of the original CSV file. OPen
the CSV file with NOTEPAD and copy a few of the rows onto the posting. I
also need to know what coutry youo are processing the data from. Dates in
excel expect different formats depending on the courty. If you are in
England and the Data comes from the US the Month and Day are reversed
which
can cause errors like the one you are seeing.

"Joel" wrote:

Most of your code is formating which cannot be eliminate or made easier.
You
don't need to use the select method liike in your original code. The
Selection method takes longer to run and makes the code harder to
understand
and longer to write. I made changes the way I would write the code.
Compare
the differences.


Sub bcms()
'
'
'

Set NewSht = ThisWorkbook.ActiveSheet
ChDir "Y:\"
Set CSVFile = Workbooks.Open( _
Filename:="Y:\report_list_bcms_skill_1_.csv")
CSVFile.ActiveSheet.Range("A1:U20").Copy _
Destination:=NewSht.Range("A1")
Application.CutCopyMode = False
CSVFile.Close
With Sheets("Sheet1")
.Cells.EntireColumn.AutoFit
.Columns("A:B").Delete
.Columns("B:G").Delete
.Columns("C:E").Select.Insert CopyOrigin:=xlFormatFromLeftOrAbove
.Rows("19:20").Delete Shift:=xlUp
.Range("B4:B18").TextToColumns Destination:=Range("B4"), _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=True, _
Semicolon:=False, _
Comma:=False, _
Space:=False, _
Other:=True, _
OtherChar:="-", _
FieldInfo:=Array(Array(1, 1), Array(2, 1)), _
TrailingMinusNumbers:=True

.Columns("C:C").Delete
.Columns("C:D").Delete
.Rows("1:3").Delete
.Rows("1:1").Insert CopyOrigin:=xlFormatFromLeftOrAbove
.Columns("B:G").Insert CopyOrigin:=xlFormatFromLeftOrAbove
.Columns("B:G").ColumnWidth = 10
.Range("A2:A16").Replace What:=",", _
Replacement:="", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False

.Range("A2:A16").TextToColumns _
Destination:=Range("B4"), _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=True, _
Semicolon:=False, _
Comma:=False, _
Space:=False, _
Other:=True, _
OtherChar:="-", _
FieldInfo:=Array(Array(1, 1), _
Array(2, 1)), _
TrailingMinusNumbers:=True

.Columns("A:C").Delete
End With

With Sheets("Sheet2")
.Range("A1") = "JAN"
.Range("A2") = "FEB"
.Range("A3") = "MAR"
.Range("A1:A3").AutoFill _
Destination:=.Range("A1:A12"), _
Type:=xlFillDefault

.Range("B1") = "1"
.Range("B2") = "2"
.Range("B3") = "3"
.Range("B1:B3").AutoFill _
Destination:=.Range("B1:B12"), _
Type:=xlFillDefault
End With

With Sheets("Sheet1")
.Columns("A:C").ColumnWidth = 8
Range("D2").FormulaR1C1 =
"=VLOOKUP(RC[-3],Sheet2!C[-3]:C[-2],2,FALSE)"
.Range("D2").Copy _
Destination:=.Range("D2:D16")

.Columns("D:D").Copy
.Columns("D:D").PasteSpecial _
Paste:=xlPasteValues

.Columns("A:A").Delete
.Columns("D:D").Insert
.Range("D2").FormulaR1C1 = "=DATE(RC[-2],RC[-1],RC[-3])"
.Range("D2").Copy _
Destination:=.Range("D2:D16")
.Columns("D:D").Copy
.Columns("D:D").PasteSpecial _
Paste:=xlPasteValues

.Columns("A:C").Delete
.Columns("A:M").ColumnWidth = 12.86
.Range("A1") = "DATE"
.Range("B1") = "TIME"
.Range("C1") = "INBOUND"
.Range("D1") = "AVG INBOUND TIME"
.Range("E1") = "ABAND"
.Range("F1") = "AVG ABAND TIME"
.Range("G1") = "AVG TALK TIME"

.Columns("H:J").Delete
.Range("H1") = "TOTAL INTERNAL"
.Range("I1") = "AVG STAFF"
Range("J1") = "% IN SERV LEVEL"

With Cells
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
.EntireColumn.AutoFit
End With
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs _
Filename:="Y:\bcms_skill1.xlsm", _
FileFormat:=xlOpenXMLWorkbookMacroEnabled
Application.DisplayAlerts = True
End With
End Sub

"Jon Dibble" wrote:

Hi This Macro

Manipluates the data from a csv file then saves itself in a location
ready
to be imported into SQL. The finished table looks like this - note that
the
DATE column has returned #N/A. When this was recorded it returned a
DATE.
Can anyone help or suggest if I could go about this task in an easier
fashion?

DATE TIME INBOUND AVG INBOUND TIME ABAND AVG ABAND TIME AVG TALK
TIME
TOTAL INTERNAL AVG STAFF % IN SERV LEVEL
#N/A 08:00 11 00:05 0 00:00 01:07 258:52:00 7 100
#N/A 09:00 76 00:07 0 00:00 01:13 367:35:00 11 82
#N/A 10:00 61 00:07 2 00:03 01:18 383:06:00 11.8 76
#N/A 11:00 91 00:07 1 00:05 01:35 337:08:00 11 85
#N/A 12:00 70 00:06 1 00:09 01:56 337:54:00 11.1 87
#N/A 13:00 44 00:10 1 00:23 01:40 539:20:00 12 67
#N/A 14:00 85 00:08 1 00:10 01:42 413:56:00 12 71
#N/A 15:00 66 00:07 3 00:06 01:46 360:15:00 11 80
#N/A 16:00 61 00:07 0 00:00 01:50 397:56:00 10.7 84
#N/A 17:00 8 00:07 0 00:00 02:15 144:50:00 3.7 75
#N/A 18:00 0 00:00 0 00:00 00:00 00:00 0
#N/A 19:00 0 00:00 0 00:00 00:00 00:00 0
#N/A 20:00 0 00:00 0 00:00 00:00 00:00 0
#N/A 21:00 0 00:00 0 00:00 00:00 00:00 0
#N/A 22:00 0 00:00 0 00:00 00:00 00:00 0




Sub bcms()
'
'
'

Set NewSht = ThisWorkbook.ActiveSheet
ChDir "Y:\"
Set CSVFile =
Workbooks.Open(Filename:="Y:\report_list_bcms_skil l_1_.csv")
CSVFile.ActiveSheet.Range("A1:U20").Copy _
Destination:=NewSht.Range("A1")
Application.CutCopyMode = False
CSVFile.Close
Cells.Select
Cells.EntireColumn.AutoFit
Columns("A:B").Select
Range("B1").Activate
Selection.Delete Shift:=xlToLeft
Columns("B:G").Select
Selection.Delete Shift:=xlToLeft
Columns("C:E").Select
Selection.Insert Shift:=xlToRight,
CopyOrigin:=xlFormatFromLeftOrAbove
Rows("19:20").Select
Selection.Delete Shift:=xlUp
Range("B4:B18").TextToColumns Destination:=Range("B4"), _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=True, _
Semicolon:=False, _
Comma:=False, _
Space:=False, _
Other:=True, _
OtherChar:="-", _
FieldInfo:=Array(Array(1, 1),
Array(2,
1)), _
TrailingMinusNumbers:=True
Columns("C:C").Select
Selection.Delete Shift:=xlToLeft
Columns("C:D").Select
Selection.Delete Shift:=xlToLeft
Rows("1:3").Select
Range("A3").Activate
Selection.Delete Shift:=xlUp
Rows("1:1").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("B:G").Select
Selection.Insert Shift:=xlToRight,
CopyOrigin:=xlFormatFromLeftOrAbove
Selection.ColumnWidth = 10
Range("A2:A16").Select
Selection.Replace What:=",", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.TextToColumns Destination:=Range("B4"), _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True,
_
OtherChar _
:="-", FieldInfo:=Array(Array(1, 1), Array(2, 1)), _
TrailingMinusNumbers:=True
Columns("A:C").Select
Range("C1").Activate
Selection.Delete Shift:=xlToLeft
Range("A2").Select
Sheets("Sheet2").Select
ActiveCell.FormulaR1C1 = "JAN"
Range("A3").Select
ActiveCell.FormulaR1C1 = "FEB"
Range("A3").Select
Selection.ClearContents
Range("A2").Select
ActiveCell.FormulaR1C1 = "FEB"
Range("A3").Select
ActiveCell.FormulaR1C1 = "MAR"
Range("A1:A3").Select
Selection.AutoFill Destination:=Range("A1:A12"),
Type:=xlFillDefault
Range("A1:A12").Select
Range("B1").Select
ActiveCell.FormulaR1C1 = "1"
Range("B2").Select
ActiveCell.FormulaR1C1 = "2"
Range("B3").Select
ActiveCell.FormulaR1C1 = "3"
Range("B1:B3").Select
Selection.AutoFill Destination:=Range("B1:B12"),
Type:=xlFillDefault
Range("B1:B12").Select
Sheets("Sheet1").Select
Columns("A:C").Select
Range("C1").Activate
Selection.ColumnWidth = 8
Range("D2").Select
ActiveCell.FormulaR1C1 =
"=VLOOKUP(RC[-3],Sheet2!C[-3]:C[-2],2,FALSE)"
Selection.AutoFill Destination:=Range("D2:D16"),
Type:=xlFillDefault
Range("D2:D16").Select
Columns("D:D").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Columns("A:A").Select
Selection.Delete Shift:=xlToLeft
Columns("D:D").Select
Selection.Insert Shift:=xlToRight,
CopyOrigin:=xlFormatFromLeftOrAbove
Range("D2").Select
ActiveCell.FormulaR1C1 = "=DATE(RC[-2],RC[-1],RC[-3])"
Range("D2").Select
Selection.AutoFill Destination:=Range("D2:D16")
Range("D2:D16").Select
Columns("D:D").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Columns("A:C").Select
Range("C1").Activate
Selection.Delete Shift:=xlToLeft
Columns("A:M").Select
Selection.ColumnWidth = 12.86
Range("A1").Select
ActiveCell.FormulaR1C1 = "DATE"
Range("B1").Select
ActiveCell.FormulaR1C1 = "TIME"
Range("C1").Select
ActiveCell.FormulaR1C1 = "INBOUND"
Range("D1").Select
ActiveCell.FormulaR1C1 = "AVG INBOUND TIME"

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default Macro not returning correct data
Sub GetIDS()

Set OctSht = Sheets("October")
Set NovSht = Sheets("November")
Set IDSht = Sheets("ID")

IDSht.Cells.ClearContents

'Copy October IDs to ID Sheet
OctSht.Columns("B").Copy _
Destination:=IDSht.Columns("A")
'Delete Header Row from ID Sheet
IDSht.Rows(1).Delete

LastRow = IDSht.Range("A" & Rows.Count).End(xlUp).Row
NewRow = LastRow + 1

'Copy November IDs to ID Sheet, skip Header
LastRow = NovSht.Range("A" & Rows.Count).End(xlUp).Row
NovSht.Range("B2:B" & LastRow).Copy _
Destination:=IDSht.Range("A" & NewRow)

'sort ID's
With IDSht
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
.Rows("1:" & LastRow).Sort _
key1:=.Range("A1"), _
order1:=xlAscending, _
header:=xlNo

'Remove duplicate IDs
RowCount = 1
Do While .Range("A" & RowCount) < ""
If .Range("A" & RowCount) = .Range("A" & (RowCount + 1)) Then
.Rows(RowCount + 1).Delete
Else
RowCount = RowCount + 1
End If
Loop
End With
End Sub
Sub MatchQuery()

Set OctSht = Sheets("October")
Set NovSht = Sheets("November")
Set OctOnly = Sheets("Oct Only")
Set NovOnly = Sheets("Nov Only")
Set MatchBoth = Sheets("Match")
Set IDSht = Sheets("ID")

LastRow = IDSht.Range("A" & Rows.Count).End(xlUp).Row
For RowCount = 1 To LastRow
ID = IDSht.Range("A" & RowCount)

Set FoundOct = OctSht.Columns("B").Find(what:=ID, _
LookIn:=xlValues, lookat:=xlWhole)

Set FoundNov = NovSht.Columns("B").Find(what:=ID, _
LookIn:=xlValues, lookat:=xlWhole)

If FoundOct Is Nothing Then

If FoundNov Is Nothing Then
MsgBox ("ID : " & ID & " is not found")
Else
'found in November only
With NovOnly
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
NewRow = LastRow + 1
NovSht.Range("A" & FoundNov.Row & ":I" & FoundNov.Row).Copy _
Destination:=.Range("A" & NewRow)
End With
End If
Else
If FoundNov Is Nothing Then
'Found in October Only
With OctOnly
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
NewRow = LastRow + 1
OctSht.Range("A" & FoundOct.Row & ":I" & FoundOct.Row).Copy _
Destination:=.Range("A" & NewRow)
End With
Else
'Found in both October and November
With MatchBoth
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
NewRow = LastRow + 1
OctSht.Range("A" & FoundOct.Row & ":I" & FoundOct.Row).Copy _
Destination:=.Range("A" & NewRow)
NovSht.Range("A" & FoundNov.Row & ":I" & FoundNov.Row).Copy _
Destination:=.Range("J" & NewRow)
End With
End If
End If
Next RowCount
End Sub




"Jon Dibble" wrote:

Hi Joel

I recorded the Macro again and added in my additonal code to ensure it
overwrites the saved file and calls macro on opening and has fixed the date
issue. Have attached the working code below. I am in the UK. Many thanks for
taking the time to look at this for me.

Sub skill1()
'
' skill1 Macro
'

'
Set NewSht = ThisWorkbook.ActiveSheet
ChDir "Y:\"
Set CSVFile =
Workbooks.Open(Filename:="Y:\report_list_bcms_skil l_1_.csv")
CSVFile.ActiveSheet.Range("A1:U20").Copy _
Destination:=NewSht.Range("A1")
Application.CutCopyMode = False
CSVFile.Close
Cells.Select
Cells.EntireColumn.AutoFit
Columns("A:B").Select
Range("B1").Activate
Selection.Delete Shift:=xlToLeft
Columns("B:G").Select
Range("G1").Activate
Selection.Delete Shift:=xlToLeft
Rows("1:3").Select
Range("A3").Activate
Selection.Delete Shift:=xlUp
Columns("C:D").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Rows("16:17").Select
Selection.Delete Shift:=xlUp
Range("B1:B15").Select
Selection.TextToColumns Destination:=Range("B1"), DataType:=xlDelimited,
_
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar
_
:="-", FieldInfo:=Array(Array(1, 1), Array(2, 1)),
TrailingMinusNumbers:=True
Columns("C:D").Select
Selection.Delete Shift:=xlToLeft
Columns("A:M").Select
Range("M1").Activate
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("B:E").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.ColumnWidth = 10
Columns("D:E").Select
Range("E1").Activate
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A1:A15").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited,
_
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, FieldInfo
_
:=Array(1, 1), TrailingMinusNumbers:=True
Selection.TextToColumns Destination:=Range("A1"),
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(5, 1), Array(8, 1), Array(13,
1), Array(17, 1), _
Array(20, 1)), TrailingMinusNumbers:=True
Columns("A:C").Select
Range("C1").Activate
Selection.Delete Shift:=xlToLeft
Range("B1:B15").Select
Selection.Replace What:=",", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("D1").Select
ActiveCell.FormulaR1C1 = "=DATE(RC[-1],RC[-3],RC[-2])"
Range("D1").Select
Selection.ClearContents
Sheets("Sheet2").Select
ActiveCell.FormulaR1C1 = "JAN"
Range("A2").Select
ActiveCell.FormulaR1C1 = "FEB"
Range("A3").Select
ActiveCell.FormulaR1C1 = "MAR"
Range("A1:A3").Select
Selection.AutoFill Destination:=Range("A1:A12"), Type:=xlFillDefault
Range("A1:A12").Select
Range("B1").Select
ActiveCell.FormulaR1C1 = "1"
Range("B2").Select
ActiveCell.FormulaR1C1 = "2"
Range("B3").Select
ActiveCell.FormulaR1C1 = "3"
Range("B1:B3").Select
Selection.AutoFill Destination:=Range("B1:B12")
Range("B1:B12").Select
Sheets("Sheet1").Select
ActiveCell.FormulaR1C1 =
"=VLOOKUP(RC[-3],Sheet2!RC[-3]:R[11]C[-2],2,FALSE)"
Columns("D:D").Select
Selection.NumberFormat = "0"
Range("D1").Select
Selection.AutoFill Destination:=Range("D1:D15")
Range("D1:D15").Select
Range("D1").Select
Selection.AutoFill Destination:=Range("D1:D15"), Type:=xlFillDefault
Range("D1:D15").Select
Range("D1:D15").Select
Selection.ClearContents
Range("D1").Select
Sheets("Sheet2").Select
Range("A1").Select
Sheets("Sheet1").Select
ActiveCell.FormulaR1C1 =
"=VLOOKUP(RC[-3],Sheet2!RC[-3]:R[11]C[-2],2,FALSE)"
Selection.AutoFill Destination:=Range("D1:D15"), Type:=xlFillDefault
Range("D1:D15").Select
Range("D1").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-3],Sheet2!R1C1:R12C2,2,FALSE)"
Range("D1").Select
Selection.AutoFill Destination:=Range("D1:D16"), Type:=xlFillDefault
Range("D1:D16").Select
Range("D16").Select
Selection.ClearContents
Columns("D:D").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Columns("A:A").Select
Selection.Delete Shift:=xlToLeft
Columns("D:D").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("D1").Select
ActiveCell.FormulaR1C1 = "=DATE(RC[-2],RC[-1],RC[-3])"
Range("D1").Select
Selection.AutoFill Destination:=Range("D1:D15"), Type:=xlFillDefault
Range("D1:D15").Select
Columns("D:D").Select
Selection.Copy
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Columns("A:C").Select
Range("C1").Activate
Selection.Delete Shift:=xlToLeft
Columns("A:A").Select
Selection.NumberFormat = "dd/mm/yyyy;@"
Range("A1").Select
Columns("A:A").ColumnWidth = 11.71
Rows("1:1").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A1").Select
ActiveCell.FormulaR1C1 = "DATE"
Range("B1").Select
ActiveCell.FormulaR1C1 = "TIME"
Range("C1").Select
ActiveCell.FormulaR1C1 = "INBOUND"
Range("D1").Select
Columns("C:C").ColumnWidth = 10.14
Columns("D:D").Select
Selection.ColumnWidth = 11.29
Columns("E:M").Select
Selection.ColumnWidth = 20.57
Range("D1").Select
Columns("D:D").ColumnWidth = 13.43
Range("D1").Select
ActiveCell.FormulaR1C1 = "AVG INBOUND TIME"
Range("E1").Select
ActiveCell.FormulaR1C1 = "AVG TALK TIME"
Range("E1").Select
Selection.ClearContents
Range("D1").Select
Selection.ClearContents
Range("D1").Select
ActiveCell.FormulaR1C1 = "AVG SPEED ANS"
Range("E1").Select
ActiveCell.FormulaR1C1 = "ABAND"
Range("F1").Select
ActiveCell.FormulaR1C1 = "AVG ABAND TIME"
Range("G1").Select
ActiveCell.FormulaR1C1 = "AVG TALK TIME"
Range("H1").Select
ActiveCell.FormulaR1C1 = "TOTAL AFTER CALL"
Range("I1").Select
ActiveCell.FormulaR1C1 = "FLOW IN"
Range("J1").Select
ActiveCell.FormulaR1C1 = "FLOW OUT"
Range("K1").Select
ActiveCell.FormulaR1C1 = "AUX / INTERNAL"
Range("L1").Select
ActiveCell.FormulaR1C1 = "AVG STAFF"
Range("M1").Select
ActiveCell.FormulaR1C1 = "IN SERV LEVEL %"
Cells.Select
With Selection.Font
.Name = "Calibri"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontMinor
End With
Cells.EntireColumn.AutoFit
Range("A2").Select
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:="Y:\bcms_skill1.xlsm", FileFormat:= _
xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
Application.DisplayAlerts = True
End Sub



"Joel" wrote in message
...
To correct the data I need to see a sample of the original CSV file. OPen
the CSV file with NOTEPAD and copy a few of the rows onto the posting. I
also need to know what coutry youo are processing the data from. Dates in
excel expect different formats depending on the courty. If you are in
England and the Data comes from the US the Month and Day are reversed
which
can cause errors like the one you are seeing.

"Joel" wrote:

Most of your code is formating which cannot be eliminate or made easier.
You
don't need to use the select method liike in your original code. The
Selection method takes longer to run and makes the code harder to
understand
and longer to write. I made changes the way I would write the code.
Compare
the differences.


Sub bcms()
'
'
'

Set NewSht = ThisWorkbook.ActiveSheet
ChDir "Y:\"
Set CSVFile = Workbooks.Open( _
Filename:="Y:\report_list_bcms_skill_1_.csv")
CSVFile.ActiveSheet.Range("A1:U20").Copy _
Destination:=NewSht.Range("A1")
Application.CutCopyMode = False
CSVFile.Close
With Sheets("Sheet1")
.Cells.EntireColumn.AutoFit
.Columns("A:B").Delete
.Columns("B:G").Delete
.Columns("C:E").Select.Insert CopyOrigin:=xlFormatFromLeftOrAbove
.Rows("19:20").Delete Shift:=xlUp
.Range("B4:B18").TextToColumns Destination:=Range("B4"), _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=True, _
Semicolon:=False, _
Comma:=False, _
Space:=False, _
Other:=True, _
OtherChar:="-", _
FieldInfo:=Array(Array(1, 1), Array(2, 1)), _
TrailingMinusNumbers:=True

.Columns("C:C").Delete
.Columns("C:D").Delete
.Rows("1:3").Delete
.Rows("1:1").Insert CopyOrigin:=xlFormatFromLeftOrAbove
.Columns("B:G").Insert CopyOrigin:=xlFormatFromLeftOrAbove
.Columns("B:G").ColumnWidth = 10
.Range("A2:A16").Replace What:=",", _
Replacement:="", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

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
Vlookup not returning correct value Drebdog Excel Worksheet Functions 4 August 6th 08 11:29 PM
MAX Function not returning correct value Phil Excel Worksheet Functions 4 April 28th 06 10:14 PM
VLOOKUP not returning correct value leigh Excel Worksheet Functions 6 October 13th 05 12:22 PM
UDF not returning correct information [email protected] Excel Programming 1 July 14th 05 12:04 AM
Vlookup not returning correct value Mandy Brookes Excel Worksheet Functions 1 January 26th 05 12:12 PM


All times are GMT +1. The time now is 03:44 AM.

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

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement