I'd like to modify mt Macro ...

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
 
M

Mike H

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

Mike H

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
 
D

Dr. Darrell

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
 
M

Mike H

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
 
M

Mike H

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
 
D

Dr. Darrell

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
 
M

Mike H

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
 
D

Dr. Darrell

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
 
M

Mike H

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
 

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

I need to modify my macro ... 0
how do I write a loop macro? 9
Modify range in VBA 9
Error 2007 0
Help to create a macro 1
Easier Way to Macro Total 4
Macro 3
Sum Columns with Specific Headings 6

Top