vba function ThisCell.Address

[Mart van Hulten]

I have this function:
Function student()
student = Range("'" + Range("B1").Value + "student'!" + Application.ThisCell.Address).Value
End Function

I use this function in worksheet1.

The value in B1 gives the name of another worksheet, let's say ws2. I need to pick up the value in that other worksheet, in the cell with the same address as the cell in worksheet1 that uses this function.
So, for instance in worksheet1 in A4 I write: =student()
But, as soon as I have clicked in another sheet, I get an error and, whatever I do, the error remains. Untill I dubbleclick + ENTER on the cell in worksheet1.
What am I doing wrong?

 

[RoryA]

You should pass the B1 as an argument:

Function student(rg as range)
student = application.thiscell.worksheet.parent.sheets(rg.Value & "student").Range(Application.ThisCell.Address).Value
End Function

and use =Student(B1)

 

[Mart van Hulten]

Thank you, that does the trick!

Yet ONE problem remains for me.
I want to lookup a value in column 7 of the cells A4:H64 in a certain worksheet. The worksheetname will be found in cell $B$1
I need this function on different worksheets, each worksheet contains another value in $B$1

Function MyFunction(rg As Range)
Dim searchSomething As String
searchSomething = Cells(2, Application.ThisCell.Column).Value
MyFunction = Application.WorksheetFunction.VLookup(searchSomething , rg.Value & "$A$4:$H$64", 7, False)

End Function

For instance in cell K2 I write:
= MyFunction($B$1)
because $B$1 contains the name of the worksheet I need to search in.
Apparently the second parameter: rg.Value & "$A$4:$H$64" is wrong, so MyFunction gives an error...

 

[Fluff]

Cross posted https://www.excelforum.com/excel-fo...th-same-column-but-other-row.html#post5021960

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[RoryA]

You didn't take note of what I did before.

Can I ask why you are doing this? It's really not efficient and you need to make this function volatile since you are referring to cells that aren't arguments to the function.

 

[Mart van Hulten]

You didn't take note of what I did before.

Can I ask why you are doing this? It's really not efficient and you need to make this function volatile since you are referring to cells that aren't arguments to the function.

There are about 10 worksheets with certain data. The structure of all those sheets are the same, but the data vary.
I have also several sheets that need those data. The name of the needed sheet is given in $B$1.
Furthermore, I use a formula, something like: VLOOKUP(J$2 ; 'othersheet'!$A$4:$H$60 ; 7 ; FALSE)
And if I want to use this same formula in all those sheets, it would be nice to copy that formula, otherwise I would need to fill in the sheetname everywhere. So I'd like to have
the possibility to replace 'othersheet' with something (I think: a function) that uses the sheetname from $B$1.

 

[RoryA]

Can't you just use the INDIRECT function?

 

[Mart van Hulten]

Can't you just use the INDIRECT function?

I think I've tried that, but I did try a lot of things, so I'm not sure. I'll try again and let you know.

 

[Mart van Hulten]

I think I've tried that, but I did try a lot of things, so I'm not sure. I'll try again and let you know.

Of course: INDIRECT solves the problem. I thought I'd already tried that...
Thank you.

 

[rlv01]

FYI, the OP has declared the problem solved in his cross post

https://www.excelforum.com/excel-fo...th-same-column-but-other-row.html#post5022517

Update: I see he has posted "solved" here also.