View Single Post
  #4   Report Post  
jerry_maguire jerry_maguire is offline
Junior Member
 
Posts: 25
Default Try this:

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

You can edit the values accordingly.

Hope this helps.

Regards,
Jerry