Short macro code

[FaizanRoshan]

Hi Everyone, i have macro code that calculate excel sheets and paste answer in dashboard row, each new excel sheet data paste in new row after calculation.
i want to short this code and will be working lot of sheet and row. i means it continue when any new sheet create its done calculation and paste in next row. Here is code:

Sub Macro1()
'
' Macro1 Macro
'


'
Range("A5").Select
ActiveCell.FormulaR1C1 = "4-16 - 4-22"
Range("B5").Select
ActiveCell.FormulaR1C1 = "1st"
Range("C5").Select
ActiveCell.FormulaR1C1 = "=AVERAGEIFS('adt4-16 - 4-22'!C16, 'adt4-16 - 4-22'!C16, "">301"",'adt4-16 - 4-22'!C16, ""<480"")"
Range("D5").Select
ActiveCell.FormulaR1C1 = "=COUNTIFS('adt4-16 - 4-22'!C16,"">""&301,'adt4-16 - 4-22'!C16,""<""&480)"
Range("E5").Select
ActiveCell.FormulaR1C1 = "=(R2C3-RC3)*(R1C4*RC4)"
Range("F5").Select
ActiveCell.FormulaR1C1 = "=AVERAGEIFS('adt4-16 - 4-22'!C16, 'adt4-16 - 4-22'!C16, "">=1"",'adt4-16 - 4-22'!C16, ""<300"")"
Range("G5").Select
ActiveCell.FormulaR1C1 = "=COUNTIFS('adt4-16 - 4-22'!C16,"">=""&1,'adt4-16 - 4-22'!C16,""<""&300)"
Range("H5").Select
ActiveCell.FormulaR1C1 = "=(R2C3-RC6)*(R1C4*RC7)"
Range("I5").Select
ActiveCell.FormulaR1C1 = "=AVERAGE('adt4-16 - 4-22'!C16)"
Range("J5").Select
ActiveCell.FormulaR1C1 = "=COUNTIFS('adt4-16 - 4-22'!C16,"">=""&1)"
Range("K5").Select
ActiveCell.FormulaR1C1 = "=(R2C3-RC9)*(R1C4*RC10)"


Range("A6").Select
ActiveCell.FormulaR1C1 = "4-23 - 4-29"
Range("B6").Select
ActiveCell.FormulaR1C1 = "2nd"
Range("C6").Select
ActiveCell.FormulaR1C1 = "=AVERAGEIFS('adt4-23 - 4-29'!C16, 'adt4-23 - 4-29'!C16, "">301"",'adt4-23 - 4-29'!C16, ""<480"")"
Range("D6").Select
ActiveCell.FormulaR1C1 = "=COUNTIFS('adt4-23 - 4-29'!C16,"">""&301,'adt4-23 - 4-29'!C16,""<""&480)"
Range("E6").Select
ActiveCell.FormulaR1C1 = "=(R[-4]C3-RC3)*(R1C4*RC4)"
Range("F6").Select
ActiveCell.FormulaR1C1 = "=AVERAGEIFS('adt4-23 - 4-29'!C16, 'adt4-23 - 4-29'!C16, "">=1"",'adt4-23 - 4-29'!C16, ""<300"")"
Range("G6").Select
ActiveCell.FormulaR1C1 = "=COUNTIFS('adt4-23 - 4-29'!C16,"">=""&1,'adt4-23 - 4-29'!C16,""<""&300)"
Range("H6").Select
ActiveCell.FormulaR1C1 = "=(R[-4]C3-RC6)*(R1C4*RC7)"
Range("I6").Select
ActiveCell.FormulaR1C1 = "=AVERAGE('adt4-23 - 4-29'!C16)"
Range("J6").Select
ActiveCell.FormulaR1C1 = "=COUNTIFS('adt4-23 - 4-29'!C16,"">=""&1)"
Range("K6").Select
ActiveCell.FormulaR1C1 = "=(R2C3-RC9)*(R1C4*RC10)"

End Sub

First 26 row of code calculate Sheet2 and paste in row 5 and next 26 do it for next sheet3 and row 6. and so on.

Please help me to make it short, continue for new sheets/row and use ful.

Thanks advance for your help.

 

[ukmikeb]

Hi

You don't need to Select cells to assign values or formulae to them, the first 2 lines would then be :-

Range("A5").[/COLOR]FormulaR1C1 = "4-16 - 4-22"

[/code]

but as that is not a formula you could put :

Range("A5").Value = "4-16 - 4-22"

Where there is a formula put, as for C5 :-

Range("C5").FormulaR1C1 = "=AVERAGEIFS('adt4-16 - 4-22'!C16, 'adt4-16 - 4-22'!C16, "">301"",'adt4-16 - 4-22'!C16, ""<480"")"

hth

 

[FaizanRoshan]

Thanks for this. i have little bit issue if you solve this
Here is code, as you see i repeat code for next row and sheet calculation. i just want to calculate all row without repeat code again & again

Sub Macro1()
'
' Macro1 Macro
'
Range("A5").Value = "4-16 - 4-22"
Range("B5").Value = "1st"
Range("C5").FormulaR1C1 = "=AVERAGEIFS('adt4-16 - 4-22'!C16, 'adt4-16 - 4-22'!C16, "">301"",'adt4-16 - 4-22'!C16, ""<480"")"
Range("D5").FormulaR1C1 = "=COUNTIFS('adt4-16 - 4-22'!C16,"">""&301,'adt4-16 - 4-22'!C16,""<""&480)"
Range("E5").FormulaR1C1 = "=(R2C3-RC3)*(R1C4*RC4)"
Range("F5").FormulaR1C1 = "=AVERAGEIFS('adt4-16 - 4-22'!C16, 'adt4-16 - 4-22'!C16, "">=1"",'adt4-16 - 4-22'!C16, ""<300"")"
Range("G5").FormulaR1C1 = "=COUNTIFS('adt4-16 - 4-22'!C16,"">=""&1,'adt4-16 - 4-22'!C16,""<""&300)"
Range("H5").FormulaR1C1 = "=(R2C3-RC6)*(R1C4*RC7)"
Range("I5").FormulaR1C1 = "=AVERAGE('adt4-16 - 4-22'!C16)"
Range("J5").FormulaR1C1 = "=COUNTIFS('adt4-16 - 4-22'!C16,"">=""&1)"
Range("K5").FormulaR1C1 = "=(R2C3-RC9)*(R1C4*RC10)"


Range("A6").Value = "4-23 - 4-29"
Range("B6").Value = "2nd"
Range("C6").FormulaR1C1 = "=AVERAGEIFS('adt4-23 - 4-29'!C16, 'adt4-23 - 4-29'!C16, "">301"",'adt4-23 - 4-29'!C16, ""<480"")"
Range("D6").FormulaR1C1 = "=COUNTIFS('adt4-23 - 4-29'!C16,"">""&301,'adt4-23 - 4-29'!C16,""<""&480)"
Range("E6").FormulaR1C1 = "=(R[-4]C3-RC3)*(R1C4*RC4)"
Range("F6").FormulaR1C1 = "=AVERAGEIFS('adt4-23 - 4-29'!C16, 'adt4-23 - 4-29'!C16, "">=1"",'adt4-23 - 4-29'!C16, ""<300"")"
Range("G6").FormulaR1C1 = "=COUNTIFS('adt4-23 - 4-29'!C16,"">=""&1,'adt4-23 - 4-29'!C16,""<""&300)"
Range("H6").FormulaR1C1 = "=(R[-4]C3-RC6)*(R1C4*RC7)"
Range("I6").FormulaR1C1 = "=AVERAGE('adt4-23 - 4-29'!C16)"
Range("J6").FormulaR1C1 = "=COUNTIFS('adt4-23 - 4-29'!C16,"">=""&1)"
Range("K6").FormulaR1C1 = "=(R2C3-RC9)*(R1C4*RC10)"


End Sub

It will be done through 'loop next"

Thanks

 

[FaizanRoshan]

Hi, I have a Macro code that have too long, i need it to convert in vba code. i believe its will be done some one that have experience. Here is code:


Private Sub CommandButton2_Click()
' Macro1 Macro
'
Range("A5").Value = "4-16 - 4-22"
Range("B5").Value = "1st"
Range("C5").FormulaR1C1 = "=AVERAGEIFS('adt4-16 - 4-22'!C16, 'adt4-16 - 4-22'!C16, "">301"",'adt4-16 - 4-22'!C16, ""<480"")"
Range("D5").FormulaR1C1 = "=COUNTIFS('adt4-16 - 4-22'!C16,"">""&301,'adt4-16 - 4-22'!C16,""<""&480)"
Range("E5").FormulaR1C1 = "=(R2C3-RC3)*(R1C4*RC4)"


End Sub
It will be continue for next row calculation. Thanks for your help

 

[diddi]

use cell referencing instead of range...
then you can loop your rows

eg Range("A5") is same as Cells(5,1)

 

[Michael M]

Hey diddi
This has been duplicate posted at least 5 times...

 

[diddi]

darn I got scammed