I'd like to modify mt Macro ...

  • Thread starter Thread starter Dr. Darrell
  • Start date Start date
D

Dr. Darrell

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

This is good stuff. You have been very helpful.

Darrell

Mike H said:
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 said:
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 said:
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

:

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

This is good stuff. You have been very helpful.

Darrell

Mike H said:
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 said:
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.

:

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

:

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