# Targets and Offsets

#### Mhat

##### Active Member
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”,
Code:
``=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:

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

### Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Replies
6
Views
188
Replies
0
Views
170
Replies
1
Views
236
Replies
1
Views
463
Replies
0
Views
241

1,186,172
Messages
5,956,359
Members
438,248
Latest member
Poysenberries

### 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?

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