I have a spreadsheet that takes known data from ~500 companies, and their percent of market share in various geographic markets, and then creates a virtual model of the geographic area based on that information to provide things like average, standard deviation, median, etc.

The macro I'm writing sorts a pivot table to display the geographic area in question, pulls twelve points of associated data (per company) from a table, then creates the model. I seem to be running into trouble at the part where I'm pulling in data. At 500 lines and twelve data points, that's only 12,000 cells to populate. I was using VLOOKUP to accomplish this, but I need to trim the sheet down to size (6 MB) and speed up the macro (depending on user, a couple of minutes per analysis).

I replaced the VLOOKUP with a FIND feature, but it's still pretty slow. Here's the loop in question:

- For i = 1 To 700
- If Sheets("Model").Range("A33").Offset(i, 0).Value = "" Then Exit For
- Sheets("Model").Range("E33").Offset(i, 0).Value = Sheet2.Range("RawData").Find(What:= _
- Sheets("Model").Range("A33").Offset(i, 0).Value, after:=Sheet2.Range("RawData").Cells(1, 1), _
- LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByRows, searchdirection:=xlNext, _
- MatchCase:=False).Offset(0, 1)
- Sheets("Model").Range("F33").Offset(i, 0).Value = Sheet2.Range("RawData").Find(What:= _
- Sheets("Model").Range("A33").Offset(i, 0).Value, after:=Sheet2.Range("RawData").Cells(1, 1), _
- LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByRows, searchdirection:=xlNext, _
- MatchCase:=False).Offset(0, 2)
- Sheets("Model").Range("G33").Offset(i, 0).Value = Sheet2.Range("RawData").Find(What:= _
- Sheets("Model").Range("A33").Offset(i, 0).Value, after:=Sheet2.Range("RawData").Cells(1, 1), _
- LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByRows, searchdirection:=xlNext, _
- MatchCase:=False).Offset(0, 3)
- Sheets("Model").Range("H33").Offset(i, 0).Value = Sheet2.Range("RawData").Find(What:= _
- Sheets("Model").Range("A33").Offset(i, 0).Value, after:=Sheet2.Range("RawData").Cells(1, 1), _
- LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByRows, searchdirection:=xlNext, _
- MatchCase:=False).Offset(0, 4)
- Sheets("Model").Range("I33").Offset(i, 0).Value = Sheet2.Range("RawData").Find(What:= _
- Sheets("Model").Range("A33").Offset(i, 0).Value, after:=Sheet2.Range("RawData").Cells(1, 1), _
- LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByRows, searchdirection:=xlNext, _
- MatchCase:=False).Offset(0, 5)
- Sheets("Model").Range("J33").Offset(i, 0).Value = Sheet2.Range("RawData").Find(What:= _
- Sheets("Model").Range("A33").Offset(i, 0).Value, after:=Sheet2.Range("RawData").Cells(1, 1), _
- LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByRows, searchdirection:=xlNext, _
- MatchCase:=False).Offset(0, 6)
- Sheets("Model").Range("K33").Offset(i, 0).Value = Sheet2.Range("RawData").Find(What:= _
- Sheets("Model").Range("A33").Offset(i, 0).Value, after:=Sheet2.Range("RawData").Cells(1, 1), _
- LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByRows, searchdirection:=xlNext, _
- MatchCase:=False).Offset(0, 7)
- If Sheets("Model").Range("F33").Offset(i, 0).Value = "" Then
- 'Do Nothing
- Else
- 'MsgBox "Just before Labor"
- If Sheets("Model").Range("F33").Offset(i, 0).Value = "Fix" Then
- 'MsgBox "fix labor"
- Sheets("Model").Range("L33").Offset(i, 0).Value = Sheets("Model").Range("G33").Offset(i, 0).Value
- End If
- If Sheets("Model").Range("F33").Offset(i, 0).Value = "Hourly" Then
- 'MsgBox "Hourly Labor"
- Sheets("Model").Range("L33").Offset(i, 0).Value = Sheets("Model").Range("H33").Offset(i, 0).Value _
- * Sheets("Model").Range("C4").Value
- End If
- If Sheets("Model").Range("F33").Offset(i, 0).Value = "Combo" Then
- 'MsgBox "Combo Labor"
- Sheets("Model").Range("L33").Offset(i, 0).Value = Sheets("Model").Range("H33").Offset(i, 0).Value _
- * Sheets("Model").Range("C4").Value + Sheets("Model").Range("I33").Offset(i, 0).Value
- End If
- Sheets("Model").Range("M33").Offset(i, 0).Value = Sheets("Model").Range("C3").Value * (1 - _
- Sheets("Model").Range("E33").Offset(i, 0).Value) + Sheets("Model").Range("C2").Value
- If Sheets("Model").Range("K33").Offset(i, 0).Value = "Flat" Then
- Sheets("Model").Range("M33").Offset(i, 0).Value = Sheets("Model").Range("M33").Offset(i, 0).Value + _
- Sheets("Model").Range("J33").Offset(i, 0).Value
- Else
- Sheets("Model").Range("M33").Offset(i, 0).Value = Sheets("Model").Range("M33").Offset(i, 0).Value + _
- (Sheets("Model").Range("J33").Offset(i, 0).Value * Sheets("Model").Range("C1").Value)
- End If
- Sheets("Model").Range("N33").Offset(i, 0).Value = Sheets("Model").Range("M33").Offset(i, 0).Value + _
- Sheets("Model").Range("L33").Offset(i, 0).Value
- Sheets("Model").Range("O33").Offset(i, 0).Value = Sheets("Model").Range("L33").Offset(i, 0).Value / _
- Sheets("Model").Range("C4").Value
- If Sheets("Model").Range("K33").Offset(i, 0).Value = "Flat" Then
- Sheets("Model").Range("P33").Offset(i, 0).Value = Sheets("Model").Range("J33").Offset(i, 0).Value
- Else
- Sheets("Model").Range("P33").Offset(i, 0).Value = Sheets("Model").Range("J33").Offset(i, 0).Value * _
- Sheets("Model").Range("C1").Value
- End If
- End If
- Next i

Display More

Am I terribly inefficient here? I'm sorry to offer such a broad question, but I feel like there are more efficient ways of pulling data from one worksheet to another, but I don't know how.