Optimizing Code

Hi,

I have a worksheet where I calculate the Z score of around 4 million cells. To speed it up I have a column of all row averages and standard deviations (ThisWorkbook.numofcolumns+2 and +3). It is still really slow so I put a loading screen. The loading screen is updated by the variable Percent. I know when setting formulas that I should do the full range at once for speed but I could not figure out how to update the loading screen at the same time (the only thing I could think of was a modeless userform offscreen with a timer). I was wondering if someone could help me a) speed it up and/or b) update the variable Percent while it calculates the Z score. Thanks!

With ThisWorkbook.Worksheets("Z score")
    
    For i = 2 To ThisWorkbook.numofcolumns + 1

    .Range(.Cells(2, i), .Cells(ThisWorkbook.numofrows + 1, i)).FormulaR1C1 = "=(('Gene Expression'!RC-'Gene Expression'!RC" & ThisWorkbook.numofcolumns + 2 & ")/'Gene Expression'!RC" & ThisWorkbook.numofcolumns + 3
  'Update loading screen
    Percent = (i + 50) / (ThisWorkbook.numofcolumns + 200)
    With Progress
            .FrameProgress.Caption = Format(Percent, "0%")
            .LabelProgress.Width = Percent * (.FrameProgress.Width - 10)
        End With

    DoEvents
    
    Next i

Hi tucanj,

If you supply a small sample of your data we could see what you are doing and be a lot more help. You can attach a sample file by clicking on Go Advanced below the message area and then on the Paper Clip Icon above the Advanced Message Area. You should also give an answer or two on how and where you want to see those z scores.