Using a previous row's cell value

MRTX7005

New Member
Joined
Aug 6, 2017
Messages
8
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.

Code:
'  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
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

JustynaMK

Well-known Member
Joined
Aug 28, 2016
Messages
673
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
Maybe using Offset will help:
Code:
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

Well-known Member
Joined
Aug 28, 2016
Messages
673
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
+ 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:
Code:
    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

New Member
Joined
Aug 6, 2017
Messages
8
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

Well-known Member
Joined
Aug 28, 2016
Messages
673
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
Cool, glad it worked for you! Take care.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,162
Messages
5,594,615
Members
413,917
Latest member
devansh02

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top