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