Hi James,

I am not sure if I really understand what you are trying to do so I don't
know how much this information will help. However, the following code finds
the last cell in column C and then names it. You can then use the name in
formulas in the worksheet in lieu of the cell address. (Note that space and
underscore at the end of a line is a break in an otherwise single line of
code.)

ActiveWorkbook.Names.Add Name:="LastC", _
RefersToR1C1:=Range("C" & Rows.Count).End(xlUp)

Don't confuse named cells with VBA variables. Named cells are saved with the
workbook. Cells can be named on the worksheet without VBA. Look up Name cells
in help for more info.

If you place the above somewhere in your code, if the position of the named
cell changes, then the code simply changes the reference to the last used
cell in column C

You use named cells in your worksheet formulas in lieu of the actual cell
address.

Example: =C9-C8 can be written as =LastC-C8 after the last cell has been
named.

Feel free to get back to me because like I said "Not sure I fully understand".

--
Regards,

OssieMac


"James" wrote:

I have the below code which is basically calculating the pythagorean theorem
for each line of data.

Project kirk082d
Line BH28
Trace inc 1
Line Name Shotpoint Trace X Y
BH28____________ 4 2 375073.33 3971996.55
BH28____________ 4.25 3 375113.09 3972026.33
BH28____________ 4.5 4 375152.85 3972056.11
BH28____________ 4.75 5 375192.62 3972085.88
BH28____________ 5 6 375232.38 3972115.66
The following are how the columns are layed out:
Line Name - Column A
ShotPoint - Column B
Trace - Column C
X - Column D
Y - Column E

Here is my code which works fine:

Sub Calc_Trace_Spacing()
'
Dim LR As Long

Range("G4").Select
ActiveCell.FormulaR1C1 = "Delta X"
Range("H4").Select
ActiveCell.FormulaR1C1 = "Delta Y"
Range("I4").Select
ActiveCell.FormulaR1C1 = "Delta X sqt"
Range("J4").Select
ActiveCell.FormulaR1C1 = "Delta Y sqt"
Range("K4").Select
ActiveCell.FormulaR1C1 = "H sqt"
Range("L4").Select
ActiveCell.FormulaR1C1 = "H - Total Length of line in meters"
Range("M4").Select
ActiveCell.FormulaR1C1 = "Total Num Traces"
Range("N4").Select
ActiveCell.FormulaR1C1 = "Trace Spacing"
Range("G5").Select
ActiveCell.FormulaR1C1 = "=R[1]C[-3]-RC[-3]"
Range("H5").Select
ActiveCell.FormulaR1C1 = "=R[1]C[-3]-RC[-3]"
Range("I5").Select
ActiveCell.FormulaR1C1 = "=POWER(RC[-2],2)"
Range("J5").Select
ActiveCell.FormulaR1C1 = "=POWER(RC[-2],2)"
Range("K5").Select
ActiveCell.FormulaR1C1 = "=RC[-2]+RC[-1]"
Range("L5").Select
ActiveCell.FormulaR1C1 = "=SQRT(RC[-1])"
Range("M5").Select
ActiveCell.FormulaR1C1 = "=R[1]C[-10]-RC[-10]"
Range("N5").Select
ActiveCell.FormulaR1C1 = "=RC[-2]/RC[-1]"
Range("O5").Select

LR = Range("A" & Rows.Count).End(xlUp).Row
Range("G5:N5").AutoFill Destination:=Range("G5:N" & LR)

Columns("G:N").Select
Columns("G:N").EntireColumn.AutoFit

End Sub

The code will do the calculations for every line of data, however I would
like to expand on that and have the code do the calculations for the last row
of data and the first row of data. This si the part that I don't know how to
do, because I do not know where the last row of data is at for every file.

For delta x the formula would be DX - D5 (data always starts on row 5). For
delta y the formula would be EX - E5. The formulas for columns I through L
and N would all be the same as in the code that I have, but the formula for
column M would be CX - C5. My 'X' is the variable because I do not know
where the last row of data will be at every file is different.

Thank you for the help.