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

Excel 2007 Formula and VBA help
 

You might try incorporating this idea
=COUNTIF(x10:bb10,"")
if(=COUNTIF(x10:bb10,"")=addemup),1,2)
May help more if sample data seen

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Preschool Mike" wrote in message
...
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

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

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

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

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