Assigning Formula

[Brutium]

Hello Everyone,

I have a rather peculiar situation. I surely hope someone will help me....

Using a macro, I created a number of sheets using a template. What I now need to do is figure out is how to assign a formula to different cells (the data comes from the various sheets that I created). I guess I need to replicate the same formula, but I can't figure out how to do it.

Any help would be greatly appreciated.

Thank you
Brutium

 

[Brutium]

Hello repairman615,

Thank you for your reply. I think that your solution would be better than mine.

Sheets("Class").Select
Range("C9:L9").Select
Selection.AutoFill Destination:=Range("C9:L52"), Type:=xlFillDefault
Range("C9:L52").Select

This is what I have but unfortunately, when the sheets are created, if I do not create enough sheets to cover up to column L then I get a #REF! error and my formula to average all the marks that I have in column N9 to N52,

=(SUMPRODUCT(C$7:L$7,C9:L9)/SUMIF(C9:L9,"<>",C$7:L$7))

will not work since it tries to add or average also the error.

What can I do?

Brutium

 

[repairman615]

Hello Brutium,

I think the method you posted would be more efficient and yeild a faster macro.

Here is an example that may work, or at least give an insight to the loop within the loop:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Addsheets()<br>    <SPAN style="color:#00007F">Dim</SPAN> LR <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, MasterSheet <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> rngTopOfList <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> wsName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> n <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, j <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, x <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br>   <br>    n = 0<br><br>    <SPAN style="color:#00007F">With</SPAN> Sheets("tools")<br>    <br>        <SPAN style="color:#00007F">Set</SPAN> rngTopOfList = .Range("B3")<br>    <br>        LR = .Cells(.Rows.Count, rngTopOfList.Column).End(xlUp).Row<br>    <br>        MasterSheet = ActiveWorkbook.Sheets.Count<br>    <br>            <SPAN style="color:#00007F">If</SPAN> LR > rngTopOfList.Row <SPAN style="color:#00007F">Then</SPAN><br>    <br>                <SPAN style="color:#00007F">For</SPAN> i = 0 <SPAN style="color:#00007F">To</SPAN> LR - rngTopOfList.Row<br>                    ActiveWorkbook.Sheets(MasterSheet).Copy _<br>                    after:=Worksheets(MasterSheet + i - 1)<br>                    ActiveWorkbook.Sheets(MasterSheet + i).Name = _<br>                    rngTopOfList.Offset(i).Value <SPAN style="color:#007F00">'''Can be replaced with wsName if the following line is moved before.</SPAN><br>                    <br>                    <SPAN style="color:#007F00">''' Here begins changes</SPAN><br>                    wsName = rngTopOfList.Offset(i).Value<br><br>                    x = 0<br>                     <br>                        <SPAN style="color:#00007F">For</SPAN> j = 1 <SPAN style="color:#00007F">To</SPAN> 42 <SPAN style="color:#007F00">'''what ever it takes to go from row 9 to row 52</SPAN><br>                            ActiveWorkbook.Sheets("Class").Range("C9").Offset(x, n).Value = _<br>                            "=" & wsName & "!M9"<br>                             <br>                            x = x + 1<br>                        <SPAN style="color:#00007F">Next</SPAN> j<br>                    <br>                    n = n + 1<br>                    <br>                <SPAN style="color:#00007F">Next</SPAN> i<br>                    <SPAN style="color:#007F00">'''</SPAN><br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>                    <br>        ActiveWorkbook.Sheets(MasterSheet).Name = rngTopOfList.Value<br>        Worksheets("MasterSheet").Visible = xlSheetVeryHidden<br>        Worksheets("Tools").Visible = xlSheetVeryHidden<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <br>    <SPAN style="color:#00007F">Set</SPAN> rngTopOfList = <SPAN style="color:#00007F">Nothing</SPAN><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br></FONT>

 

[Brutium]

Good morning repairman615,

Thank you for your reply.
Unfortunately it does not work, since it fill in the whole range with the same value coming from the first sheet that I create.

 

[repairman615]

Is this the value from M9?

If so what cell or where would the next value come from ans so on?

edit:
also does this fill in without error as you would like?

 

[Brutium]

Good morning repairman615

I really appreciate your help. Unfortunately I have been VERY busy these last few days and I was not able to reply to your post.

What now happens, is that the program pull the information from the first student, this is what is found in M9 from the worksheets, and repeats it for the remainder of my students, the ones found on M10:M52. What should happen is that the information found in the created sheets in M9 to M52 should also populate in my sheet called Class from C2 to L2 until C52 to L52.

Also, and this is my problem, if I do not use some of the columns, up to the L column, the error #REF! appears and I am not able to perform the calculations in the M column of the Class sheet, because the formula, obviously, cannot calculate the error.

I hope this is clear enough.

Brutium

 

[repairman615]

Hello Brutium,


If you are using 2007 or 2010, then use the IFFERROR() function to deal with the error like:

=IFERROR("Here gets placed the original formula as the first argument of the iferror function" , "This text will display if the original formula yeilds error")


else if

using 2003 then an IF(ISerror("your Formula"),"Value if error" , "Your Formula" ) will also do.

In other words, a way to deal with the error might clean up the look.




Next as for the formula to get cell M9 down to cell M52

Try replacing the formula in the code:
this
"=" & wsName & "!M9"

with

"=" & wsName & "!M" & x + 9

should that work, then even this formula could include the IFERROR to cope with the errors.

Does this populate the summary as you would like?

What version are you using?

Regards,

 

[Brutium]

repairman615....


Thank you, THANK YOU!!!!!

It worked just great!!!!!
It solved my problem.

It is very conforting to know that there is, always, someone who is willing to put in the time to help others. I really appreciate all the help you have provided me.

One last question...

Is it possible to Protect my Summary sheet after the other sheets are created? If I protect it before I create the other sheets the program crashes because it needs to access the summary sheet with whatever data comes from the newly created sheets.

Thank you....

Brutium

 

[Brutium]

Once again hello repairman615.


I solved the issue by placing this bit of code

Sheets("Class").Unprotect Password:="password" '''...the macro....

and then close up at the end of the macro with

Sheets("Class").Protect Password:="password"

Once again THANK YOU for all your help....

Brutium

 

[repairman615]

Very Nice!!

Glad to be of help.