Using a previous row's cell value

[MRTX7005]

I am struggling with figuring out the VBA code to use the cell value in the previous row in a formula.

The formula is applying a simple filter to data from an accelerometer that is a little noisy. The data is in column format. Trying to create the equation that basically states: New_filtered_Cell_Value = (ThetaX * Previous Row's Cell filtered Value) + (1-ThetaX) * Newly_read_cell's_value.

The ThetaX is a filtering number between 0 and 1.

'  G Force X Axis


    Dim LastRow5 As Long
    Dim Rng5 As Range
    Dim ThetaX As Long
    
    LastRow5 = Sheets("Data").Cells(Rows.Count, "J").End(xlUp).Row
    ThetaX = Sheets("Dashboard").Range("AF12").Value   'This is the filter strength value, will be between 0 to 1'
    
    Set Rng5 = Sheets("Data").Range("J2:J" & LastRow5)
    
    
    For Each cell In Rng5
           'cell.Value = (ThetaX * previous row's filtered cell value) + ((1 - ThetaX) * cell.Value)
    Next cell




   'Replace #Div/0 cells with other value for graphing purposes'
    For Each cell In Range("J2:J" & LastRow5)
        If IsError(cell) Then
          If cell.Value = CVErr(xlErrDiv0) Then cell.Value = 0
        End If
    Next cell

The part I am struggling on is how to get the "previous row's cell value" in the equation. This cell value will have the filter multiplier already added to it. The code above is just a starting point/not correct.


Also I added the replace #Div/0 with 0 assuming that the first cell value (J2) will give some odd value since there is not a previous cell value. J1 is a column title. Not sure if this is the correct way to handle. Thoughts?


Hope that makes sense. Any help would be appreciated.
MR

 

[JustynaMK]

Maybe using Offset will help:

cell.Value = (ThetaX * cell.Offset(-1, 0).Value) + ((1 - ThetaX) * cell.Value)

Also consider changing ThetaX datatype from Long to "Single" or "Double", as Long won't allow you to process values with decimal places (i.e. between 0 and 1).

 

[JustynaMK]

+ one more tip: Offset won't work for the first cell (J2), as I suspect cell J1 is your header. You might want to create additional condition for "cell" parameter in case it equals 2, like:

    For Each cell In Rng5
	If cell.Row = 2 then
		'different formula
	Else
           'cell.Value = (ThetaX * previous row's filtered cell value) + ((1 - ThetaX) * cell.Value)
	End if
    Next cell

 

[MRTX7005]

Using the offset worked.
Also changed the datatype to Single.
On the value for J2 and not really having a J1 value, I changed the equation slightly. The more I thought about it, the one data point for J2 in this application is not that important.

Thanks.
MR

 

[JustynaMK]

Cool, glad it worked for you! Take care.