Reputation: 194

Get closest cell with a value before the current

In a column filled mostly by blank cells, I need to get the value of the cell thats closest to the currently selected one, in a row before it, and isn't blank.

While the "isn't blank" part can be easily achieved with the an IF and ISBLANK statement, I can't figure out how to get the position of the first cell upwards from the current one that isn't blank.

In this example spreadsheet, in the cell C7 I want to display the value of B7 minus the value of B4 (=B7-B4). The problem is that these values are separated by an unpredictable number of cells, which can range from 0 to 20 or even more. Needing to parse a spreadsheet with thousands of rows and frequent data added, selecting the upper cell manually is not an option.

In continuation of my example, I want C13 to display =B13-B10, but lets say there were two more blank rows in between them (row 13 becomes row 15), I would want it to display =B15-B10.

Can the position be obtained with a MATCH query, knowing that the Types will always be T1 or T2?

Thank you in advance.

Upvotes: 0

Answers (3)

T.M.

Reputation: 9948

Writing formulae via VBA

Below you find a VBA approach writing your subtracting formulae to column C. BTW, searching via array is much more quicker than looping through a range.

Code

Option Explicit

Sub subtractLastValueRow()
' declare vars
  Dim oSht    As Worksheet                       ' work sheet
  Dim a       As Variant                         ' one based 2-dim data field array
  Dim n       As Long                            ' last row in column B
  Dim i       As Long                            ' item no
  Dim ii      As Long                            ' last item no
  Dim j       As Long
  Dim s       As String
' set sheet
  Set oSht = ThisWorkbook.Worksheets("MySheet")  ' fully qualified reference to worksheet
' get last row number of search column
  n = oSht.Range("B" & oSht.Rows.Count).End(xlUp).Row
  If n < 2 Then Exit Sub                         ' only if data avaible (row 1 = title line)

' get range (after title line) values to one based 2dim data field array
  a = oSht.Range("B2:C" & n).Value               '  array gets data from e.g. "A2:A100"
' loop through column B to find keyword sKey
  If Len(a(1, 1) & "") > 0 Then ii = 1 + 1       '  first item in array
  For i = LBound(a) + 1 To UBound(a)             '  array boundaries counting from 1+1 to n -1 (one off for title line)
      ' value found
        If Len(a(i, 1) & "") > 0 Then
           For j = i + 1 To UBound(a)
               If Len(a(j, 1) & "") > 0 Then
                ' write .Formula (alternatively use .Value, if value wanted)
                  oSht.Range("C" & i + 1).Formula = "=B" & i + 1 & "-B" & ii
                  ii = i + 1                      '  note last found i
                  Exit For
                End If
           Next j
        End If
  Next
  If Len(a(UBound(a), 1) & "") > 0 Then           ' last item in array
     oSht.Range("C" & UBound(a) + 1).Formula = "=B" & UBound(a) + 1 & "-B" & ii
  End If
End Sub

Note

If you want to write values instead of work sheet formulae, simply replace .Formula with .Value.

Upvotes: 1