-
Aug 25th, 2014, 09:37 AM
#1
Thread Starter
Member
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
-
Aug 25th, 2014, 09:39 AM
#2
Re: User Defined Function
if you enter that IF statement directly into a cell, does it work?
-
Aug 25th, 2014, 10:01 AM
#3
Thread Starter
Member
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
-
Aug 25th, 2014, 10:05 AM
#4
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.
-
Aug 25th, 2014, 12:16 PM
#5
Thread Starter
Member
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.
-
Aug 25th, 2014, 12:36 PM
#6
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
-
Aug 25th, 2014, 01:49 PM
#7
Thread Starter
Member
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
-
Aug 25th, 2014, 02:03 PM
#8
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
-
Aug 25th, 2014, 02:30 PM
#9
Thread Starter
Member
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
-
Aug 25th, 2014, 03:37 PM
#10
Re: User Defined Function
and your lookup range is sorted by whatever "x" is, in the first column?
-
Aug 25th, 2014, 04:08 PM
#11
Thread Starter
Member
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.
-
Aug 25th, 2014, 04:15 PM
#12
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.
-
Aug 25th, 2014, 04:19 PM
#13
Thread Starter
Member
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.
-
Aug 25th, 2014, 04:30 PM
#14
Re: User Defined Function
10.4, don't blame you for that!
i'll try to simulate the "other file" scenario tomorrow.
-
Nov 13th, 2014, 09:51 AM
#15
Thread Starter
Member
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?
-
Nov 13th, 2014, 10:14 AM
#16
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|