• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Vba code short but useable

FaizanRoshan88

FaizanRoshan88

Member
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:
Code: 
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
 

Attachments

  • Sample Dashboard.xlsm
    37.5 KB · Views: 1
its work perfectly, thanks you so much.
i want to know little more thing if i create new excel tab and fill data with same format,is this code able to fill next row with calculating new sheet data?
I will be create lot of new tabs. its there any solution for this.
 
Hi ,

If you see the code , there are the following 2 lines :

TabNames = Array("4-16 - 4-22", "4-23 - 4-29", "4-30 - 5-6")
Ordinals = Array("1st", "2nd", "3rd")

If you want to add more tabs , ensure that they follow the same naming convention of having the prefix adt. Add the names to the first line , and extend the second line as well.

As an example , suppose you want to add a 4th tab , which is already present in your workbook , with the name adt5-7 - 5-13 ; the above 2 lines will become :

TabNames = Array("4-16 - 4-22", "4-23 - 4-29", "4-30 - 5-6" , "5-7 - 5-13")
Ordinals = Array("1st", "2nd", "3rd", "4th")

Narayan
 
Its good. but if i have hundred or thousand of tab, then is this possible i don't write tab name each time. its just update auto.
thanks
 
Last edited:
Hi ,

Can you specify the following ?

1. The naming convention for all the relevant tabs will be strictly followed ; can you confirm this ?

2. What will be the starting tab , can you specify this ?

3. The summary tab will be the one having the code ; every other tab will be presumed to have data in it in column P. Can you confirm this ?

Narayan
 
yha i specify following.
1. i confirm tabs will be strictly followed.
2. Tabs will be starting with adt4-16 - 4-22
3. for data P, i am not sure about it. if you take data from column "I" it will have data in all tabs. but its will be change using formula, as you see in excel sheet. or use hours as data Like: 8 hours or 480 minutes & 5 hours or 300 minutes
 
Hi ,

The problem is that the SUMIF(S) family of functions cannot use other functions to derive the ranges ; directly using the data in column I may not be possible. The existing formulae in column P will have to be continued , and the values in column P can be used.

Narayan
 
Hi Narayan,
will you have any solution about last last issue on calculating all sheet, i'm very thankful to you if you help me for this.
thanks
 
Hi, as you say its not possible to take data form column I in 5h format, is it possible to take data form column I in proper time format like 5:00:00, 7:40:00 etc.
 
Hi,
Yha i change it but its not work because there is not problem of P to I, when i change Number "300" to " 5:00:00" as standard time format. its not work.
 
Hi ,

Your data in standard time format seems to have a space character in the first place , which will create a problem ; can you upload a file with data in column I ?

Narayan
 
Hi,
In if its possible to calculate column I with data of "5h 30m" its best otherwise i will change it in time format.
Remember the numerical value (e.g 300) or (e.g 5:00:00) is use for working hours of employee, its not time. if i use (e.g 5h 30m) in code its easy to use but not work. In I column some value is more then 24 Like"25:23:00 etc" Its means 25 h 23 m or 1530 in number.
The numerical value (e.g 300) or (e.g 5:00:00) is use for working hours of employee its not time. but i will not use its as number as its difficult to calculate every time. if i use (e.g 5h 30m) its easy to use but not work in code.
The numerical value (e.g 300) or (e.g 5:00:00) is use for working hours of employee its not time. but i will not use its as number as its difficult to calculate every time. if i use (e.g 5h 30m) its easy to use but not work in code.
 

Attachments

  • Sample Dashboard_Revised.xlsm
    36.1 KB · Views: 0
Its work on some columns but columns C,D & I dose not work. is it not possible to use column I data in 5h 30m format. or use the formula direct in code that use in columns P for changing 5h 20m to number 510 etc.
In attach file see formula use in column P that calculate column I.
 

Attachments

  • Sample Dashboard_Revised.xlsm
    34 KB · Views: 1
Hi ,

I am tired.

Please decide once and for all what you want to do ; do you want to use column P or not ?

If you want to use column P , with its own formula , then the original code will work.

If you do not want to use column P , with its own formula , then any other column which needs to be summed up or averaged , will need to have its data in the form of numeric quantities ; 5h and 6m are not numeric quantities. If the columns have numeric quantities , then the second version of the code will work.

If you do not want to use either column P or column I or any other column , then please give a complete specification of what your input data will look like ; start a new thread if possible so that someone else can answer.

Narayan
 
You must log in or register to reply here.
Back
Top