Editing a Macro

[Pezgordo]

I have a macro that I wrote using the Developer in Excel (I would have no idea how to write one from scratch). The macro selects various stats from an imported box score and places those stats into cells within the worksheet. The cells the macro places the stats into is D7 thru AX7, and this worked great for week # 1. However I am now into week # 2 and need to place all the stats into cells D8 thru AX8 (the next row). I tried going into Developer and change all the D7 thru AX7 sections to D8 thru AX8 via the editor, but that didn't work.

My question is do I need to write a new macro each week or is there a way to edit the current macro whereby it will start on a different row?

Here is a small sample of the macro:

ActiveCell.FormulaR1C1 = "=R[44]C[123]"
Range("E7").Select
ActiveCell.FormulaR1C1 = "=R[43]C[122]"
Range("F7").Select
ActiveCell.FormulaR1C1 = "=R[46]C[121]"
Range("G7").Select
ActiveCell.FormulaR1C1 = "=R[50]C[121]"
Range("H7").Select
ActiveCell.FormulaR1C1 = "=R[50]C[119]"
Range("I7").Select
ActiveCell.FormulaR1C1 = "=R[49]C[118]"
Range("J7").Select
ActiveCell.FormulaR1C1 = "=R[53]C[117]"
Range("K7").Select

Thank you,

Tim

 

[Datsmart]

I don't quite understand the formula relationship you have going, but the following code will input your listed formulas into the current active row.

Sub CopyActiveCell()
    ActiveCell.FormulaR1C1 = "=R[44]C[123]"
    ActiveCell.Offset(0, 1).FormulaR1C1 = "=R[43]C[122]"
    ActiveCell.Offset(0, 2).FormulaR1C1 = "=R[46]C[121]"
    ActiveCell.Offset(0, 3).FormulaR1C1 = "=R[50]C[121]"
    ActiveCell.Offset(0, 4).FormulaR1C1 = "=R[50]C[119]"
    ActiveCell.Offset(0, 5).FormulaR1C1 = "=R[49]C[118]"
    ActiveCell.Offset(0, 6).FormulaR1C1 = "=R[53]C[117]"
End Sub

Code assumes you first select a cell in column "D" and run the code for that row.

 

[Pezgordo]

Thank you, I ran the code but it didn't work. I am sure I wrote it incorrectly. Actually the part that you sent me worked on row 7 (starting on cell D), but not on row 8. Here is the new code I wrote based on your suggestion:

Sub BoxScoreStats()
'
' BoxScoreStats Macro
'

'
ActiveCell.FormulaR1C1 = "=R[44]C[123]"
ActiveCell.Offset(0, 1).FormulaR1C1 = "=R[43]C[122]"
ActiveCell.Offset(0, 2).FormulaR1C1 = "=R[46]C[121]"
ActiveCell.Offset(0, 3).FormulaR1C1 = "=R[50]C[121]"
ActiveCell.Offset(0, 4).FormulaR1C1 = "=R[50]C[119]"
ActiveCell.Offset(0, 5).FormulaR1C1 = "=R[49]C[118]"
ActiveCell.Offset(0, 6).FormulaR1C1 = "=R[53]C[117]"
ActiveCell.Offset(0, 7).FormulaR1C1 = "=RC[-3]/RC[-4]"
ActiveCell.Offset(0, 8).FormulaR1C1 = "=R[87]C[111]"
ActiveCell.Offset(0, 9).FormulaR1C1 = "=R[87]C[111]"
ActiveCell.Offset(0, 10).FormulaR1C1 = "=R[50]C[115]"
ActiveCell.Offset(0, 11).FormulaR1C1 = "=R[57]C[112]"
ActiveCell.Offset(0, 12).FormulaR1C1 = "=R[57]C[112]"
ActiveCell.Offset(0, 13).FormulaR1C1 = "=R[82]C[110]"
ActiveCell.Offset(0, 14).FormulaR1C1 = "=R[82]C[111]"
ActiveCell.Offset(0, 15).FormulaR1C1 = "=R[39]C[108]"
ActiveCell.Offset(0, 16).FormulaR1C1 = "=R[77]C[107]"
ActiveCell.Offset(0, 17).FormulaR1C1 = "=R[58]C[106]"
ActiveCell.Offset(0, 18).FormulaR1C1 = "=R[58]C[106]"
ActiveCell.Offset(0, 19).FormulaR1C1 = "=R[84]C[105]"
ActiveCell.Offset(0, 20).FormulaR1C1 = "=R[84]C[103]"
ActiveCell.Offset(0, 21).FormulaR1C1 = "=R[85]C[102]"
ActiveCell.Offset(0, 22).FormulaR1C1 = "=R[86]C[101]"
ActiveCell.Offset(0, 23).FormulaR1C1 = "=R[44]C[95]"
ActiveCell.Offset(0, 24).FormulaR1C1 = "=R[43]C[94]"
ActiveCell.Offset(0, 25).FormulaR1C1 = "=R[46]C[93]"
ActiveCell.Offset(0, 26).FormulaR1C1 = "=R[50]C[93]"
ActiveCell.Offset(0, 27).FormulaR1C1 = "=R[50]C[91]"
ActiveCell.Offset(0, 28).FormulaR1C1 = "=R[49]C[90]"
ActiveCell.Offset(0, 29).FormulaR1C1 = "=R[53]C[89]"
ActiveCell.Offset(0, 30).FormulaR1C1 = "=RC[-3]/RC[-4]"
ActiveCell.Offset(0, 31).FormulaR1C1 = "=R[87]C[91]"
ActiveCell.Offset(0, 32).FormulaR1C1 = "=R[87]C[91]"
ActiveCell.Offset(0, 33).FormulaR1C1 = "=R[50]C[87]"
ActiveCell.Offset(0, 34).FormulaR1C1 = "=R[57]C[88]"
ActiveCell.Offset(0, 35).FormulaR1C1 = "=R[57]C[88]"
ActiveCell.Offset(0, 36).FormulaR1C1 = "=R[82]C[82]"
ActiveCell.Offset(0, 37).FormulaR1C1 = "=R[82]C[83]"
ActiveCell.Offset(0, 38).FormulaR1C1 = "=R[39]C[80]"
ActiveCell.Offset(0, 39).FormulaR1C1 = "=R[77]C[79]"
ActiveCell.Offset(0, 40).FormulaR1C1 = "=R[58]C[78]"
ActiveCell.Offset(0, 41).FormulaR1C1 = "=R[58]C[78]"
ActiveCell.Offset(0, 42).FormulaR1C1 = "=R[84]C[77]"
ActiveCell.Offset(0, 43).FormulaR1C1 = "=R[84]C[75]"
ActiveCell.Offset(0, 44).FormulaR1C1 = "=R[85]C[74]"
ActiveCell.Offset(0, 45).FormulaR1C1 = "=R[86]C[73]"
End Sub

Any ideas and/or suggestions of what I have done incorrectly?

Tim

 

[Datsmart]

What is the activecell when you run the macro?

As I said in my first post:

Code assumes you first select a cell in column "D" and run the code for that row.

Select cell D8, and the code will input formulae in row 8.

 

[Pezgordo]

Is the activecell the cell where you start the macro? If so it is D7.

When I run it on row 7 it works, but it doesn't for row 8. For example on row 7, D7 = DW51, E7 = DW50, F7 = DW53, G7 = DX57, H7 = DW57, I7 = DW56, J7 = DW60, etc.

On row 8, D8 = DW52, E8 = DW51, F8 = DW54, G8 = DX58, H8 = DW58, I8 = DW57, J8 = DW61.

So it appears that it goes to the next number in row 8. I tried running it on row 9 and it did go up another number (D9 = DW53, E9 = DW52, etc.).

 

[Datsmart]

If you want to input your formulas in every row below Row7, this code will do that.

Sub InputFormulas()
Dim Rng As Range
Dim a As Range
    'Assign variable for Sheet name
    sn = ActiveSheet.Name
    'Assign variable for LastRow
    LR = Sheets(sn).Cells(Sheets(sn).Rows.Count, "D").End(xlUp).Row
    'Assign variable for Start Row
    sr = 7
    'Assign range for formulas
    Set Rng = Sheets(sn).Range(Sheets(sn).Cells(sr, "D"), Sheets(sn).Cells(LR, "D"))
        'Loop through items in Rng
        For Each a In Rng
            cr = a.Row
            cc = a.Column
            Cells(cr, cc).FormulaR1C1 = "=R[44]C[123]"
            Cells(cr, cc + 1).FormulaR1C1 = "=R[43]C[122]"
            Cells(cr, cc + 2).FormulaR1C1 = "=R[46]C[121]"
            Cells(cr, cc + 3).FormulaR1C1 = "=R[50]C[121]"
            Cells(cr, cc + 4).FormulaR1C1 = "=R[50]C[119]"
            Cells(cr, cc + 5).FormulaR1C1 = "=R[49]C[118]"
            Cells(cr, cc + 6).FormulaR1C1 = "=R[53]C[117]"
        Next a
End Sub

This code will input formulas down to the last row in column D that has values. It will not assign values to empty rows as written.
The earlier code I gave you was meant to input formulas in just the "Active Row" that the cursor is on when the code is run.