VBA IF functions (Help)

[Aaron719]

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

[Aussiebear]

Try using Case Select as an alternative

[GTO]

Greetings Aaron,

Let me be the first to welcome you to vbaexpress :-) Although I don't get to spend as much time here as I'd like, I can assure you that you will be glad you joined. There's great folks here, who are both helpful and patient, so no worries as to "starting out".

Now to your problem - a couple of initial observations:

The IF...THEN...ELSE should be in block format.

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:
  
  ...remainder of code...

After that, if you are using Option Explicit (You should be IMO), you may receive an error for 'rVal' not being declared. That is, unless it is declared at module or global level, and just omitted from what you posted.

My other observation is more general. I imagine you will receive some good suggestions for shortening up the code a bit, or at least laid out without all the GoTo's. Might I suggest that you post the workbook (you can attach it to your next post). If it contains sensitive data, representative data can be substituted of course, but this gets us both looking at the same thing.

Hope that gets the ball rolling for you, and again, welcome to VBAX!

Mark

[GTO]

Grrr....!

Just if anyone knows - how do we now add italics, font color change, etc, to code (within the Code tags) and have it render correctly?

[L@ja]

Or u can use an additional sheet to store these data, and just use a simple vlookup to build your range...

[Aaron719]

Aussiebear:
I don't think I can use Case Select because I have two conditions that need to be met before I can get a result. Or can I?

GTO:
Thanks for the welcome. I don't think I'm going to post the whole workbook as it is pretty elaborate at the moment and it has a lot of personal data in it. Thanks for the offer though.

So to declare rVal should I have something like this....

Function RemainingIncome(pVal As String) As Long, (rVal As String) As Long

[Aaron719]

So i wrote it as Function RemaingIncome(pVal As String, rVal As String) As Long

That seems to be correct... when I hover over pVal in the code it says "pVal = 1" which is the value of the cell it depends on. Also rVal says "rVal=31" which is also true.

Now... I get "Identifier under cursor is not recognized" for the line above. It is highlighted in yellow. I'm not sure what this means.

Edit:
Oh, and I also wrote my code a little neater like you said GTO. I put it in block format.

[GTO]

So i wrote it as Function RemaingIncome(pVal As String, rVal As String) As Long

That seems to be correct... when I hover over pVal in the code it says "pVal = 1" which is the value of the cell it depends on. Also rVal says "rVal=31" which is also true.[/QUOTE]

That is correct, any arguments passed are done exactly as your second try.

Now... I get "Identifier under cursor is not recognized" for the line above. It is highlighted in yellow. I'm not sure what this means.

Sorry, what "line above" are you referring to?

Mark

[Paul_Hossler]

If the data is laid (layed??) out neatly as the code seems to imply, I'd just index into in.

1. I had no idea what pVal was so I assumed it was the day
2. I passed the parameters as Longs and returned a Double, since if we're talking MY income, I want the pennies also

Option Explicit
Function RemainingIncome(pVal As Long, rVal As Long) As Double
    Dim iRow As Long, iCol As Long
    Dim rStart As Range, rEnd As Range
    iRow = 14 + (pVal - 1) * 18
    '   pval = 1 then iRow = 14 + (1-1) * 18 = 14
    '   pval = 2 then iRow = 14 + (2-1) * 18 = 14 + 18 = 32
    '   pval = 3 then iRow = 14 + (3-1) * 18 = 14 + 36 = 50
    iCol = 7 + (rVal - 1) * 2
    '   rval = 1 then iCol = 7 + (1-1) * 2 = 7 + 0 = 7 or Col G
    '   rval = 2 then iCol = 7 + (2-1) * 2 = 7 + 2 =  9 or Col I
    
    Set rStart = Cells(iRow, iCol)
    MsgBox rStart.Address
    Set rEnd = Cells(iRow, 67)
    MsgBox rEnd.Address
    RemainingIncome = Application.WorksheetFunction.Sum(Range(rStart, rEnd))
End Function

Paul

PS not tested with any real data and you should comment out or delete the 2 MsgBox statements before running or a lot of data since they're execute every time

[Bob Phillips]

You could use Select Case as AussieBear suggests, but I also think you could do it with a simple formula

=SUM(INDEX(G1:BO212,C3*18-4,DAY($E$1)*2-1):INDEX(BO1:BO212,C3*18-4))

I am assuming each month offsets by anotrher 18 rows.

[Aaron719]

Thank you so much everyone! It does exactly what I need it to, thanks to all of you. I ended up using xld's solution of using index, and it works perfect. I thought I'd keep it simple when given the opportunity.

I learned quite a bit in just this small discussion. I'll be referencing this thread for future coding. Really I couldn't be happier. I can tell this is a nice and intelligent community. Hopefully I can pass this help on within these boards.

Thanks again.