VBA Evaluate Formula and "Copy Down" Result as String/Value to Last Row

**falkon007** · 04-21-2013, 11:25 PM

Hi there,
I have a piece of code which is working beautifully but at 500000 lines it is occasionally causing out of Memory issues when I run some other macros after it because it stores the actual formula in the cell. I wish for the Macro to evaluate the formula in relevant to the row it is in but only paste the value as text in the cell instead of having 6 coloumns * 500000 lines of formula saved in cells:

Here is the code:

Sub ConcatFullImport()
Dim LastRow As Long
    Dim Ws As Worksheet
    Application.ScreenUpdating = 0

    Set Ws = Sheets("FullImport")
    If Ws.Range("G13").Value = "" Then Exit Sub
    Ws.Range("A13:F" & Rows.Count).ClearContents

    LastRow = Ws.Range("AS" & Ws.Rows.Count).End(xlUp).Row
    Ws.Range("A13:A" & LastRow).Formula = "=IF(ISNA(MID(INDEX(Data!$D$4:$D$20,MATCH(CONCATENATE(C13,D13,E13),Data!$C$4:$C$20,0)),1,4)),"""",MID(INDEX(Data!$D$4:$D$20,MATCH(CONCATENATE(C13,D13,E13),Data!$C$4:$C$20,0)),1,4))"
    Ws.Range("B13:B" & LastRow).Formula = "=IF(ISNA(MID(INDEX(Data!$B$4:$B$20,MATCH(CONCATENATE(C13,D13,E13),Data!$C$4:$C$20,0)),1,50)),"""",MID(INDEX(Data!$B$4:$B$20,MATCH(CONCATENATE(C13,D13,E13),Data!$C$4:$C$20,0)),1,50))"
    Ws.Range("C13:C" & LastRow).Formula = "=IF(K13="""","""",K13)"
    Ws.Range("D13:D" & LastRow).Formula = "=IF(L13="""","""",L13)"
    Ws.Range("E13:E" & LastRow).Formula = "=IF(CONCATENATE(C13,D13)=""UR"","""",IF(S13="""","""",S13))"
    Ws.Range("F13:F" & LastRow).Formula = "=CONCATENATE(H13,I13,J13)"
    
    Application.ScreenUpdating = True
    Ws.Calculate
End Sub

Is it possible to manipulate this VBA to paste only the values from Row 1 to 500000 down coloumns A to F?

**falkon007** · 04-22-2013, 12:01 AM

In other worlds if I could make coloumn E be:

Ws.Range("E13:E" & LastRow).Formula.Result = "=IF(CONCATENATE(C13,D13)=""UR"","""",IF(S13="""","""",S13))"

or

Ws.Range("E13:E" & LastRow).Formula.Value = "=IF(CONCATENATE(C13,D13)=""UR"","""",IF(S13="""","""",S13))"

So then the contens of E13 would be the result of the formula above ....and the contents of E500000 would be the result of the formula:

=IF(CONCATENATE(C500000,D500000)=""UR"","""",IF(S500000="""","""",S500000)

(I know this isn't a real coding possibility but you may understand what I wish to achieve.)

Cheers,

**JosephP** · 04-22-2013, 03:04 AM

you could try simply entering the formula then converting it to a value. also since you're using 2010 or later you can use IFERROR

   With Ws.Range("A13:A" & LastRow)
.Formula = "=IFERROR(MID(INDEX(Data!$D$4:$D$20,MATCH(CONCATENATE(C13,D13,E13),Data!$C$4:$C$20,0)),1,4),"""")
.Value = .Value
End With

for example.