Is there a smarter way to do this: 1-Copy Formula >> 2-Paste Formula Down >> 3-Hardcode Pasted Range process I use frequently?

[d0rian]

I have a 30-MB file (8 sheets, some with 80,000 rows) I try to keep as trim and nimble as possible, but it's gotten really laggy and crashes frequently now. I'm rebuilding it from scratch and hoping to discover ways to improve some processes that perhaps I've been doing a dumb/slow way. The one I use most often is a copy/paste-formulas/copy/paste-values process, because with 80,000+ rows, I try to keep as much of the sheet hardcoded wherever possible. So a typical snippet of VBA looks like this:

Sub CPHC()
    Application.ScreenUpdating = False
    With Sheets("cad")
        .Range("formula_source").Copy
        .Range(Range("pasterange.C").Value).PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        .Range(Range("pasterange.C").Value).Copy
        .Range(Range("pasterange.C").Value).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
    End With
    Application.ScreenUpdating = True
    Application.CutCopyMode = False
End Sub

Hopefully it's self-explanatory, but it:

1. Copies a range (e.g. "formula_source" = D1:N1) that has the formula I want to paste to 80,000 rows
2. Pastes the formula to the desired range I need to run calculations on (e.g. D2:N80000)
3. Then Copies/Pastes that target range (D2:N80000) so that those values are hardcoded, not live formulas (otherwise, I'd have 80,000 formulas recalculating all the time, and the file would be unusable.)

Depending on the formulas I'm pasting down, the above can take 5-30 seconds, and I want to know whether there's a much speedier way to be doing this sort of process. Ideas I've had (which I'm not sure exist):

1. Is there any Excel function that -- in a single step -- will paste the hardcoded RESULTS of formulas, rather than the live formula itself? Theoretically that would eliminate step #3 above, and I wouldn't have to copy the target D2:N80000 range and paste-values. I don't think so, but if there is, that might cut this process in half.

2. Is there a smart way to instead accomplish the above with VBA where...like, all the calculations are done "in memory" or something? My code above essentially just automates the copy/paste-down/hardcode-values process (with screen-updating turned off), but can I somehow use VBA to, I dunno, make all of those calculations happen in the background rather than in the Sheet cells themselves, and just have the RESULTS pasted in the target range?

Open to any and all ideas!!

 

[offthelip]

I finally got back to this thread, if you wanted to do the whole thing in memory one very fast way of doing index/ match in memory is using the dictionary object. this code should do the index mathc that you posted and it take 100 milliseconds to run on my machine:

Sub dictionary()
 ' this does an index match by using the dictionary object
   Dim Ary As Variant
   Dim i As Long
   Dim Dic As Object
   tt = Timer()
   
   Dim outarr(1 To 60000, 1 To 1)
   Set Dic = CreateObject("Scripting.dictionary")
   With Sheets("sheet1")
      Ary = .Range("A2", .Range("A" & Rows.Count).End(xlUp).Offset(, 4)).Value2
   For i = 1 To UBound(Ary)
      Dic(Ary(i, 1)) = Ary(i, 3)
   Next i
   
   For i = 1 To 59999
    outarr(i, 1) = Dic(Ary(i, 4))
   Next i
   .Range("G2:g6000") = outarr
   End With
   
   ts = Timer()
   MsgBox 1000 * (ts - tt)
   End Sub

 

[offthelip]

I spotted a slight error:

 .Range("G2:g6000") = outarr

should be

 .Range("G2:g60000") = outarr

which increased the time to 121 milliseconds!!
Note I put the out put in column G so that I could compare it with formula values.