.Offset(0, 16).Value = Application.WorksheetFunct
Try commenting it out and running a test.
Kevin
Sub Convert_data()
' Converts Genesys Data to the correct format for loading
'
' 20110225 - Created by Nigel Rablin
'
Dim Genesys_region As Range
Dim Cell As Variant
Dim Lastrow As Long
Dim i As Long
Lastrow = Sheets("Genesys Raw").Range("C1000000").End(xlUp).Row
Set Genesys_region = Sheets("Genesys Raw").Range("C3:C" & Lastrow)
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Sheets("Converted Data").Range("A4:Z1000000").ClearContents
For Each Cell In Genesys_region
On Error GoTo Errorhandler
If Cell.Value <> Prevvalue Then
matrixrow = Application.WorksheetFunction.Match(Trim(Cell.Value), Sheets("Matrix Raw").Range("M1:M999"), 0)
End If
Prevvalue = Cell.Value
On Error GoTo 0
team_code = Sheets("Matrix Raw").Range("I" & matrixrow).Value
With Sheets("Converted Data").Range("C" & (i + 4))
.Offset(0, 0).Value = Sheets("Matrix Raw").Range("C" & matrixrow).Value
.Offset(0, 1).Value = Sheets("Matrix Raw").Range("L" & matrixrow).Value
.Offset(0, 2).Value = Sheets("Matrix Raw").Range("K" & matrixrow).Value
.Offset(0, 3).Value = Sheets("Matrix Raw").Range("M" & matrixrow).Value
.Offset(0, 4).Value = Trim(Cell.Value)
.Offset(0, 6).Value = Sheets("Matrix Raw").Range("F" & matrixrow).Value
.Offset(0, 7).Value = Cell.Offset(0, 1)
.Offset(0, 8).Value = UCase(Format(Cell.Offset(0, 1), "ddd"))
.Offset(0, 9).Value = Cell.Offset(0, 2)
.Offset(0, 10).Value = Cell.Offset(0, 1) + Cell.Offset(0, 3)
.Offset(0, 11).Value = Cell.Offset(0, 1) + Cell.Offset(0, 4)
.Offset(0, 12).Value = Cell.Offset(0, 5) * 24 * 60
.Offset(0, 15).Value = team_code
.Offset(0, 16).Value = Application.WorksheetFunction.VLookup(team_code, Sheets("Matrix Raw").Range("R:S"), 2, False)
End With
i = i + 1
Next Cell
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Exit Sub
Errorhandler:
matrixrow = 3434
Resume Next
End Sub
On Error GoTo Errorhandler
If Cell.Value <> Prevvalue Then
matrixrow = Application.WorksheetFunction.Match(Trim(Cell.Value), Sheets("Matrix Raw").Range("M1:M999"), 0)
End If
Prevvalue = Cell.Value
On Error GoTo 0
For Each Cell In Genesys_region
On Error GoTo Errorhandler
If Cell.Value <> Prevvalue Then
matrixrow = Application.WorksheetFunction.Match(Trim(Cell.Value), Sheets("Matrix Raw").Range("M1:M999"), 0)
End If
Prevvalue = Cell.Value
On Error GoTo 0
team_code = Sheets("Matrix Raw").Range("I" & matrixrow).Value
With Sheets("Converted Data").Range("C" & (i + 4))
.Offset(0, 0).Value = Sheets("Matrix Raw").Range("C" & matrixrow).Value
.Offset(0, 1).Value = Sheets("Matrix Raw").Range("L" & matrixrow).Value
.Offset(0, 2).Value = Sheets("Matrix Raw").Range("K" & matrixrow).Value
.Offset(0, 3).Value = Sheets("Matrix Raw").Range("M" & matrixrow).Value
.Offset(0, 4).Value = Trim(Cell.Value)
.Offset(0, 6).Value = Sheets("Matrix Raw").Range("F" & matrixrow).Value
.Offset(0, 7).Value = Cell.Offset(0, 1)
.Offset(0, 8).Value = UCase(Format(Cell.Offset(0, 1), "ddd"))
.Offset(0, 9).Value = Cell.Offset(0, 2)
.Offset(0, 10).Value = Cell.Offset(0, 1) + Cell.Offset(0, 3)
.Offset(0, 11).Value = Cell.Offset(0, 1) + Cell.Offset(0, 4)
.Offset(0, 12).Value = Cell.Offset(0, 5) * 24 * 60
.Offset(0, 15).Value = team_code
.Offset(0, 16).Value = Application.WorksheetFunction.VLookup(team_code, Sheets("Matrix Raw").Range("R:S"), 2, False)
End With
i = i + 1
Next Cell
Sub Array_test()
Dim Genesys_Data(), Matrix_Data(), MatrixList()
Lastrow = Sheets("Genesys Raw").Range("C1000000").End(xlUp).Row
Set Genesys_region = Sheets("Genesys Raw").Range("C3:J" & Lastrow)
Genesys_Data = Genesys_region.Value2
MatrixList = Sheets("Matrix Raw").Range("M1:M999").Value
Matrix_Data = Sheets("Matrix Raw").Range("C1:M999").Value2
For lngrow = 1 To Genesys_region.Rows.Count
thevalue = Trim(Genesys_Data(lngrow, 1))
If thevalue <> oldvalue Then
On Error Resume Next
matrixrow = WorksheetFunction.Match(thevalue, MatrixList, 0)
On Error GoTo 0
oldvalue = thevalue
End If
If matrixrow > 0 Then
team_code = Sheets("Matrix Raw").Range("I" & matrixrow).Value
With Sheets("Converted Data").Range("C" & (i + 4))
.Offset(0, 0).Value = Matrix_Data(matrixrow, 1)
.Offset(0, 1).Value = Matrix_Data(matrixrow, 10)
.Offset(0, 2).Value = Matrix_Data(matrixrow, 9)
.Offset(0, 3).Value = Matrix_Data(matrixrow, 11)
.Offset(0, 4).Value = thevalue
.Offset(0, 6).Value = Matrix_Data(matrixrow, 4)
.Offset(0, 7).Value = Genesys_Data(lngrow, 2)
.Offset(0, 8).Value = UCase(Genesys_Data(lngrow, 2))
.Offset(0, 9).Value = Genesys_Data(lngrow, 3)
.Offset(0, 10).Value = Genesys_Data(lngrow, 2) + Genesys_Data(lngrow, 4)
.Offset(0, 11).Value = Genesys_Data(lngrow, 2) + Genesys_Data(lngrow, 5)
.Offset(0, 12).Value = Genesys_Data(lngrow, 6) * 24 * 60
'.Offset(0, 15).Value = team_code
'.Offset(0, 16).Value = Application.WorksheetFunction.VLookup(team_code, Sheets("Matrix Raw").Range("R:S"), 2, False)
End With
i = i + 1
Else
' No match
End If
Next
End Sub
Sub Import_data()
' 1. Converts GENESYS Schedule State Report (XLS format) into MATRIX eWFM (CSV format)
' 2. Creates SHIFT and ADHRS container codes for MATRIX processing
' 3. Writes records to CSV file
'
' 20110223 - Created by Stephen Cockram
' 20110224 - Added Comments
'
Dim connectString, dirName, fileDate As String
Dim Filename As Variant
Filename = Application.GetOpenFilename()
If Filename = 0 Then
MsgBox ("Cancelled")
Exit Sub
End If
dirName = Left(Filename, InStrRev(Filename, "\"))
' filedate = Mid(Filename, 29, 8)
fileDate = "20110217"
connectString = "ODBC;DSN=Excel Files;DBQ=" & Filename & ";DefaultDir=" & dirName & ";DriverId=790;MaxBufferSize=2048;PageTimeout=5;"
Application.Calculation = xlCalculationManual
With Sheets("Genesys Raw").Range("A3").ListObject.QueryTable
.Connection = connectString
.Refresh
End With
With Sheets("MATRIX Raw")
.Range("A2").ListObject.QueryTable.Refresh
.Range("R2").ListObject.QueryTable.Refresh
End With
Application.Calculation = xlCalculationAutomatic
Call Convert_data
' **** CREATE subroutine to remove unknown agents ****
Call Savethecsv(fileDate)
End Sub
Sub Array_test()
Dim Genesys_Data(), Matrix_Data(), MatrixList(), output_data()
i = 1
Lastrow = Sheets("Genesys Raw").Range("C1000000").End(xlUp).Row
Set Genesys_region = Sheets("Genesys Raw").Range("C3:J" & Lastrow)
Genesys_Data = Genesys_region.Value2
MatrixList = Sheets("Matrix Raw").Range("M1:M999").Value
Matrix_Data = Sheets("Matrix Raw").Range("C1:M999").Value2
For lngrow = 1 To Genesys_region.Rows.Count
thevalue = Trim(Genesys_Data(lngrow, 1))
If thevalue <> oldvalue Then
On Error Resume Next
matrixrow = WorksheetFunction.Match(thevalue, MatrixList, 0)
On Error GoTo 0
oldvalue = thevalue
End If
If matrixrow > 0 Then
team_code = Matrix_Data(matrixrow, 7)
ReDim Preserve output_data(16, i)
output_data(1, i) = Matrix_Data(matrixrow, 1)
output_data(2, i) = Matrix_Data(matrixrow, 10)
output_data(3, i) = Matrix_Data(matrixrow, 9)
output_data(4, i) = Matrix_Data(matrixrow, 11)
output_data(5, i) = thevalue
output_data(6, i) = Matrix_Data(matrixrow, 4)
output_data(7, i) = Genesys_Data(lngrow, 2)
output_data(8, i) = UCase(Genesys_Data(lngrow, 2))
output_data(10, i) = Genesys_Data(lngrow, 3)
output_data(11, i) = Genesys_Data(lngrow, 2) + Genesys_Data(lngrow, 4)
output_data(12, i) = Genesys_Data(lngrow, 2) + Genesys_Data(lngrow, 5)
output_data(13, i) = Genesys_Data(lngrow, 6) * 24 * 60
'.Offset(0, 15).Value = team_code
'.Offset(0, 16).Value = Application.WorksheetFunction.VLookup(team_code, Sheets("Matrix Raw").Range("R:S"), 2, False)
Debug.Print i
i = i + 1
Else
' No match
End If
Next
Debug.Print "End"
output_data = Application.Transpose(output_data)
Sheets("Converted_Data").Range("C4").Resize(UBound(output_data), UBound(Application.Transpose(output_data))) = output_data
Title | # Comments | Views | Activity |
---|---|---|---|
Auto Operation of MS Excel 2013 | 2 | 29 | |
Excel- VBA help on macros that size columns and rows | 7 | 21 | |
Excel Macro - Lookup-Highlight | 4 | 24 | |
Relative link_location when pasting a =HYPERLINK() formula to a different cell in Excel | 7 | 17 |
Join the community of 500,000 technology professionals and ask your questions.
Connect with top rated Experts
21 Experts available now in Live!