Need to add next loop in code

Closed
FaizanRoshan88
Posts
4
Registration date
Friday July 31, 2015
Status
Member
Last seen
August 1, 2015
- Jul 31, 2015 at 06:52 AM
FaizanRoshan88
Posts
4
Registration date
Friday July 31, 2015
Status
Member
Last seen
August 1, 2015
- Jul 31, 2015 at 08:22 PM
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

1 reply

Blocked Profile
Jul 31, 2015 at 08:44 AM
I am not certain of your problem. Are you trying to only loop a certain number of time, based on the number of rows you have?
0
FaizanRoshan88
Jul 31, 2015 at 09:47 AM
I just want to do calculation in all row without repeating code for each row.
0
FaizanRoshan88
Jul 31, 2015 at 09:48 AM
i don't know how can do this.
0
FaizanRoshan88
Jul 31, 2015 at 09:53 AM
Hi,
I have a vba code that have too long, i can't be able to make it short because i have not experience in vba. but i believe its will be done some one that have experience. Here is code:
0
Blocked Profile
Jul 31, 2015 at 11:09 AM
Well, I am not certain that you can write the code to DYNAMIC for each and every line, without the code looping. Make a variable to keep track of the Row. Then point each Cell to correspond with the variable of the row. Then loop through the count, incrementing the value by one for each loop. Then you wrote the code once, but it will run for each row. You just need to count the row from the last one, and count on your way up to 0. I cannot provide you with a turn key solution, as I am a volunteer and I cannot possible give you every single variable for your code. NOW WHAT I CAN DO IS: ASSIST you in the design, and troubleshooting of your code.

Have FUN and hang in there.
0
FaizanRoshan88
Posts
4
Registration date
Friday July 31, 2015
Status
Member
Last seen
August 1, 2015
> Blocked Profile
Jul 31, 2015 at 08:22 PM
thanks for you help.
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
0