Could be simplified to remove selections
Range("c" & Rows.Count).End(xlUp).name="Lastc"
Range("D" & Rows.Count).End(xlUp).name="LastD"
Range("e" & Rows.Count).End(xlUp).name="Laste"

Range("G2").FormulaR1C1 = "=LastD-R[3]C[-3]"
Range("H2").FormulaR1C1 = "=LastE-R[3]C[-3]"
Range("I2").FormulaR1C1 = "=POWER(RC[-2],2)"
Range("J2").FormulaR1C1 = "=POWER(RC[-2],2)"
Range("K2").FormulaR1C1 = "=RC[-2]+RC[-1]"
Range("L2").FormulaR1C1 = "=SQRT(RC[-1])"
Range("M2").FormulaR1C1 = "=LastC-R[3]C[-10]"
Range("N2").FormulaR1C1 = "=RC[-2]/RC[-1]"



--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"James" wrote in message
...
Your code worked great now that I figured out how to use it.

Now the portion that I was trying to figure out looks like this now:

ActiveWorkbook.Names.Add Name:="LastD", _
RefersToR1C1:=Range("D" & Rows.Count).End(xlUp)
ActiveWorkbook.Names.Add Name:="LastE", _
RefersToR1C1:=Range("E" & Rows.Count).End(xlUp)
ActiveWorkbook.Names.Add Name:="Lastc", _
RefersToR1C1:=Range("C" & Rows.Count).End(xlUp)



Range("G2").Select
ActiveCell.FormulaR1C1 = "=LastD-R[3]C[-3]"
Range("H2").Select
ActiveCell.FormulaR1C1 = "=LastE-R[3]C[-3]"
Range("I2").Select
ActiveCell.FormulaR1C1 = "=POWER(RC[-2],2)"
Range("J2").Select
ActiveCell.FormulaR1C1 = "=POWER(RC[-2],2)"
Range("K2").Select
ActiveCell.FormulaR1C1 = "=RC[-2]+RC[-1]"
Range("L2").Select
ActiveCell.FormulaR1C1 = "=SQRT(RC[-1])"
Range("M2").Select
ActiveCell.FormulaR1C1 = "=LastC-R[3]C[-10]"
Range("N2").Select
ActiveCell.FormulaR1C1 = "=RC[-2]/RC[-1]"

Thanks so much for pointing me in the right direction.

"OssieMac" wrote:

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.