How to combined standard deviation with offset and last cell in VBA excel

Question

I currently have a daily report at work where I have to obtain the standard deviation of the last cell and its 364 previous observations. However, I would like to find a way to do it by VBA.

I would really appreciate any advice you could give me.

So far, I have tried the following code (I am new to VBA so please excuse me if it is a really easy question):

Sheets("Hoja1").Select
lastvalue=Range("A1").End(xlDown).Select
Cells (2,4)=Application.WorksheetFunction.StDev(Application.WorksheetFunction.Offset(lastvalue,0,0,-365))

Is the code you posted working for you? If not, what error are you getting? — PeterT, Apr 27 '20 at 21:08
Read this: https://stackoverflow.com/questions/38882321/better-way-to-find-last-used-row — HackSlash, Apr 27 '20 at 21:09

score 0 · Answer 1 · answered Apr 27 '20 at 21:27

0

Assuming you have at least 356 rows of data....

Dim rng As Range

With Sheets("Hoja1")
    Set rng = .cells(.rows.count, 1).end(xlUp).Offset(-364,0).Resize(365, 1)
    .Cells(2, 4).Value = Application.StDev(rng)
End With

answered Apr 27 '20 at 21:27

Tim Williams

154,628
8
97
125

How to combined standard deviation with offset and last cell in VBA excel

1 Answers1