CODEWITHSUNDEEP
excelvba
friko
friko

Reputation: 607

Refering to excel cells in absolute and relative terms

Let's say I have a given range as argument to a function:

Function wrapper(current As range)

now I would like to get the value of the cell that to the far left (Column A).

E.g if current cell is H12, I would like to get cell text which is in A12.

Thank you for your help

ok, lets leave out above problem. In general, what I want to achieve is to get a variable of Range type. In below example:

Function wrapper(current As range)
Set hardwarePos = Cells(current.Row, 1)

my hardwarePos is of type Set, and I can not pass it to another function ( GetFormula(var as Range). :

Function wrapper(current As range)
Set hardwarePos = Cells(current.Row, 1)
wrapper = GetFormula(hardwarePos)

This is not working...

thank's for you tips. I like the way KazJaw solved it, but stil I have some problems. Why this does not work:

Function wrapper(current As range)
Dim hardwarePos As range
hardwarePos = Cells(current.Row, 1)
wrapper = hardwarePos.Text
End Function

Upvotes: 3

Views: 4062

Answers (2)

brettdj
brettdj

Reputation: 55692

As a formula (without VBA)

=INDIRECT(ADDRESS(ROW(),1))

With VBA:

Function wrapper(current As Range)
wrapper = Sheets(current.Parent.Name).Cells(current.Cells(1).Row, 1).Value
End Function

Upvotes: 2

Francesco Bonizzi
Francesco Bonizzi

Reputation: 5302

Use this:

Public Function wrapper(current As String)
    Dim newCell As String
    newCell = Range(current).Offset(0, -1).Address

    ' Range(newCell).Select
    ' Range(newCell).value = "blabla"
End Function

It takes address from the current cell and uses it get the address of the previous cell in terms of columns (with offset function, -1 is for "one column left")

Then by using Range(newCell) you can do what you want with that cell, selecting, writing, ecc.

You can call wrapper with: wapper(MyCell.Address)

Upvotes: -1

Related Questions