How to display cell value offset active cell

1
votes

I'm trying to display the value of a cell according to my ActiveCell or Target Cell using a Function. The cell I'm trying to display is in the same spreadsheet.

My objective is to create a Header in the spreadsheet that would display information according to the position of the Active cell.

I've tried this code and typed the function =VendorName5() in the cell where I want the value to be displayed but it seems to be missing something. Can you help ?

Function VendorName5() As String

Name = ActiveCell.Offset(0, -4)

       VendorName5 = Name

End Function

OK, found it:

"Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Column = 8 Then Range("C2") = Cells(Target.Row, 2) End If

End Sub"

tks for the help

2
vbaexcel
Is this code in the Worksheet code module? Also, I'm assuming this is VBA, not VB.net. – David Zemens
Yes it's in the module. yes it's VBA – Patrick Brunet
I don't think you can use ActiveCell during a UDF as part of a worksheet function. What's your end goal here? If you just want to know the value of a cell 4 columns to the left then you could, for example, put =A1 in cell E1 – tigeravatar
This works for me if i put the code in an ordinary code module, but does not work if I put it in the worksheet code module. Excel 2010. – David Zemens
Also if you really indeed it to be a dynamic function baseon the ActiveCell this should be fine using the Worksheet_Change () event. – David Zemens

2 Answers

1
votes

This is VBA not VB.NET

Try .Value of Cell 

Function VendorName5() As String

    Name = ActiveCell.Offset(0, -4).Value

    VendorName5 = Name

End Function
0
votes

Why not use the Offset function:

In cell A1:

=OFFSET(A1,0,4,1,1)

Or, refer to the cell directly:

=E1

etc.

This seems overkill to use a UDF to do something that worksheet functions ordinarily allow for, already.