CODEWITHSUNDEEP
excelvba
smirnoff103
smirnoff103

Reputation: 328

Get previous column address

I have a named cell, and I want to start searching values from this row, but from the previous column. This is the code I have:

'I use the "false false" for not having an absolute reference, I don't know if this will have any impact later ?
newAdres = SearchForTotal(Range("total1").Address(False, False))

And this is the function itself:

Function SearchForTotal(givenLocation As Variant) As Variant
    Debug.Print givenLocation
    Debug.Print Range(givenLocation).Offset(, -1)

The first debug gives me: "U84", the second gives me: "0" , while I would like to have T84 instead

Upvotes: 0

Views: 299

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57683

Don't give your function an address/string give the function the cell/range. Variant is the worst type you can use in VBA.

Function SearchForTotal(ByVal givenLocation As Range) As Range
    Debug.Print givenLocation.Address
    Debug.Print givenLocation.Offset(0, 1).Address

    ' return the offset cell
    Set SearchForTotal = givenLocation.Offset(0, 1)
End Function

Then call your function like

Dim NewCell As Range
Set NewCell = SearchForTotal(Range("total1"))

Debug.Print NewCell.Address

Always work with ranges/cells and use addresses/strings only for displaying purpose.

Upvotes: 3

Related Questions