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

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

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 :/

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 :/

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

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

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?

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?

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 :)

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!

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!

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)

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 :)

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

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