Targets and Offsets

[Mhat]

Hi all,

I have a worksheet called “STATS” that has departure points in column “G” and arrival points in column “H”. It transfers the last value from column “G” to a cell on a sheet called DIST, and the last value from column H to another cell on the DIST worksheet. The worksheet then calculates the time and distance between the two points and returns the time value to column “I” and the distance value to column “R”. The code works okay to this point. One of the problems that I am having is that I originally had a formula in column “G”,

=IF(H24>0,H24,"")

that places the last value in column H into the next available cell in column G. Using the existing code it will not recognize and transfer the value the formula places in the cell.

The second issue is that when I make a mistake and delete a value that I have entered into column H the code offsets the values off of column G, which makes for quite a mess because other cells are now calculating values they shouldn’t be. Any help would be appreciated, the code is as follows:

'Places Apt. ID's in DIST sheet to determine distance and time between airports
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Row > 13 And Target.Column = 7 Then
Sheets("DIST").Range("D4").Value = Target
End If
If Target.Row > 13 And Target.Column = 8 Then
Sheets("DIST").Range("D5").Value = Target
End If
ActiveCell.Offset(0, 9).Value = Sheets("DIST").Range("E14").Value
Target.Offset(0, 1).Value = Sheets("WIND").Range("B17").Value
End If
End Sub