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
Bookmarks