Help with my VBA and formula

J

James

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

OssieMac

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 said:
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.
Click to expand...
 
J

James

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 said:
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 said:
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.
Click to expand...
Click to expand...
 
J

James

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 said:
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 said:
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.
Click to expand...
Click to expand...
 
D

Don Guillett

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
(e-mail address removed)
James said:
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 said:
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 said:
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.
Click to expand...
Click to expand...
Click to expand...
 
O

OssieMac

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
 
J

James

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 said:
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
Click to expand...
 
J

James

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

Don Guillett said:
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
(e-mail address removed)
James said:
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 said:
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


:

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.
Click to expand...
Click to expand...
Click to expand...
 
D

Don Guillett

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
(e-mail address removed)
James said:
Thanks for the suggestion, it makes it look much better that way.

Don Guillett said:
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
(e-mail address removed)
James said:
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.

:

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


:

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.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Geometric progression in VBA 10
Linking Cells Macro 3
Checkbox linked to calcs 1
Data samples 3
Insert columns and values programmatically with IF statement 9
"=TEXT(RC[-1],"MMM")" 4
macro to copy rows that contain a cell with a certain number 3
Link Cells & Paste Special Macro 7

Top