ExcelBanter - Help with my VBA and formula

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Help with my VBA and formula (https://www.excelbanter.com/excel-programming/423362-help-my-vba-formula.html)

James

Help with my VBA and formula

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.

OssieMac

Help with my VBA and formula

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.

James

Help with my VBA and formula

I am trying to work with your line of code right now, but my VBA is not very
good, so I have been looking at other examples too. Let me try to explain
what I am doing again.

I have data that always starts on row 5 with header data in rows 1 thru 4.
The original data starts in columnA to columnE. I have the code that
calculates the pythagorean theorem for each line of the data using the
original data from columnA to columnE. The code places the calculations in
columnG thru columnN. This parts works fine, however I would like to expand
on this.

After the calculations are made for all the rows of data from row 5 to some
unknown row, I would like to calculate the pythagorean theorem using the last
row of data and row 5 (numbers all come from original data columnA thru
columnE).

I am unsure how to apply my formulas to this code when I do not know the
last row of data and how to tell it to put the formulas in the row after my
calculations have been done from my original code. Basically the last part
of what I want it to do is to place the formulas in columnG thru columnN but
calculating the last row of data from row 5.

I hope this better explains it. Thanks for your help.

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

James

Help with my VBA and formula

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.

Don Guillett

Help with my VBA and formula

OssieMac

Help with my VBA and formula

Hi James,

My testing placed your code in row 2. Perhaps that was just your method of
testing. I think that your code should be something like the following to
select all the last row cells for the formulas.

Probably not necessary with your particular project but when you want to
refer to a specific row irrespective of the position of the formula on the
worksheet you should make the row absolute with a $ sign in front of the row.
Check the formulas in the last row after the code runs.

the code is not as I would do it but I have tried to keep it so that you
will understand what is occurring with the method of selecting the last cells
for the formulas.

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)

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("G" & Rows.Count).End(xlUp).Select
ActiveCell.FormulaR1C1 = "=LastD-R5C[-3]"
Range("H" & Rows.Count).End(xlUp).Select
ActiveCell.FormulaR1C1 = "=LastE-R5C[-3]"
Range("I" & Rows.Count).End(xlUp).Select
ActiveCell.FormulaR1C1 = "=POWER(RC[-2],2)"
Range("J" & Rows.Count).End(xlUp).Select
ActiveCell.FormulaR1C1 = "=POWER(RC[-2],2)"
Range("K" & Rows.Count).End(xlUp).Select
ActiveCell.FormulaR1C1 = "=RC[-2]+RC[-1]"
Range("L" & Rows.Count).End(xlUp).Select
ActiveCell.FormulaR1C1 = "=SQRT(RC[-1])"
Range("M" & Rows.Count).End(xlUp).Select
ActiveCell.FormulaR1C1 = "=Lastc-R5C[-10]"
Range("N" & Rows.Count).End(xlUp).Select
ActiveCell.FormulaR1C1 = "=RC[-2]/RC[-1]"

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

End Sub

--
Regards,

OssieMac

James

Help with my VBA and formula

This works out as well, however after I got thinking about I figured it would
be nice to see the total calculation for the line at the top of the
spreadsheet and not at the bottom. Either way works, thanks for getting me
to that point appreciate the help.

"OssieMac" wrote:

Hi James,

My testing placed your code in row 2. Perhaps that was just your method of
testing. I think that your code should be something like the following to
select all the last row cells for the formulas.

Probably not necessary with your particular project but when you want to
refer to a specific row irrespective of the position of the formula on the
worksheet you should make the row absolute with a $ sign in front of the row.
Check the formulas in the last row after the code runs.

the code is not as I would do it but I have tried to keep it so that you
will understand what is occurring with the method of selecting the last cells
for the formulas.

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)

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("G" & Rows.Count).End(xlUp).Select
ActiveCell.FormulaR1C1 = "=LastD-R5C[-3]"
Range("H" & Rows.Count).End(xlUp).Select
ActiveCell.FormulaR1C1 = "=LastE-R5C[-3]"
Range("I" & Rows.Count).End(xlUp).Select
ActiveCell.FormulaR1C1 = "=POWER(RC[-2],2)"
Range("J" & Rows.Count).End(xlUp).Select
ActiveCell.FormulaR1C1 = "=POWER(RC[-2],2)"
Range("K" & Rows.Count).End(xlUp).Select
ActiveCell.FormulaR1C1 = "=RC[-2]+RC[-1]"
Range("L" & Rows.Count).End(xlUp).Select
ActiveCell.FormulaR1C1 = "=SQRT(RC[-1])"
Range("M" & Rows.Count).End(xlUp).Select
ActiveCell.FormulaR1C1 = "=Lastc-R5C[-10]"
Range("N" & Rows.Count).End(xlUp).Select
ActiveCell.FormulaR1C1 = "=RC[-2]/RC[-1]"

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

End Sub

--
Regards,

OssieMac

James

Help with my VBA and formula

Thanks for the suggestion, it makes it look much better that way.

"Don Guillett" wrote:

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.

Don Guillett

Help with my VBA and formula

Looks aren't the reason. Selections slow things down and make it jerky
especially if you allow screen updating.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"James" wrote in message
...
Thanks for the suggestion, it makes it look much better that way.

"Don Guillett" wrote:

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.

All times are GMT +1. The time now is 10:55 PM.