I have written the below reasonably simple code to iterate through and combine data from a couple of sheets into the format I am after for export.

Unfortunately it takes just under a second for each line, and I have 15000 lines. I have used all of my usual optimization standards but cannot seem to speed it up.

Need some suggestions to rewrite/improve. I am extremely familiar with VBA and comfortable with all concepts - looking for wiser heads than mine.

Sub Convert_data()' Converts Genesys Data to the correct format for loading'' 20110225 - Created by Nigel Rablin'Dim Genesys_region As RangeDim Cell As VariantDim Lastrow As LongDim 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 SubErrorhandler: matrixrow = 3434 Resume NextEnd Sub

It looks like you need
1) a 6 column, variable row length array for Gensys_region (as you use a 5 colum offset later)
2) lookup matrixrow as per your current code ( matrixrow = Application.WorksheetFunction.Match(Trim(Cell.Value), Sheets("Matrix Raw").Range("M1:M999"), 0))
3) a 17 column variable row length array to dump to Sheets("Converted Data").Range("C" & (i + 4))

If you have trouble then a sample file would help, but I think you will be ok implementing the array approach based on my article example

As per now you have a read range and output range
The arrays simply sit inside these two ranges and do the work
Cheers

Again great thinking, but no, I have no events running. There are two dynamic queries that run before this Macro, but I have turned them off and am simply running this Macro in isolation on just data.

The error Handler can be turned off, it basically sets a value for if the match is not found.

The plan for the error handler is to push out trash data into a separate sheet.

It will also only fire for that one line and I am comfortable for every error in that section to be piped elsewhere.

The code simply loops through some data and looks for a match in the "Username" in a different data set. - It then pulls data from the relevant sheet for the information it is after.

I was all good in the floods. My workplace was evacuated, but I basically work from my laptop through a wireless connection anyway, so I evacuated early and worked from my balcony watching the chaos

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

On error Goto 0 will actually turn normal error handling back on. So my Error Handler only happens on the lines of code above. The performance detriment for that line is almost non existant.

I know the error will appear here if it cannot fid a match. Normally I would use VBA find, but it does not work in this instance due to differing character types.

One thing I do when I have a lot of spreadsheet lookup, references, and math going on, is to paste the FORMULA (rather than value) on the first line, then one command copies that formula down a # of rows, and the last step to convert all formulas to values. It would be a quick thing to test and see if this helps before wrapping your head around a completely different approach (which may be the correct way, but I'm just saying :)

So you could Turn Calculations OFF, and do something like the below for all rows, or leave Calculations ON and do it for one row, copy down, then set formulas to values:

Since Cell is a vlookup output, you could code that formula in as well. You could do this for one row of output, then copy down the entire range. Alternatively (may or may not be a timesaver) you could plow all the formulas in with calculations off, then...

Then turn calculation on, select your output range and set selection.value = selection.value

One very quick way to test is to manually set the formulas in the range of cells, turn calc off, then copy down, then turn calc on. Did that process much faster or not?
Enjoy!

Dave

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

I am in the middle of coding a multidimensional array method. Appears to be quite good so far, just looking for a way to find a match in the array - assuming use of filter at the moment.

Agreed - very enlightening article - can't wait to turn your tips into real practice/knowledge.

Dave

0

Featured Post

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns overâ€¦

Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custoâ€¦