Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA formula building
hello,
i try to write a function corresponding to index and match functions used in excel spreadsheets.. i have sth like this: Function FindID(c As Variant, x As Integer) 'c - file (ie. [file1.xls] 'x - arkusz (ie. 4 FindID = "=INDEX('" & c & x & "'!R8C3:R8C154,1,MATCH(RC3,'" & c & x & "'!R15C3:R15C154,0))" End Function is it possible to build forumula in such a way? best |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA formula building
Function FindID(c As Variant, x As Integer)
'c - file (ie. [file1.xls] 'x - arkusz (ie. 4 s = "=INDEX('" & c & x & "'!R8C3:R8C154,1,MATCH(RC3,'" & c & x & "'!R15C3:R15C154,0))" FindID = Evaluate(s) End Function -- Regards, Tom Ogilvy "pm" wrote in message ... hello, i try to write a function corresponding to index and match functions used in excel spreadsheets.. i have sth like this: Function FindID(c As Variant, x As Integer) 'c - file (ie. [file1.xls] 'x - arkusz (ie. 4 FindID = "=INDEX('" & c & x & "'!R8C3:R8C154,1,MATCH(RC3,'" & c & x & "'!R15C3:R15C154,0))" End Function is it possible to build forumula in such a way? best |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA formula building
Tom Ogilvy wrote:
Function FindID(c As Variant, x As Integer) 'c - file (ie. [file1.xls] 'x - arkusz (ie. 4 s = "=INDEX('" & c & x & "'!R8C3:R8C154,1,MATCH(RC3,'" & c & x & "'!R15C3:R15C154,0))" FindID = Evaluate(s) End Function it doesn't work :/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA formula building
I had to change x to String so I could pass it a sheet name, but once I did
that and put my workbook in R1C1 mode and entered data in the appropriate places, it worked for me =findid("[aa_test.xls]","Sheet1") -- Regards, Tom Ogilvy "pm" wrote in message ... Tom Ogilvy wrote: Function FindID(c As Variant, x As Integer) 'c - file (ie. [file1.xls] 'x - arkusz (ie. 4 s = "=INDEX('" & c & x & "'!R8C3:R8C154,1,MATCH(RC3,'" & c & x & "'!R15C3:R15C154,0))" FindID = Evaluate(s) End Function it doesn't work :/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA formula building
Tom Ogilvy wrote:
I had to change x to String so I could pass it a sheet name, but once I did that and put my workbook in R1C1 mode and entered data in the appropriate places, it worked for me =findid("[aa_test.xls]","Sheet1") now, function looks like this: Function FindID(c As Variant, x As String) 'c - file 'x - sheet s = "=INDEX('" & c & x & "'!R8C3:R8C154,1,MATCH(RC3,'" & c & x & "'!R15C3:R15C154,0))" FindID = Evaluate(s) End Function and formula returns #ARG! arguments c and x are results of formulas in other cells i use this function in this way: =findid(C5;B3) value in C5 (c) is [names_en.xls] value in B3 (x) is 4 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA formula building
Unless your sheet is named 4, then there is no way that will work. If you
want to refer to the 4th sheet in the tab order, then your function would require more work: Function FindID(c As Variant, x1 As Integer) 'c - file (ie. [file1.xls] 'x - arkusz (ie. 4 x = Worksheets(x1).Name s = "=INDEX('" & c & x & "'!R8C3:R8C154,1,MATCH(RC3,'" & c & x & "'!R15C3:R15C154,0))" FindID = Evaluate(s) End Function -- Regards, Tom Ogilvy "pm" wrote in message ... Tom Ogilvy wrote: I had to change x to String so I could pass it a sheet name, but once I did that and put my workbook in R1C1 mode and entered data in the appropriate places, it worked for me =findid("[aa_test.xls]","Sheet1") now, function looks like this: Function FindID(c As Variant, x As String) 'c - file 'x - sheet s = "=INDEX('" & c & x & "'!R8C3:R8C154,1,MATCH(RC3,'" & c & x & "'!R15C3:R15C154,0))" FindID = Evaluate(s) End Function and formula returns #ARG! arguments c and x are results of formulas in other cells i use this function in this way: =findid(C5;B3) value in C5 (c) is [names_en.xls] value in B3 (x) is 4 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA formula building
Tom napisal:
Unless your sheet is named 4, then there is no way that will work. i cannot modyfie sheet's name :/ is there any way to resolve this problem? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA formula building
If your sheet is named 4, then it shouldn't be a problem (it worked for me
when I named my sheet "4". However, you also said your formula looked like =findid(C5;B3) which shows your sheet is using A1 notation. You need to move to R1C1 notation or it won't work (in my testing). Otherwise you need to change your function to use A1 notation. -- Regards, Tom Ogilvy "pm" wrote in message ... Tom napisal: Unless your sheet is named 4, then there is no way that will work. i cannot modyfie sheet's name :/ is there any way to resolve this problem? |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA formula building
Tom Ogilvy wrote:
which shows your sheet is using A1 notation. You need to move to R1C1 notation or it won't work (in my testing). Otherwise you need to change your function to use A1 notation. great!! thank you :) |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA formula building
pm wrote:
thank you :) but i have one more question i changed my function: Function FindID(c As Variant, x As String, y As Range) 'c - file 'x - sheet 'y - value i look for s = "=INDEX('" & c & x & "'!C8:EX8,1,MATCH(" & y.Value & ",'" & c & x & "'!C15:EX15,0))" FindID = Evaluate(s) End Function and formula: =findid($C$5;$B$3;$C23) and result #N/D! |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA formula building
That would mean the Value is not found. I would guess it is being returned
by the match function. if y.value is a number and you are looking for numbers and the lookup range contains numbers, then I would expect it to work. If y.value is a string, then I would expect a #Name error. -- Regards, Tom Ogilvy "pm" wrote in message ... pm wrote: thank you :) but i have one more question i changed my function: Function FindID(c As Variant, x As String, y As Range) 'c - file 'x - sheet 'y - value i look for s = "=INDEX('" & c & x & "'!C8:EX8,1,MATCH(" & y.Value & ",'" & c & x & "'!C15:EX15,0))" FindID = Evaluate(s) End Function and formula: =findid($C$5;$B$3;$C23) and result #N/D! |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA formula building
Tom Ogilvy wrote:
if y.value is a number and you are looking for numbers and the lookup range contains numbers, then I would expect it to work. If y.value is a string, then I would expect a #Name error. y.value is not a number (it is a 6digit text) and is in lookup range (but range consist 6digit texts) |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA formula building
pm napisal:
y.value is not a number (it is a 6digit text) and is in lookup range (but range consist 6digit texts) s = "=INDEX('" & c & x & "'!C8:EX8,1,MATCH(" & y.Address & ",'" & c & x & "'!C15:EX15,0))" y.Address ok. thanks for help anyway :) |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA formula building
pm wrote:
s = "=INDEX('" & c & x & "'!C8:EX8,1,MATCH(" & y.Address & ",'" & c & x & "'!C15:EX15,0))" y.Address everything seems to be ok, only when i work in the same sheet.. ie. i use my function in D20 cell in sheet1 and then in another sheet i'd like to get value from this cell (by simple link) =Sheet!D4 and i get #N/D! just i write anything in another sheet and results in D20 in sheet1 turn into error, when i write anything in sheet1 result in D20 become value... does this function work only when i operate in sheet i placed this function? rgs |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA formula building
Change y.address to
y.address(1,1,xlA1,True) -- Regards, Tom Ogilvy "pm" wrote in message ... pm wrote: s = "=INDEX('" & c & x & "'!C8:EX8,1,MATCH(" & y.Address & ",'" & c & x & "'!C15:EX15,0))" y.Address everything seems to be ok, only when i work in the same sheet.. ie. i use my function in D20 cell in sheet1 and then in another sheet i'd like to get value from this cell (by simple link) =Sheet!D4 and i get #N/D! just i write anything in another sheet and results in D20 in sheet1 turn into error, when i write anything in sheet1 result in D20 become value... does this function work only when i operate in sheet i placed this function? rgs |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Loose formula/value (when building graphs) | Excel Worksheet Functions | |||
Formula Building in Excel 2007 | Excel Discussion (Misc queries) | |||
When building formula in excel, it would be very useful to have t. | Excel Programming | |||
When building formula in excel, it would be very useful to have t. | Excel Programming | |||
Building a Named Range in a formula with VBA | Excel Programming |