I have a macrco which fills in a table which will be used to create a
Run-Chart. When I have filled values into a worksheet, I click a Forms Button
and the data is recorded in several cells, (L3:R3).

When I enter new values into the worksheet and hit the button again I would
like the data to be filled into the next row of cells, (L4:R4), and
subsequently (L5:R5), (L6:r6) etc.

How do I change the address to enter the data each time the form button is
clicked.

My code is as follows:

Sub SaveRunChartData_101_4900_30Deg()
'
' SaveRunChartData_101_4900_30Deg Macro
' Macro recorded 2/24/2010 by Darrell.Roak
'

'
Range("L3").Select
ActiveCell.FormulaR1C1 = "=R[4]C[-7]"
Range("M3").Select
ActiveCell.FormulaR1C1 = "=R[4]C[-6]"
Range("N3").Select
ActiveCell.FormulaR1C1 = "=R[33]C[-8]"
Range("O3").Select
ActiveCell.FormulaR1C1 = "=R[12]C[-10]"
Range("P3").Select
ActiveCell.FormulaR1C1 = "=R[21]C[-11]"
Range("Q3").Select
ActiveCell.FormulaR1C1 = "=R[28]C[-12]"
Range("R3").Select
ActiveCell.FormulaR1C1 = "=R[31]C[-12]"
Range("L4").Select
End Sub

Hi,

Try this and note I got rid of all the unnecessary selection

Sub SaveRunChartData_101_4900_30Deg()
'
' SaveRunChartData_101_4900_30Deg Macro
' Macro recorded 2/24/2010 by Darrell.Roak
'

'
Dim LastRow As Long
LastRow = Cells(Cells.Rows.Count, "L").End(xlUp).Row + 1
Stop
Range("L" & LastRow).FormulaR1C1 = "=R[4]C[-7]"
Range("M" & LastRow).FormulaR1C1 = "=R[4]C[-6]"
Range("N" & LastRow).FormulaR1C1 = "=R[33]C[-8]"
Range("O" & LastRow).FormulaR1C1 = "=R[12]C[-10]"
Range("P" & LastRow).FormulaR1C1 = "=R[21]C[-11]"
Range("Q" & LastRow).FormulaR1C1 = "=R[28]C[-12]"
Range("R" & LastRow).FormulaR1C1 = "=R[31]C[-12]"
End Sub

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Dr. Darrell" wrote:

I have a macrco which fills in a table which will be used to create a
Run-Chart. When I have filled values into a worksheet, I click a Forms Button
and the data is recorded in several cells, (L3:R3).

When I enter new values into the worksheet and hit the button again I would
like the data to be filled into the next row of cells, (L4:R4), and
subsequently (L5:R5), (L6:r6) etc.

How do I change the address to enter the data each time the form button is
clicked.

My code is as follows:

Sub SaveRunChartData_101_4900_30Deg()
'
' SaveRunChartData_101_4900_30Deg Macro
' Macro recorded 2/24/2010 by Darrell.Roak
'

'
Range("L3").Select
ActiveCell.FormulaR1C1 = "=R[4]C[-7]"
Range("M3").Select
ActiveCell.FormulaR1C1 = "=R[4]C[-6]"
Range("N3").Select
ActiveCell.FormulaR1C1 = "=R[33]C[-8]"
Range("O3").Select
ActiveCell.FormulaR1C1 = "=R[12]C[-10]"
Range("P3").Select
ActiveCell.FormulaR1C1 = "=R[21]C[-11]"
Range("Q3").Select
ActiveCell.FormulaR1C1 = "=R[28]C[-12]"
Range("R3").Select
ActiveCell.FormulaR1C1 = "=R[31]C[-12]"
Range("L4").Select
End Sub

OOPs

You don't of course need the STOP comand, I put that there for debugging :(
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Mike H" wrote:

Hi,

Try this and note I got rid of all the unnecessary selection

Sub SaveRunChartData_101_4900_30Deg()
'
' SaveRunChartData_101_4900_30Deg Macro
' Macro recorded 2/24/2010 by Darrell.Roak
'

'
Dim LastRow As Long
LastRow = Cells(Cells.Rows.Count, "L").End(xlUp).Row + 1
Stop
Range("L" & LastRow).FormulaR1C1 = "=R[4]C[-7]"
Range("M" & LastRow).FormulaR1C1 = "=R[4]C[-6]"
Range("N" & LastRow).FormulaR1C1 = "=R[33]C[-8]"
Range("O" & LastRow).FormulaR1C1 = "=R[12]C[-10]"
Range("P" & LastRow).FormulaR1C1 = "=R[21]C[-11]"
Range("Q" & LastRow).FormulaR1C1 = "=R[28]C[-12]"
Range("R" & LastRow).FormulaR1C1 = "=R[31]C[-12]"
End Sub

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Dr. Darrell" wrote:

I have a macrco which fills in a table which will be used to create a
Run-Chart. When I have filled values into a worksheet, I click a Forms Button
and the data is recorded in several cells, (L3:R3).

When I enter new values into the worksheet and hit the button again I would
like the data to be filled into the next row of cells, (L4:R4), and
subsequently (L5:R5), (L6:r6) etc.

How do I change the address to enter the data each time the form button is
clicked.

My code is as follows:

Sub SaveRunChartData_101_4900_30Deg()
'
' SaveRunChartData_101_4900_30Deg Macro
' Macro recorded 2/24/2010 by Darrell.Roak
'

'
Range("L3").Select
ActiveCell.FormulaR1C1 = "=R[4]C[-7]"
Range("M3").Select
ActiveCell.FormulaR1C1 = "=R[4]C[-6]"
Range("N3").Select
ActiveCell.FormulaR1C1 = "=R[33]C[-8]"
Range("O3").Select
ActiveCell.FormulaR1C1 = "=R[12]C[-10]"
Range("P3").Select
ActiveCell.FormulaR1C1 = "=R[21]C[-11]"
Range("Q3").Select
ActiveCell.FormulaR1C1 = "=R[28]C[-12]"
Range("R3").Select
ActiveCell.FormulaR1C1 = "=R[31]C[-12]"
Range("L4").Select
End Sub

Mike:

Thanks for the help. Rationally it makes sence to me, (I'm not a code
wizard!!!)

I entered the code as you typed it, and got an error at "Stop". There is no
explanation on my VBA Editor.

Darrell

"Dr. Darrell" wrote:

I have a macrco which fills in a table which will be used to create a
Run-Chart. When I have filled values into a worksheet, I click a Forms Button
and the data is recorded in several cells, (L3:R3).

When I enter new values into the worksheet and hit the button again I would
like the data to be filled into the next row of cells, (L4:R4), and
subsequently (L5:R5), (L6:r6) etc.

How do I change the address to enter the data each time the form button is
clicked.

My code is as follows:

Sub SaveRunChartData_101_4900_30Deg()
'
' SaveRunChartData_101_4900_30Deg Macro
' Macro recorded 2/24/2010 by Darrell.Roak
'

'
Range("L3").Select
ActiveCell.FormulaR1C1 = "=R[4]C[-7]"
Range("M3").Select
ActiveCell.FormulaR1C1 = "=R[4]C[-6]"
Range("N3").Select
ActiveCell.FormulaR1C1 = "=R[33]C[-8]"
Range("O3").Select
ActiveCell.FormulaR1C1 = "=R[12]C[-10]"
Range("P3").Select
ActiveCell.FormulaR1C1 = "=R[21]C[-11]"
Range("Q3").Select
ActiveCell.FormulaR1C1 = "=R[28]C[-12]"
Range("R3").Select
ActiveCell.FormulaR1C1 = "=R[31]C[-12]"
Range("L4").Select
End Sub

Hi,

See my other post, I put the STOP command in for debugging and forgot to
take it out. simply delete that line
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Dr. Darrell" wrote:

Mike:

Thanks for the help. Rationally it makes sence to me, (I'm not a code
wizard!!!)

I entered the code as you typed it, and got an error at "Stop". There is no
explanation on my VBA Editor.

Darrell

"Dr. Darrell" wrote:

I have a macrco which fills in a table which will be used to create a
Run-Chart. When I have filled values into a worksheet, I click a Forms Button
and the data is recorded in several cells, (L3:R3).

When I enter new values into the worksheet and hit the button again I would
like the data to be filled into the next row of cells, (L4:R4), and
subsequently (L5:R5), (L6:r6) etc.

How do I change the address to enter the data each time the form button is
clicked.

My code is as follows:

Sub SaveRunChartData_101_4900_30Deg()
'
' SaveRunChartData_101_4900_30Deg Macro
' Macro recorded 2/24/2010 by Darrell.Roak
'

'
Range("L3").Select
ActiveCell.FormulaR1C1 = "=R[4]C[-7]"
Range("M3").Select
ActiveCell.FormulaR1C1 = "=R[4]C[-6]"
Range("N3").Select
ActiveCell.FormulaR1C1 = "=R[33]C[-8]"
Range("O3").Select
ActiveCell.FormulaR1C1 = "=R[12]C[-10]"
Range("P3").Select
ActiveCell.FormulaR1C1 = "=R[21]C[-11]"
Range("Q3").Select
ActiveCell.FormulaR1C1 = "=R[28]C[-12]"
Range("R3").Select
ActiveCell.FormulaR1C1 = "=R[31]C[-12]"
Range("L4").Select
End Sub

I should have added to that.

When debugging, I like the stop command because execution of the code does
exactly that, it stops. You can then in VB editor hover the cursor over any
variable that has been set before the stop command and see its value.

there are other ways of doing this but STOP is the one I prefer
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Mike H" wrote:

Hi,

See my other post, I put the STOP command in for debugging and forgot to
take it out. simply delete that line
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Dr. Darrell" wrote:

Mike:

Thanks for the help. Rationally it makes sence to me, (I'm not a code
wizard!!!)

I entered the code as you typed it, and got an error at "Stop". There is no
explanation on my VBA Editor.

Darrell

"Dr. Darrell" wrote:

I have a macrco which fills in a table which will be used to create a
Run-Chart. When I have filled values into a worksheet, I click a Forms Button
and the data is recorded in several cells, (L3:R3).

When I enter new values into the worksheet and hit the button again I would
like the data to be filled into the next row of cells, (L4:R4), and
subsequently (L5:R5), (L6:r6) etc.

How do I change the address to enter the data each time the form button is
clicked.

My code is as follows:

Sub SaveRunChartData_101_4900_30Deg()
'
' SaveRunChartData_101_4900_30Deg Macro
' Macro recorded 2/24/2010 by Darrell.Roak
'

'
Range("L3").Select
ActiveCell.FormulaR1C1 = "=R[4]C[-7]"
Range("M3").Select
ActiveCell.FormulaR1C1 = "=R[4]C[-6]"
Range("N3").Select
ActiveCell.FormulaR1C1 = "=R[33]C[-8]"
Range("O3").Select
ActiveCell.FormulaR1C1 = "=R[12]C[-10]"
Range("P3").Select
ActiveCell.FormulaR1C1 = "=R[21]C[-11]"
Range("Q3").Select
ActiveCell.FormulaR1C1 = "=R[28]C[-12]"
Range("R3").Select
ActiveCell.FormulaR1C1 = "=R[31]C[-12]"
Range("L4").Select
End Sub

Silly me:

The code does work now; however.

I have a formula in each of the Cells (L3:R3) =$E$7, =$G$7, etc. The Code
advances the adress of the Source Data as well as the Target Cells.

"Dr. Darrell" wrote:

Mike:

Thanks for the help. Rationally it makes sence to me, (I'm not a code
wizard!!!)

I entered the code as you typed it, and got an error at "Stop". There is no
explanation on my VBA Editor.

Darrell

"Dr. Darrell" wrote:

I have a macrco which fills in a table which will be used to create a
Run-Chart. When I have filled values into a worksheet, I click a Forms Button
and the data is recorded in several cells, (L3:R3).

When I enter new values into the worksheet and hit the button again I would
like the data to be filled into the next row of cells, (L4:R4), and
subsequently (L5:R5), (L6:r6) etc.

How do I change the address to enter the data each time the form button is
clicked.

My code is as follows:

Sub SaveRunChartData_101_4900_30Deg()
'
' SaveRunChartData_101_4900_30Deg Macro
' Macro recorded 2/24/2010 by Darrell.Roak
'

'
Range("L3").Select
ActiveCell.FormulaR1C1 = "=R[4]C[-7]"
Range("M3").Select
ActiveCell.FormulaR1C1 = "=R[4]C[-6]"
Range("N3").Select
ActiveCell.FormulaR1C1 = "=R[33]C[-8]"
Range("O3").Select
ActiveCell.FormulaR1C1 = "=R[12]C[-10]"
Range("P3").Select
ActiveCell.FormulaR1C1 = "=R[21]C[-11]"
Range("Q3").Select
ActiveCell.FormulaR1C1 = "=R[28]C[-12]"
Range("R3").Select
ActiveCell.FormulaR1C1 = "=R[31]C[-12]"
Range("L4").Select
End Sub

Hi,

So your saying in every subsequent row of (presumably) test data the formula
is the same because the test data has changed in the source cells. If that's
the case then simply hard code the formula instead of using R1C1.

I used the cell ref's as they would apply to row 3 so change them if that
assumption is incorrect

Sub SaveRunChartData_101_4900_30Deg()
'
' SaveRunChartData_101_4900_30Deg Macro
' Macro recorded 2/24/2010 by Darrell.Roak
'

'
Dim LastRow As Long
LastRow = Cells(Cells.Rows.Count, "L").End(xlUp).Row + 1
Range("L" & LastRow).Formula = "=E7"
Range("M" & LastRow).Formula = "=G7"
Range("N" & LastRow).Formula = "=F36"
Range("O" & LastRow).Formula = "=E15"
Range("P" & LastRow).Formula = "=E24"
Range("Q" & LastRow).Formula = "=E31"
Range("R" & LastRow).Formula = "=F34"
End Sub
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Dr. Darrell" wrote:

Silly me:

The code does work now; however.

I have a formula in each of the Cells (L3:R3) =$E$7, =$G$7, etc. The Code
advances the adress of the Source Data as well as the Target Cells.

"Dr. Darrell" wrote:

Mike:

Thanks for the help. Rationally it makes sence to me, (I'm not a code
wizard!!!)

I entered the code as you typed it, and got an error at "Stop". There is no
explanation on my VBA Editor.

Darrell

"Dr. Darrell" wrote:

I have a macrco which fills in a table which will be used to create a
Run-Chart. When I have filled values into a worksheet, I click a Forms Button
and the data is recorded in several cells, (L3:R3).

When I enter new values into the worksheet and hit the button again I would
like the data to be filled into the next row of cells, (L4:R4), and
subsequently (L5:R5), (L6:r6) etc.

How do I change the address to enter the data each time the form button is
clicked.

My code is as follows:

Sub SaveRunChartData_101_4900_30Deg()
'
' SaveRunChartData_101_4900_30Deg Macro
' Macro recorded 2/24/2010 by Darrell.Roak
'

'
Range("L3").Select
ActiveCell.FormulaR1C1 = "=R[4]C[-7]"
Range("M3").Select
ActiveCell.FormulaR1C1 = "=R[4]C[-6]"
Range("N3").Select
ActiveCell.FormulaR1C1 = "=R[33]C[-8]"
Range("O3").Select
ActiveCell.FormulaR1C1 = "=R[12]C[-10]"
Range("P3").Select
ActiveCell.FormulaR1C1 = "=R[21]C[-11]"
Range("Q3").Select
ActiveCell.FormulaR1C1 = "=R[28]C[-12]"
Range("R3").Select
ActiveCell.FormulaR1C1 = "=R[31]C[-12]"
Range("L4").Select
End Sub

Mike:

This is good stuff. You have been very helpful.

Darrell

"Mike H" wrote:

Hi,

So your saying in every subsequent row of (presumably) test data the formula
is the same because the test data has changed in the source cells. If that's
the case then simply hard code the formula instead of using R1C1.

I used the cell ref's as they would apply to row 3 so change them if that
assumption is incorrect

Sub SaveRunChartData_101_4900_30Deg()
'
' SaveRunChartData_101_4900_30Deg Macro
' Macro recorded 2/24/2010 by Darrell.Roak
'

'
Dim LastRow As Long
LastRow = Cells(Cells.Rows.Count, "L").End(xlUp).Row + 1
Range("L" & LastRow).Formula = "=E7"
Range("M" & LastRow).Formula = "=G7"
Range("N" & LastRow).Formula = "=F36"
Range("O" & LastRow).Formula = "=E15"
Range("P" & LastRow).Formula = "=E24"
Range("Q" & LastRow).Formula = "=E31"
Range("R" & LastRow).Formula = "=F34"
End Sub
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Dr. Darrell" wrote:

Silly me:

The code does work now; however.

I have a formula in each of the Cells (L3:R3) =$E$7, =$G$7, etc. The Code
advances the adress of the Source Data as well as the Target Cells.

"Dr. Darrell" wrote:

Mike:

Thanks for the help. Rationally it makes sence to me, (I'm not a code
wizard!!!)

I entered the code as you typed it, and got an error at "Stop". There is no
explanation on my VBA Editor.

Darrell

"Dr. Darrell" wrote:

I have a macrco which fills in a table which will be used to create a
Run-Chart. When I have filled values into a worksheet, I click a Forms Button
and the data is recorded in several cells, (L3:R3).

When I enter new values into the worksheet and hit the button again I would
like the data to be filled into the next row of cells, (L4:R4), and
subsequently (L5:R5), (L6:r6) etc.

How do I change the address to enter the data each time the form button is
clicked.

My code is as follows:

Sub SaveRunChartData_101_4900_30Deg()
'
' SaveRunChartData_101_4900_30Deg Macro
' Macro recorded 2/24/2010 by Darrell.Roak
'

'
Range("L3").Select
ActiveCell.FormulaR1C1 = "=R[4]C[-7]"
Range("M3").Select
ActiveCell.FormulaR1C1 = "=R[4]C[-6]"
Range("N3").Select
ActiveCell.FormulaR1C1 = "=R[33]C[-8]"
Range("O3").Select
ActiveCell.FormulaR1C1 = "=R[12]C[-10]"
Range("P3").Select
ActiveCell.FormulaR1C1 = "=R[21]C[-11]"
Range("Q3").Select
ActiveCell.FormulaR1C1 = "=R[28]C[-12]"
Range("R3").Select
ActiveCell.FormulaR1C1 = "=R[31]C[-12]"
Range("L4").Select
End Sub

Your welcome and thanks for the feedback
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Dr. Darrell" wrote:

Mike:

This is good stuff. You have been very helpful.

Darrell

"Mike H" wrote:

Hi,

So your saying in every subsequent row of (presumably) test data the formula
is the same because the test data has changed in the source cells. If that's
the case then simply hard code the formula instead of using R1C1.

I used the cell ref's as they would apply to row 3 so change them if that
assumption is incorrect

Sub SaveRunChartData_101_4900_30Deg()
'
' SaveRunChartData_101_4900_30Deg Macro
' Macro recorded 2/24/2010 by Darrell.Roak
'

'
Dim LastRow As Long
LastRow = Cells(Cells.Rows.Count, "L").End(xlUp).Row + 1
Range("L" & LastRow).Formula = "=E7"
Range("M" & LastRow).Formula = "=G7"
Range("N" & LastRow).Formula = "=F36"
Range("O" & LastRow).Formula = "=E15"
Range("P" & LastRow).Formula = "=E24"
Range("Q" & LastRow).Formula = "=E31"
Range("R" & LastRow).Formula = "=F34"
End Sub
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Dr. Darrell" wrote:

Silly me:

The code does work now; however.

I have a formula in each of the Cells (L3:R3) =$E$7, =$G$7, etc. The Code
advances the adress of the Source Data as well as the Target Cells.

"Dr. Darrell" wrote:

Mike:

Thanks for the help. Rationally it makes sence to me, (I'm not a code
wizard!!!)

I entered the code as you typed it, and got an error at "Stop". There is no
explanation on my VBA Editor.

Darrell

"Dr. Darrell" wrote:

I have a macrco which fills in a table which will be used to create a
Run-Chart. When I have filled values into a worksheet, I click a Forms Button
and the data is recorded in several cells, (L3:R3).

When I enter new values into the worksheet and hit the button again I would
like the data to be filled into the next row of cells, (L4:R4), and
subsequently (L5:R5), (L6:r6) etc.

How do I change the address to enter the data each time the form button is
clicked.

My code is as follows:

Sub SaveRunChartData_101_4900_30Deg()
'
' SaveRunChartData_101_4900_30Deg Macro
' Macro recorded 2/24/2010 by Darrell.Roak
'

'
Range("L3").Select
ActiveCell.FormulaR1C1 = "=R[4]C[-7]"
Range("M3").Select
ActiveCell.FormulaR1C1 = "=R[4]C[-6]"
Range("N3").Select
ActiveCell.FormulaR1C1 = "=R[33]C[-8]"
Range("O3").Select
ActiveCell.FormulaR1C1 = "=R[12]C[-10]"
Range("P3").Select
ActiveCell.FormulaR1C1 = "=R[21]C[-11]"
Range("Q3").Select
ActiveCell.FormulaR1C1 = "=R[28]C[-12]"
Range("R3").Select
ActiveCell.FormulaR1C1 = "=R[31]C[-12]"
Range("L4").Select
End Sub