Results 1 to 16 of 16

Thread: User Defined Function

  1. #1

    Thread Starter
    Member
    Join Date
    Jul 2014
    Posts
    33

    User Defined Function

    Hi.

    Not sure why my user defined function isnt working. It seems to be because of the "IF" statement.

    Code:
    Function material(x)
    
    
            material = "=IF(RC[-7]=""0"",""0"",IF(RC[-7]="""",""0"",IF(x="""",""0"",IF(RC[-7]="""",""0"",VLOOKUP(x,'I:\[PRICING.xlsx]spreadsheet'!R3C1:R62000C9,4,FALSE)))))"
    
    End Function
    Thanks

  2. #2
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: User Defined Function

    if you enter that IF statement directly into a cell, does it work?

  3. #3

    Thread Starter
    Member
    Join Date
    Jul 2014
    Posts
    33

    Re: User Defined Function

    It goes in as text, so the whole formula is displayed but doesn't calculate. The RC referencing could be an issue

  4. #4
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: User Defined Function

    try to enter it by using the arrow keys to find the cells you want, and see what the formula ends up looking like.

  5. #5

    Thread Starter
    Member
    Join Date
    Jul 2014
    Posts
    33

    Re: User Defined Function

    When I created the function I wrote the code out while recording it as a macro. I used the mouse and clicked on my reference cells, and the macro put it in RC format.

  6. #6
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: User Defined Function

    I would look at this:

    http://answers.microsoft.com/en-us/o...9-91145f1706e5

    I think your syntax is wrong. Use a normal VBA If...Then with the vLookup as part of it, something like:

    Code:
    Function getVal(x)
        If ActiveCell.Offset(0, -7).Value = 0 Then
            getVal = 0
        ElseIf ActiveCell.Offset(0, -7).Value = "" Then
            getVal = 0
        ElseIf x = "" Then   'not sure x can be ""
            getVal = 0
        Else
            'something with the vLookup here?
        End If
    End Function

  7. #7

    Thread Starter
    Member
    Join Date
    Jul 2014
    Posts
    33

    Re: User Defined Function

    ok that helps alot, thank you. But the function still does not want to take the "X" value (text) and perform a Vlookup. it just puts the entire code, including the equals, in the cell but does not calculate or put the X value in

    Function getVal(x)

    If ActiveCell.Offset(0, -7).Value = 0 Then
    getVal = 0
    ElseIf ActiveCell.Offset(0, -7).Value = "" Then
    getVal = 0

    Else
    getVal = "=VLookup(""x"",'I:\PRICING\[pricing.xlsx]spreadsheet'!$A$3:$I$62000, 4, False)"
    End If
    End Function

  8. #8
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: User Defined Function

    I'm putting this function in A1, referencing B1 and my lookup table is in D2:E6 (not including the headers in D1 and E1):

    Code:
    Function getVal(str As String)
        Dim myRange As Range
        Dim ws As Worksheet
        
        Set ws = ActiveSheet
        Set myRange = ws.Range("d2:e6") 'my lookup table
        
        If ActiveCell.Offset(0, 1) = 0 Then
            getVal = "0"
        ElseIf IsEmpty(ActiveCell.Offset(0, 1)) Then
            getVal = "0"
        Else
            getVal = Application.WorksheetFunction.VLookup(ActiveCell.Offset(0, 1), myRange, 2)
        End If
    End Function

  9. #9

    Thread Starter
    Member
    Join Date
    Jul 2014
    Posts
    33

    Re: User Defined Function

    just put this in. getting the #VALUE!

    Code:
    Function getVal(x)
    
    Dim mylookup As Range
    
    Set mylookup = Workbooks("I:\PRICING.xlsx").Worksheets("spreadsheet").Range("A3:I620000")
    
        If ActiveCell.Offset(0, -7).Value = 0 Then
            getVal = 0
        ElseIf ActiveCell.Offset(0, -7).Value = "" Then
            getVal = 0
       
        Else
            getVal = Application.WorksheetFunction.VLookup(x, mylookup, 4, False)
            
        End If
    End Function

  10. #10
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: User Defined Function

    and your lookup range is sorted by whatever "x" is, in the first column?

  11. #11

    Thread Starter
    Member
    Join Date
    Jul 2014
    Posts
    33

    Re: User Defined Function

    yes it is. I was just able to get your suggestion to work, where the lookup table is in the same workbook/sheet. i guess my trouble is getting my

    Code:
    set mylookup = workbooks().worksheets().range()
    to go into an old spreadsheet and find the data.

  12. #12
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: User Defined Function

    ah, I see. you SHOULD be able to do it that way as well, but I think you'd have to open the old sheet first.

  13. #13

    Thread Starter
    Member
    Join Date
    Jul 2014
    Posts
    33

    Re: User Defined Function

    Well even with the old spreadsheet open it's not wanting to cooperate. I just want a way to shorten up my code, so when users are on my worksheet they can have an easier code, ie "getval()" instead of several if statements and long file calls.

  14. #14
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: User Defined Function

    10.4, don't blame you for that!

    i'll try to simulate the "other file" scenario tomorrow.

  15. #15

    Thread Starter
    Member
    Join Date
    Jul 2014
    Posts
    33

    Re: User Defined Function

    So i would like to make a user defined function, since my formula is super long and could easily be screwed up by my users. I think the problem is that i cannot figure out how to use a vlookup in a user defined function. can anyone help with this one?

  16. #16
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: User Defined Function

    Hmmm...I see that I never responded to your earlier post, sorry!

    Something simple like this, when my range of data is from B1 to C4, and the lookup value is in column B:

    Code:
    Function myLook(lVal As String)
        myLook = WorksheetFunction.VLookup(lVal, ActiveSheet.Range("b1:c4"), 2)
    End Function

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width