Function Revision

cstimart

Well-known Member
Joined
Feb 25, 2010
Messages
1,180
I am terrible with writing functions using the R1C1 style. I assume this can be written better that I have it.

Code:
Function Revenue_CQ(x As String)
Dim rw As Integer
If x = "C" Then
    rw = 12
ElseIf x = "P" Then
    rw = 61
End If
    With Sheets("Info")
        If Quarter() = "1Q" Then
            Revenue_CQ = .Range("C" & rw)
        ElseIf Quarter() = "2Q" Then
            Revenue_CQ = .Range("I" & rw)
        ElseIf Quarter() = "3Q" Then
            Revenue_CQ = .Range("O" & rw)
        ElseIf Quarter() = "4Q" Then
            Revenue_CQ = .Range("U" & rw)
        End If
    End With
End Function
Help?
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,916
Office Version
  1. 365
Platform
  1. Windows
With functions, you usually do not reference sheets and cells directly within the function. You usually feed it in a bunch of values or cell references, and then program it to do the calculations off of that.

Can you explain what the function is supposed to be doing, and how it should work?
 

cstimart

Well-known Member
Joined
Feb 25, 2010
Messages
1,180
it's basically going to the Info sheet and grabbing a value, depending on the value input into the function and the value in Quarter().

therefore, a cell on my spreadsheet contained =Revenue_CQ("P"), and we are looking a 1st Quarter data, the function would go to the Info sheet and extract the value in range C61.

I just figure that the "C" - "I" - "O" - "U" columns could be utilized via the R1C1 option...driven from the quarter() value, using (Left(Quarter(),1) *6) - 3 and then eliminating the If/Then/Else?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,916
Office Version
  1. 365
Platform
  1. Windows
Where does Quarter() come from?
Is this another UDF that you have?
 

cstimart

Well-known Member
Joined
Feb 25, 2010
Messages
1,180

ADVERTISEMENT

Where does Quarter() come from?
Is this another UDF that you have?

Yes, Quarter() is a Function I have that derives the quarter from the file name.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,916
Office Version
  1. 365
Platform
  1. Windows
I am thinking it might be possible that you may not need any UDFs at all, that you might be able to do it all through a standard Excel function.

Can you post the code for your Quarter function? I find it odd that it is doing the calculation without any inputs.
 

cstimart

Well-known Member
Joined
Feb 25, 2010
Messages
1,180

ADVERTISEMENT

Code:
Function Quarter()
    Dim spacePos As Integer
    Quarter = Application.ActiveWorkbook.FullName
    spacePos = InStr(Quarter, "CONTRACT")
        Quarter = Mid$(Quarter, spacePos - 3, 2)
End Function
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,916
Office Version
  1. 365
Platform
  1. Windows
Here is how I would write the function:
Code:
Function Revenue_CQ(x As String, Quarter As String)
 
    Dim rw As Integer
    Dim qtr As Byte
    
    Select Case x
        Case "C"
            rw = 12
        Case "P"
            rw = 61
'   What to do if x not equal to "P" or "C"?
        Case Else
            Revenue_CQ = 0
            Exit Function
    End Select
    
'   Get the numeric quarter number (or exit function if no quarter)
    If Len(Quarter) > 0 Then
        qtr = Left(Quarter, 1) + 0
    Else
        Revenue_CQ = 0
        Exit Function
    End If
        
'   Return revenue
    Revenue_CQ = Sheets("Info").Cells(rw, qtr * 6 - 3)
    
End Function
Note that I made use of the fact that columnc C, I, O, and U are 6 columns from each other. So I was table to collapse the lookup into a single line, making use of the number from the quarter.

Note also that I do not call Quarter() from inside the function. Instead, I added a variable to this Function to feed it in. So, if you are calling this function from a Spreadsheet, your formula might look like this:

=Revenue_CQ("P",Quarter())
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,916
Office Version
  1. 365
Platform
  1. Windows
BTW, you could keep the Quarter function call in this function, if you like. I just put it in the arguments of your function instead to give it more flexibility (then you could use this function and feed it values from places other than the file name, if need be).
 

Watch MrExcel Video

Forum statistics

Threads
1,122,577
Messages
5,596,996
Members
414,116
Latest member
sfullnet

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top