[RESOLVED] Rewrite worksheet formula to VBA function

[Tobian]

I recorded following Macro:

Range("B11").Select
    ActiveCell.FormulaR1C1 = _
        "=CELL(""contents"",R2C)"

I want to make a function in VBA called Function ValueRowTwo() which does the same thing like the markro above. Eg. when I insert ValueRowTwo in cel B7 it should return the value of cell B2, or when I insert it in cell C5 it should return the value of cell C2.
Can somebody rewrite this worksheet formula to VBA function for me?

[Static]

it should be basically the same thing??

ValueRowTwo("B11")
 
Private Sub ValueRowTwo(cRnge As String)
    Range(cRnge).ForumulaR1C1 = "=CELL(""contents"",R2C)"
End Sub

unless u DONT want a formula in it.. in that case it would be

Range("B11") = ValueRowTwo("B")
 
Private Function ValueRowTwo(cRnge As String) As String
    ValueRowTwo = Range(cRnge & "2").Value
End Function

[Tobian]

Range("B11") = ValueRowTwo("B")

Unlike my makro, the function doesn't need no cell selection first, because the function is inserted that cell already (eg. B11).

Private Function ValueRowTwo(cRnge As String) As String
ValueRowTwo = Range(cRnge & "2").Value
End Function
[/Highlight]

So the Function ValueRowTwo doesn't need cRnge input. It has to look in the same column that Function ValueRowTwo is put in and look in row 2 for a value to return.

[Static]

I know... but if u are not selecting the cell first, u cant use Active Cell...
so, that being said.. u need to pass in some sort of information telling excel where u are... so it knows where to look for the info...
so if u want B11 to equal B2 u need to tell it to look in B2 by at least telling the function u are in column B...

[Tobian]

Then maybe active cell is not the right thing to use.

In the makro I use function Cell. When I insert it in cell B7, I give it Reverence B$2. When I copy/paste this formula in cell C10 and check the reverence, it shall be C$2. So C is relative and 2 is absolute. Is this not possible in VBA?

[Static]

hmm.. im not sure what u are after exactly then...
if u want to click in a cell.. and type "=ValueRowTwo()" and have it give the value in row 2 of the same column... u cant...
since u are entering a value in the Cell of "=ValueRowTwo()" it will create a circular reference since it is trying to then set the value to something else.. no good.

I would stick with just using the forumula...

[Tobian]

Let me try to make my question more clear.
Can you make me a function ValueRowTwo() that always return the value of the cell in Row two, in the column the function ValueRowTwo is put in? So when I put the function ValueRowTwo somewhere in column B, it should return the value of cell B2.
But, when I use the same function ValueRowTwo somewhere in column C, it should return the value of cell C2 and not B2.

[Static]

No. You cant.. I was trying to explain that in the post above...

If u call a function IN a cell.. that function cannot manipulate the cell u are calling it in. It creates a circular reference...

bAsically u are telling Excel
Range("B10").Forumula = "=ValueRowTwo()"
but ValueRowTwo() is trying to tell Rabge("B10") the value should be the value in Row 2.... so they conflict.
Create this sub

Public Sub ValueRowTwo()
    ActiveCell.Value = Cells(2, ActiveCell.Column).Value
End Sub

Now.. right click the toolbar.. pick customize

in the commands tab.. pick macros
now pick "Custom Button"
drag it to the tool bar....
right click the new button, Set the Name
then set the Macro to this new macro..
now.... close the custom window...
pick a cell and click the butt.. it should set that cell to the value in row 2 of that column

[Tobian]

If u call a function IN a cell.. that function cannot manipulate the cell u are calling it in. It creates a circular reference...

I don't understand there is a circular reference. When I call the function in cell C10, this function has to return the value of cell C2.
You want the function to return the value of C10 and that is what you mean with a circular reference, I think.

[Tobian]

if u want to click in a cell.. and type "=ValueRowTwo()" and have it give the value in row 2 of the same column... u cant...
since u are entering a value in the Cell of "=ValueRowTwo()" it will create a circular reference since it is trying to then set the value to something else.. no good.

I think we mix up things, it's getting confusing now.
Please try my example in excel a see what is happening:

Select cell C10
Insert Function Cell
Write "contents" in box Info_Type
Write C$2 in Reverence
Click Enter

Now, when you give cell C2 the value 2, you will see that cell C10 also displays the value 2.
Then, give cell D2 the value 4. Now you copy cell C10 and Special Paste Formula in cell D17 and you will see that cell D17 displays value 4 now. There is also no circular reference.

[VBKNIGHT]

try this make a function

Function PutRowTwo() As String

Dim c As Integer, x As String
    c = ActiveCell.Column
    
    PutRowTwo = Cells(2, c).Value
    
End Function

then put this on a cell as a userdefined function and it would get the value of row2 of whatever column you are currently active.

hope this is what you've wanted.

[Tobian]

Thanks, this is exactly what I want.