Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2007 Formula and VBA help
I am new to writing formulas and VBA in excel so please be as detailed with
your help as much as possible. I need help some help writing a formula and writing some VBA code. While the formula below works, I was wondering if their was a shorter and better way of writing it? =IF(AND(X10="",Y10="",Z10="",AA10="",AB10="",AB10= "",AC10="",AD10="",AE10="",AF10="",AG10="",AH10="" ,AI10="",AJ10="",AK10="",AL10="",AM10="",AN10="",A O10="",AP10="",AQ10="",AR10="",AS10="",AT10="",AU1 0="",AV10="",AW10="",AX10="",AY10="",AZ10="",BA10= "",BB10=""),0,(COUNTIF(X8:BB10,"+")/(COUNTIF(X8:BB10,"+")+COUNTIF(X8:BB10,"-")))) In english it needs to look at a range of cells (X10 thru BB10) and return 0 if they are empty. If the range contains a (+) or (-) it needs to count all the (+) and divide the total number of (+) by the total number of (+) and (-). The answer should be a percentage. My second problem, nothing to do with the first is writing some VBA code. Below is the code I have but it does not work. I don't have any experience with writing code in excel so it may look a bit strange. What I'm trying to do is collect the contents of cell BC8 on Sheet (Grade1) if the cell E5 on sheet (Grade1) says August and store the information in my Dim AugustG1A statment. Then using my InsertFirstGrade sub I want to send the information in AugustG1A to cell T7 on sheet (SpGrade1). Any help writing this correctly would be much appreciated. Dim AugustG1A As Integer Sub ClearMonths() AugustG1A = 0 End Sub Sub Collect1stGrade() 'This code does not work If Range("'Grade1'!E5") = "August" Then AugustG1A = Range("'Goal1'!BC8") End If End Sub Sub InsertFirstGrade() Range("'SpGrade1'!T7") = AugustG1A End Sub -- Mike Mast Special Education Preschool Teacher |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2007 Formula and VBA help
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2007 Formula and VBA help
Clearly you can replace the large
AND(.......) with COUNTBLANK(X10:BB10)=31 -- Gary''s Student - gsnu200901 "Preschool Mike" wrote: I am new to writing formulas and VBA in excel so please be as detailed with your help as much as possible. I need help some help writing a formula and writing some VBA code. While the formula below works, I was wondering if their was a shorter and better way of writing it? =IF(AND(X10="",Y10="",Z10="",AA10="",AB10="",AB10= "",AC10="",AD10="",AE10="",AF10="",AG10="",AH10="" ,AI10="",AJ10="",AK10="",AL10="",AM10="",AN10="",A O10="",AP10="",AQ10="",AR10="",AS10="",AT10="",AU1 0="",AV10="",AW10="",AX10="",AY10="",AZ10="",BA10= "",BB10=""),0,(COUNTIF(X8:BB10,"+")/(COUNTIF(X8:BB10,"+")+COUNTIF(X8:BB10,"-")))) In english it needs to look at a range of cells (X10 thru BB10) and return 0 if they are empty. If the range contains a (+) or (-) it needs to count all the (+) and divide the total number of (+) by the total number of (+) and (-). The answer should be a percentage. My second problem, nothing to do with the first is writing some VBA code. Below is the code I have but it does not work. I don't have any experience with writing code in excel so it may look a bit strange. What I'm trying to do is collect the contents of cell BC8 on Sheet (Grade1) if the cell E5 on sheet (Grade1) says August and store the information in my Dim AugustG1A statment. Then using my InsertFirstGrade sub I want to send the information in AugustG1A to cell T7 on sheet (SpGrade1). Any help writing this correctly would be much appreciated. Dim AugustG1A As Integer Sub ClearMonths() AugustG1A = 0 End Sub Sub Collect1stGrade() 'This code does not work If Range("'Grade1'!E5") = "August" Then AugustG1A = Range("'Goal1'!BC8") End If End Sub Sub InsertFirstGrade() Range("'SpGrade1'!T7") = AugustG1A End Sub -- Mike Mast Special Education Preschool Teacher |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2007 Formula and VBA help
For the VBA part:
replace: Range("'Grade1'!E5") with: Sheets("Grade1").Range("E5") -- Gary''s Student - gsnu200901 "Preschool Mike" wrote: I am new to writing formulas and VBA in excel so please be as detailed with your help as much as possible. I need help some help writing a formula and writing some VBA code. While the formula below works, I was wondering if their was a shorter and better way of writing it? =IF(AND(X10="",Y10="",Z10="",AA10="",AB10="",AB10= "",AC10="",AD10="",AE10="",AF10="",AG10="",AH10="" ,AI10="",AJ10="",AK10="",AL10="",AM10="",AN10="",A O10="",AP10="",AQ10="",AR10="",AS10="",AT10="",AU1 0="",AV10="",AW10="",AX10="",AY10="",AZ10="",BA10= "",BB10=""),0,(COUNTIF(X8:BB10,"+")/(COUNTIF(X8:BB10,"+")+COUNTIF(X8:BB10,"-")))) In english it needs to look at a range of cells (X10 thru BB10) and return 0 if they are empty. If the range contains a (+) or (-) it needs to count all the (+) and divide the total number of (+) by the total number of (+) and (-). The answer should be a percentage. My second problem, nothing to do with the first is writing some VBA code. Below is the code I have but it does not work. I don't have any experience with writing code in excel so it may look a bit strange. What I'm trying to do is collect the contents of cell BC8 on Sheet (Grade1) if the cell E5 on sheet (Grade1) says August and store the information in my Dim AugustG1A statment. Then using my InsertFirstGrade sub I want to send the information in AugustG1A to cell T7 on sheet (SpGrade1). Any help writing this correctly would be much appreciated. Dim AugustG1A As Integer Sub ClearMonths() AugustG1A = 0 End Sub Sub Collect1stGrade() 'This code does not work If Range("'Grade1'!E5") = "August" Then AugustG1A = Range("'Goal1'!BC8") End If End Sub Sub InsertFirstGrade() Range("'SpGrade1'!T7") = AugustG1A End Sub -- Mike Mast Special Education Preschool Teacher |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2007 Formula and VBA help
The formula you suggested worked well. Much easier to write. Still having
trouble with the VBA. I tried what you suggested and got a runtime error '9': Subscript out of range. Here is how I wrote it: Sub Collect1stGrade() If Sheets("Grade1").Range("E5") = "August" Then AugustG1A = Sheets("Goal1").Range("BC8") End If End Sub Any suggestions? -- Mike Mast Special Education Preschool Teacher "Gary''s Student" wrote: For the VBA part: replace: Range("'Grade1'!E5") with: Sheets("Grade1").Range("E5") -- Gary''s Student - gsnu200901 "Preschool Mike" wrote: I am new to writing formulas and VBA in excel so please be as detailed with your help as much as possible. I need help some help writing a formula and writing some VBA code. While the formula below works, I was wondering if their was a shorter and better way of writing it? =IF(AND(X10="",Y10="",Z10="",AA10="",AB10="",AB10= "",AC10="",AD10="",AE10="",AF10="",AG10="",AH10="" ,AI10="",AJ10="",AK10="",AL10="",AM10="",AN10="",A O10="",AP10="",AQ10="",AR10="",AS10="",AT10="",AU1 0="",AV10="",AW10="",AX10="",AY10="",AZ10="",BA10= "",BB10=""),0,(COUNTIF(X8:BB10,"+")/(COUNTIF(X8:BB10,"+")+COUNTIF(X8:BB10,"-")))) In english it needs to look at a range of cells (X10 thru BB10) and return 0 if they are empty. If the range contains a (+) or (-) it needs to count all the (+) and divide the total number of (+) by the total number of (+) and (-). The answer should be a percentage. My second problem, nothing to do with the first is writing some VBA code. Below is the code I have but it does not work. I don't have any experience with writing code in excel so it may look a bit strange. What I'm trying to do is collect the contents of cell BC8 on Sheet (Grade1) if the cell E5 on sheet (Grade1) says August and store the information in my Dim AugustG1A statment. Then using my InsertFirstGrade sub I want to send the information in AugustG1A to cell T7 on sheet (SpGrade1). Any help writing this correctly would be much appreciated. Dim AugustG1A As Integer Sub ClearMonths() AugustG1A = 0 End Sub Sub Collect1stGrade() 'This code does not work If Range("'Grade1'!E5") = "August" Then AugustG1A = Range("'Goal1'!BC8") End If End Sub Sub InsertFirstGrade() Range("'SpGrade1'!T7") = AugustG1A End Sub -- Mike Mast Special Education Preschool Teacher |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2007 Formula and VBA help
Three things to look at:
1. Make the the tab names are spelt correctly 2. Use .Value If Sheets("Grade1").Range("E5").Value = "August" Then AugustG1A = Sheets("Goal1").Range("BC8").Value 3. Make sure the value in cell BC8 matches the type of variable August1A -- Gary''s Student - gsnu200901 "Preschool Mike" wrote: The formula you suggested worked well. Much easier to write. Still having trouble with the VBA. I tried what you suggested and got a runtime error '9': Subscript out of range. Here is how I wrote it: Sub Collect1stGrade() If Sheets("Grade1").Range("E5") = "August" Then AugustG1A = Sheets("Goal1").Range("BC8") End If End Sub Any suggestions? -- Mike Mast Special Education Preschool Teacher "Gary''s Student" wrote: For the VBA part: replace: Range("'Grade1'!E5") with: Sheets("Grade1").Range("E5") -- Gary''s Student - gsnu200901 "Preschool Mike" wrote: I am new to writing formulas and VBA in excel so please be as detailed with your help as much as possible. I need help some help writing a formula and writing some VBA code. While the formula below works, I was wondering if their was a shorter and better way of writing it? =IF(AND(X10="",Y10="",Z10="",AA10="",AB10="",AB10= "",AC10="",AD10="",AE10="",AF10="",AG10="",AH10="" ,AI10="",AJ10="",AK10="",AL10="",AM10="",AN10="",A O10="",AP10="",AQ10="",AR10="",AS10="",AT10="",AU1 0="",AV10="",AW10="",AX10="",AY10="",AZ10="",BA10= "",BB10=""),0,(COUNTIF(X8:BB10,"+")/(COUNTIF(X8:BB10,"+")+COUNTIF(X8:BB10,"-")))) In english it needs to look at a range of cells (X10 thru BB10) and return 0 if they are empty. If the range contains a (+) or (-) it needs to count all the (+) and divide the total number of (+) by the total number of (+) and (-). The answer should be a percentage. My second problem, nothing to do with the first is writing some VBA code. Below is the code I have but it does not work. I don't have any experience with writing code in excel so it may look a bit strange. What I'm trying to do is collect the contents of cell BC8 on Sheet (Grade1) if the cell E5 on sheet (Grade1) says August and store the information in my Dim AugustG1A statment. Then using my InsertFirstGrade sub I want to send the information in AugustG1A to cell T7 on sheet (SpGrade1). Any help writing this correctly would be much appreciated. Dim AugustG1A As Integer Sub ClearMonths() AugustG1A = 0 End Sub Sub Collect1stGrade() 'This code does not work If Range("'Grade1'!E5") = "August" Then AugustG1A = Range("'Goal1'!BC8") End If End Sub Sub InsertFirstGrade() Range("'SpGrade1'!T7") = AugustG1A End Sub -- Mike Mast Special Education Preschool Teacher |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula help in Excel 2007 | Excel Worksheet Functions | |||
formula excel 2007 | Excel Discussion (Misc queries) | |||
I need a formula for excel 2007 | Excel Discussion (Misc queries) | |||
Excel 2007 formula help | Excel Worksheet Functions | |||
excel 2007 formula | Excel Worksheet Functions |