VBA memory error

[Vbalearner85]

I have a simple VBA code as below to copy some formulas and then do some more steps. This throws vba memory error either on ".value" or ".numberformat" steps. Memory usage/CPU usage suddenly jumps like 90%. Not sure if I am doing something fundamentally wrong. Using 2016 Excel version on windows 10. Please advise

I then run this code in a loop on a large file(300MB) to format around 150+ sheets.

Sub Format()
   
Dim lr As Long, lastrow As Long
Application.ScreenUpdating = False
   

lr = Cells(Rows.Count, "BH").End(xlUp).Row
       
Range("BR1").Value = "MP"
Range("BS1").Value = "PEP"
 
Range("BR2").Formula = "=IF(AND(OR(AY2=""LP"",AY2=""HP""),BA2>$BZ$1),BH2-BF2,"" "")"
If lr > 2 Then Range("BR2").AutoFill Destination:=Range("BR2:BR" & lr)
Range("BS2").Formula = "=IF(AND(OR(AY2=""LP"",AY2=""HP""),BA2>$BZ$1),BE2-BF2,"" "")"
If lr > 2 Then Range("BS2").AutoFill Destination:=Range("BS2:BS" & lr)
ActiveSheet.Calculate
Columns("BR").Value = Columns("BR").Value
Columns("BS").Value = Columns("BS").Value
Intersect(Range("2:" & lr), Range("BR:BS")).NumberFormat = "##,##0.0;[Red](##,##0.0)"
Columns("BR:BS").EntireColumn.AutoFit

Application.ScreenUpdating = True

End Sub

 

[StephenCrump]

Does this work any better?

With Range("BR2:BR" & lr)
    .Formula = "=IF(AND(OR(AY2=""LP"",AY2=""HP""),BA2>$BZ$1),BH2-BF2,"" "")"
    .Value = .Value
    .NumberFormat = "##,##0.0;[Red](##,##0.0)"
    .EntireColumn.AutoFit
End With
    
With Range("BS2:BS" & lr)
    .Formula = "=IF(AND(OR(AY2=""LP"",AY2=""HP""),BA2>$BZ$1),BE2-BF2,"" "")"
    .Value = .Value
    .NumberFormat = "##,##0.0;[Red](##,##0.0)"
    .EntireColumn.AutoFit
End With

If lr is particularly big (?) we could also break the columns into smaller chunks, setting formulae and replacing with values inside a simple loop.

It might work, but we've ignored the 300MB elephant in the room. This Sub could just be the final straw.

Your other code might also be chewing up memory.

 

[Vbalearner85]

Thanks for the edits...but as you mentioned issue is 300 MB elephant....not sure if upgrading from 8GB to 16 GB RAM would be of any help.

 

[Joe4]

Thanks for the edits...but as you mentioned issue is 300 MB elephant....not sure if upgrading from 8GB to 16 GB RAM would be of any help.

The mention of copying formulas to 150+ sheets leads me to believe that what you really have is a relational database, in which case, it would probably be better served in a program designed for relational databases, like Microsoft Access or SQL.

People often try Excel for such things, and many times it *can* be done sometimes, but Excel isn't really the right tool for the project (as that is not what Excel was designed for), so performance often takes a bit hit if (and when) the data really gets to be large.

 

[Vbalearner85]

The mention of copying formulas to 150+ sheets leads me to believe that what you really have is a relational database, in which case, it would probably be better served in a program designed for relational databases, like Microsoft Access or SQL.

People often try Excel for such things, and many times it *can* be done sometimes, but Excel isn't really the right tool for the project (as that is not what Excel was designed for), so performance often takes a bit hit if (and when) the data really gets to be large.

Joe your opinion seems to be right..but I am more acquainted with Excel..and have least idea of other MS Tolls

 

[Joe4]

Joe your opinion seems to be right..but I am more acquainted with Excel..and have least idea of other MS Tolls

Yes, that is why a lot of people do it (or the do not have access to the other programs).
However, if it keeps growing, you may come to that breaking point where you need to make some sort of change because Excel just cannot handle it anymore.