Aaron719
12-27-2013, 09:00 PM
Hello all,
Thanks for any help in advance. I'm new to the site and new to using VBA. I've read through your forum for help, but this will be my first post here.
Software: Excel 2013
My problem (hopefully I can explain it well):
I am trying to sum a range of cells and which range of cells depends on what the values are in two completely different cells. I can write this fairly easily using standard "IF/AND" functions within the cell, BUT... I want to do this particular command for each day of the month and for all 12 months.
Below I have what I initially had written out using the standard functions given in Excel.
=IF(AND(C3=1,(DAY($E$1)=1)), (SUM(BG14:BO14)), (FALSE))
This function does what it is supposed to, but it is not written for all the days in January and is not written for all the the other months; February, March... and so on. This would be insane to type all of this out and probably not possible.
So I started writing it in VBA, but my biggest problems are: 1) not being able to manually select the two different cells I want it to reference, and 2) My organization and using the correct "terms" so that it will quit throwing errors at me. I will include a sample of what I have so far below.
1) "By manually select the cells" I mean, if I were to type the name of the function into the cell, I want to be able to chose the cells it references. EX: "=RemainingIncome(C3, F5)" Where C3 is the month and F5 is the day of the month. If C3=1 and F5=1 then sum a specific range.
2) I'm not sure what exactly pVal means or does, or its limitations, but I know it allows me to do the above, but I can only get it to work with 1 cell reference, not 2. Also I don't know if I'm on the right track below with my GoTo, rVal, and the rest. This was created just from browsing around.
The VBA I have written is below. I can't tell if I'm horribly wrong or just missing one thing. At the moment I get an "error" that says I need to include "Else without if". I'm not sure if I need to do this somewhere or its confused as to what I'm trying to do. Any help is greatly appreciated.
Function RemainingIncome(pVal As String) As Long
If pVal = "1" Then GoTo JAN
ElseIf pVal = "2" Then GoTo FEB
ElseIf pVal = "3" Then GoTo MAR
ElseIf pVal = "4" Then GoTo APR
ElseIf pVal = "5" Then GoTo MAY
ElseIf pVal = "6" Then GoTo JUN
ElseIf pVal = "7" Then GoTo JUL
ElseIf pVal = "8" Then GoTo AUG
ElseIf pVal = "9" Then GoTo SEP
ElseIf pVal = "10" Then GoTo Oct
ElseIf pVal = "11" Then GoTo NOV
ElseIf pVal = "12" Then GoTo DEC
End If
JAN:
If rVal = "1" Then
RemainingIncome = Sum.Range("G14:BO14")
ElseIf rVal = "2" Then
RemainingIncome = Sum.Range("I14:BO14")
ElseIf rVal = "3" Then
RemainingIncome = Sum.Range("K14:BO14")
ElseIf rVal = "4" Then
RemainingIncome = Sum.Range("M14:BO14")
ElseIf rVal = "5" Then
RemainingIncome = Sum.Range("O14:BO14")
ElseIf rVal = "6" Then
RemainingIncome = Sum.Range("Q14:BO14")
ElseIf rVal = "7" Then
RemainingIncome = Sum.Range("S14:BO14")
ElseIf rVal = "8" Then
RemainingIncome = Sum.Range("U14:BO14")
ElseIf rVal = "9" Then
RemainingIncome = Sum.Range("W14:BO14")
ElseIf rVal = "10" Then
RemainingIncome = Sum.Range("Y14:BO14")
ElseIf rVal = "11" Then
RemainingIncome = Sum.Range("AA14:BO14")
ElseIf rVal = "12" Then
RemainingIncome = Sum.Range("AC14:BO14")
ElseIf rVal = "13" Then
RemainingIncome = Sum.Range("AE14:BO14")
ElseIf rVal = "14" Then
RemainingIncome = Sum.Range("AG14:BO14")
ElseIf rVal = "15" Then
RemainingIncome = Sum.Range("AI14:BO14")
ElseIf rVal = "16" Then
RemainingIncome = Sum.Range("AK14:BO14")
ElseIf rVal = "17" Then
RemainingIncome = Sum.Range("AM14:BO14")
ElseIf rVal = "18" Then
RemainingIncome = Sum.Range("AO14:BO14")
ElseIf rVal = "19" Then
RemainingIncome = Sum.Range("AQ14:BO14")
ElseIf rVal = "20" Then
RemainingIncome = Sum.Range("AS14:BO14")
ElseIf rVal = "21" Then
RemainingIncome = Sum.Range("AU14:BO14")
ElseIf rVal = "22" Then
RemainingIncome = Sum.Range("AW14:BO14")
ElseIf rVal = "23" Then
RemainingIncome = Sum.Range("AY14:BO14")
ElseIf rVal = "24" Then
RemainingIncome = Sum.Range("BA14:BO14")
ElseIf rVal = "25" Then
RemainingIncome = Sum.Range("BC14:BO14")
ElseIf rVal = "26" Then
RemainingIncome = Sum.Range("BE14:BO14")
ElseIf rVal = "27" Then
RemainingIncome = Sum.Range("BG14:BO14")
ElseIf rVal = "28" Then
RemainingIncome = Sum.Range("BI14:BO14")
ElseIf rVal = "29" Then
RemainingIncome = Sum.Range("BK14:BO14")
ElseIf rVal = "30" Then
RemainingIncome = Sum.Range("BM14:BO14")
ElseIf rVal = "31" Then
RemainingIncome = Sum.Range("BO14:BO14")
End If
FEB:
If rVal = "1" Then
RemainingIncome = Sum.Range("G32:BO32")
ElseIf rVal = "2" Then
RemainingIncome = Sum.Range("I32:BO32")
ElseIf rVal = "3" Then
RemainingIncome = Sum.Range("K32:BO32")
ElseIf rVal = "4" Then
RemainingIncome = Sum.Range("M32:BO32")
ElseIf rVal = "5" Then
RemainingIncome = Sum.Range("O32:BO32")
ElseIf rVal = "6" Then
RemainingIncome = Sum.Range("Q32:BO32")
ElseIf rVal = "7" Then
RemainingIncome = Sum.Range("S32:BO32")
ElseIf rVal = "8" Then
RemainingIncome = Sum.Range("U32:BO32")
ElseIf rVal = "9" Then
RemainingIncome = Sum.Range("W32:BO32")
ElseIf rVal = "10" Then
RemainingIncome = Sum.Range("Y32:BO32")
ElseIf rVal = "11" Then
RemainingIncome = Sum.Range("AA32:BO32")
ElseIf rVal = "12" Then
RemainingIncome = Sum.Range("AC32:BO32")
ElseIf rVal = "13" Then
RemainingIncome = Sum.Range("AE32:BO32")
ElseIf rVal = "14" Then
RemainingIncome = Sum.Range("AG32:BO32")
ElseIf rVal = "15" Then
RemainingIncome = Sum.Range("AI32:BO32")
ElseIf rVal = "16" Then
RemainingIncome = Sum.Range("AK32:BO32")
ElseIf rVal = "17" Then
RemainingIncome = Sum.Range("AM32:BO32")
ElseIf rVal = "18" Then
RemainingIncome = Sum.Range("AO32:BO32")
ElseIf rVal = "19" Then
RemainingIncome = Sum.Range("AQ32:BO32")
ElseIf rVal = "20" Then
RemainingIncome = Sum.Range("AS32:BO32")
ElseIf rVal = "21" Then
RemainingIncome = Sum.Range("AU32:BO32")
ElseIf rVal = "22" Then
RemainingIncome = Sum.Range("AW32:BO32")
ElseIf rVal = "23" Then
RemainingIncome = Sum.Range("AY32:BO32")
ElseIf rVal = "24" Then
RemainingIncome = Sum.Range("BA32:BO32")
ElseIf rVal = "25" Then
RemainingIncome = Sum.Range("BC32:BO32")
ElseIf rVal = "26" Then
RemainingIncome = Sum.Range("BE32:BO32")
ElseIf rVal = "27" Then
RemainingIncome = Sum.Range("BG32:BO32")
ElseIf rVal = "28" Then
RemainingIncome = Sum.Range("BI32:BO32")
End If
End Function
Thanks for any help in advance. I'm new to the site and new to using VBA. I've read through your forum for help, but this will be my first post here.
Software: Excel 2013
My problem (hopefully I can explain it well):
I am trying to sum a range of cells and which range of cells depends on what the values are in two completely different cells. I can write this fairly easily using standard "IF/AND" functions within the cell, BUT... I want to do this particular command for each day of the month and for all 12 months.
Below I have what I initially had written out using the standard functions given in Excel.
=IF(AND(C3=1,(DAY($E$1)=1)), (SUM(BG14:BO14)), (FALSE))
This function does what it is supposed to, but it is not written for all the days in January and is not written for all the the other months; February, March... and so on. This would be insane to type all of this out and probably not possible.
So I started writing it in VBA, but my biggest problems are: 1) not being able to manually select the two different cells I want it to reference, and 2) My organization and using the correct "terms" so that it will quit throwing errors at me. I will include a sample of what I have so far below.
1) "By manually select the cells" I mean, if I were to type the name of the function into the cell, I want to be able to chose the cells it references. EX: "=RemainingIncome(C3, F5)" Where C3 is the month and F5 is the day of the month. If C3=1 and F5=1 then sum a specific range.
2) I'm not sure what exactly pVal means or does, or its limitations, but I know it allows me to do the above, but I can only get it to work with 1 cell reference, not 2. Also I don't know if I'm on the right track below with my GoTo, rVal, and the rest. This was created just from browsing around.
The VBA I have written is below. I can't tell if I'm horribly wrong or just missing one thing. At the moment I get an "error" that says I need to include "Else without if". I'm not sure if I need to do this somewhere or its confused as to what I'm trying to do. Any help is greatly appreciated.
Function RemainingIncome(pVal As String) As Long
If pVal = "1" Then GoTo JAN
ElseIf pVal = "2" Then GoTo FEB
ElseIf pVal = "3" Then GoTo MAR
ElseIf pVal = "4" Then GoTo APR
ElseIf pVal = "5" Then GoTo MAY
ElseIf pVal = "6" Then GoTo JUN
ElseIf pVal = "7" Then GoTo JUL
ElseIf pVal = "8" Then GoTo AUG
ElseIf pVal = "9" Then GoTo SEP
ElseIf pVal = "10" Then GoTo Oct
ElseIf pVal = "11" Then GoTo NOV
ElseIf pVal = "12" Then GoTo DEC
End If
JAN:
If rVal = "1" Then
RemainingIncome = Sum.Range("G14:BO14")
ElseIf rVal = "2" Then
RemainingIncome = Sum.Range("I14:BO14")
ElseIf rVal = "3" Then
RemainingIncome = Sum.Range("K14:BO14")
ElseIf rVal = "4" Then
RemainingIncome = Sum.Range("M14:BO14")
ElseIf rVal = "5" Then
RemainingIncome = Sum.Range("O14:BO14")
ElseIf rVal = "6" Then
RemainingIncome = Sum.Range("Q14:BO14")
ElseIf rVal = "7" Then
RemainingIncome = Sum.Range("S14:BO14")
ElseIf rVal = "8" Then
RemainingIncome = Sum.Range("U14:BO14")
ElseIf rVal = "9" Then
RemainingIncome = Sum.Range("W14:BO14")
ElseIf rVal = "10" Then
RemainingIncome = Sum.Range("Y14:BO14")
ElseIf rVal = "11" Then
RemainingIncome = Sum.Range("AA14:BO14")
ElseIf rVal = "12" Then
RemainingIncome = Sum.Range("AC14:BO14")
ElseIf rVal = "13" Then
RemainingIncome = Sum.Range("AE14:BO14")
ElseIf rVal = "14" Then
RemainingIncome = Sum.Range("AG14:BO14")
ElseIf rVal = "15" Then
RemainingIncome = Sum.Range("AI14:BO14")
ElseIf rVal = "16" Then
RemainingIncome = Sum.Range("AK14:BO14")
ElseIf rVal = "17" Then
RemainingIncome = Sum.Range("AM14:BO14")
ElseIf rVal = "18" Then
RemainingIncome = Sum.Range("AO14:BO14")
ElseIf rVal = "19" Then
RemainingIncome = Sum.Range("AQ14:BO14")
ElseIf rVal = "20" Then
RemainingIncome = Sum.Range("AS14:BO14")
ElseIf rVal = "21" Then
RemainingIncome = Sum.Range("AU14:BO14")
ElseIf rVal = "22" Then
RemainingIncome = Sum.Range("AW14:BO14")
ElseIf rVal = "23" Then
RemainingIncome = Sum.Range("AY14:BO14")
ElseIf rVal = "24" Then
RemainingIncome = Sum.Range("BA14:BO14")
ElseIf rVal = "25" Then
RemainingIncome = Sum.Range("BC14:BO14")
ElseIf rVal = "26" Then
RemainingIncome = Sum.Range("BE14:BO14")
ElseIf rVal = "27" Then
RemainingIncome = Sum.Range("BG14:BO14")
ElseIf rVal = "28" Then
RemainingIncome = Sum.Range("BI14:BO14")
ElseIf rVal = "29" Then
RemainingIncome = Sum.Range("BK14:BO14")
ElseIf rVal = "30" Then
RemainingIncome = Sum.Range("BM14:BO14")
ElseIf rVal = "31" Then
RemainingIncome = Sum.Range("BO14:BO14")
End If
FEB:
If rVal = "1" Then
RemainingIncome = Sum.Range("G32:BO32")
ElseIf rVal = "2" Then
RemainingIncome = Sum.Range("I32:BO32")
ElseIf rVal = "3" Then
RemainingIncome = Sum.Range("K32:BO32")
ElseIf rVal = "4" Then
RemainingIncome = Sum.Range("M32:BO32")
ElseIf rVal = "5" Then
RemainingIncome = Sum.Range("O32:BO32")
ElseIf rVal = "6" Then
RemainingIncome = Sum.Range("Q32:BO32")
ElseIf rVal = "7" Then
RemainingIncome = Sum.Range("S32:BO32")
ElseIf rVal = "8" Then
RemainingIncome = Sum.Range("U32:BO32")
ElseIf rVal = "9" Then
RemainingIncome = Sum.Range("W32:BO32")
ElseIf rVal = "10" Then
RemainingIncome = Sum.Range("Y32:BO32")
ElseIf rVal = "11" Then
RemainingIncome = Sum.Range("AA32:BO32")
ElseIf rVal = "12" Then
RemainingIncome = Sum.Range("AC32:BO32")
ElseIf rVal = "13" Then
RemainingIncome = Sum.Range("AE32:BO32")
ElseIf rVal = "14" Then
RemainingIncome = Sum.Range("AG32:BO32")
ElseIf rVal = "15" Then
RemainingIncome = Sum.Range("AI32:BO32")
ElseIf rVal = "16" Then
RemainingIncome = Sum.Range("AK32:BO32")
ElseIf rVal = "17" Then
RemainingIncome = Sum.Range("AM32:BO32")
ElseIf rVal = "18" Then
RemainingIncome = Sum.Range("AO32:BO32")
ElseIf rVal = "19" Then
RemainingIncome = Sum.Range("AQ32:BO32")
ElseIf rVal = "20" Then
RemainingIncome = Sum.Range("AS32:BO32")
ElseIf rVal = "21" Then
RemainingIncome = Sum.Range("AU32:BO32")
ElseIf rVal = "22" Then
RemainingIncome = Sum.Range("AW32:BO32")
ElseIf rVal = "23" Then
RemainingIncome = Sum.Range("AY32:BO32")
ElseIf rVal = "24" Then
RemainingIncome = Sum.Range("BA32:BO32")
ElseIf rVal = "25" Then
RemainingIncome = Sum.Range("BC32:BO32")
ElseIf rVal = "26" Then
RemainingIncome = Sum.Range("BE32:BO32")
ElseIf rVal = "27" Then
RemainingIncome = Sum.Range("BG32:BO32")
ElseIf rVal = "28" Then
RemainingIncome = Sum.Range("BI32:BO32")
End If
End Function