Excel 2007 Formula and VBA help

  • Thread starter Thread starter Preschool Mike
  • Start date Start date
P

Preschool Mike

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="",AO10="",AP10="",AQ10="",AR10="",AS10="",AT10="",AU10="",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
 
You might try incorporating this idea
=COUNTIF(x10:bb10,"")
if(=COUNTIF(x10:bb10,"")=addemup),1,2)
May help more if sample data seen
 
For the VBA part:

replace:
Range("'Grade1'!E5")
with:
Sheets("Grade1").Range("E5")
 
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?
 
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
 
Back
Top