Excel: Update Cell with VBA or Macro Help

[dleewms]

I have an excel spreadsheet that shows days ahead of behind schedule for a project. I want my last cell in each row (F3) to be automatically updated based on the most recent (last) ahead/behind data.

For example in Job 1, the following fields are populated:
A3 = -15
B3 = -10
C3 = 15
D3 = Blank
E3 = Blank
In this example F3 should contain 15.

Job 2 has the following fields populated:
A4 = -12
B4 = -5
C4 = -15
D4 = -8
E4 = -6
In this example F3 should contain -6.

Basically, I need F# to contain the value in the cell the furthest to the right, if it is not blank. Any help would be greatly appreciated.

 

[Jorgandr]

You need to put this in your worksheet code

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim c As Integer
Dim r As Integer

'column number to start looking for not empty values (1 less than column F)
c = 5

'Gets the row number of the cell you have entered/changed and stores it in variable r
r = Target.Row

Do While c > 0
If Not IsEmpty(ActiveSheet.Columns(c).Cells(r).Value) Then
ActiveSheet.Columns("F&quot.Cells(r).Value = ActiveSheet.Columns(c).Cells(r).Value
Exit Sub
End If
c = c - 1
Loop
End Sub

 

[Jorgandr]

You'll want to add the following code after the loop to set F# to null if you delete all values in that row

If c = 0 Then
Cells(r, "F&quot.Value = Null
End If

 

[dleewms]

Thanks so much. I was able to accomplish my goal using the following formula: =OFFSET(<startvalue>,0,MATCH(MAX(<range>)+1,<range>,1)-1).

When I get a moment, I will try it with the code you so graciously provided.