PDA

View Full Version : [SOLVED] VBA IF functions (Help)



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

Aussiebear
12-27-2013, 09:30 PM
Try using Case Select as an alternative

GTO
12-27-2013, 09:48 PM
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
12-27-2013, 09:51 PM
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
12-28-2013, 12:56 AM
Or u can use an additional sheet to store these data, and just use a simple vlookup to build your range...

Aaron719
12-28-2013, 02:51 PM
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
12-28-2013, 02:56 PM
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
12-28-2013, 04:53 PM
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
12-28-2013, 04:56 PM
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
12-28-2013, 05:18 PM
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
12-29-2013, 09:09 PM
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.