# 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

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Replies
21
Views
850
Replies
1
Views
550
Replies
6
Views
80
Replies
12
Views
288
Replies
3
Views
65

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,152,817
Messages
5,772,456
Members
425,760
Latest member
paphon

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

### Which adblocker are you using?

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

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