CODEWITHSUNDEEP
vbaexcel
JeanLo
JeanLo

Reputation: 101

VBA determine the cell before the address?

I have this code : 

localise = Cells.Find("Hello" , lookIn:=xlValues).Address 
MsgBox localise

the msgbox give me $A$10 , but i want the cell before this one , how can i program this on VBA ?

Upvotes: 1

Views: 709

Answers (3)

Dave
Dave

Reputation: 4356

The use of Offset will get you what you want. In this case:

MsgBox localise.Offset(-1, 0).Address

This will give you the same column but one row before the found item.

You will also need to use Set in the line Set localise = ... in order to have the necessary object reference in place and not generate an Object Required error. It's also recommended to not assume that you will find the item you are searching for and handle this situation. In short:

Set localise = Cells.Find("Hello", LookIn:=xlValues)
If Not localise Is Nothing Then
    MsgBox localise.Offset(-1, 0).Address
Else
    MsgBox "Didn't find it"
End If

Upvotes: 2

Tim Edwards
Tim Edwards

Reputation: 1028

Using Offset is one option:

localise = Cells.Find("Hello", LookIn:=xlValues).Offset(-1, 0).Address

Or if you're just after $A$9 and want a less involved way then just find the second $ in the string and make right of that into a value and minus 1 from it.

Upvotes: 2

brettdj
brettdj

Reputation: 55682

Start you search in the last row and use xlnext for search direction (to start looking in row 1)

misread your question as finding the first occurrence. still would recommend searching from row 1 though, and not presuming that range exists - which your current code does

Sub FindFirst()
Dim Localise As Range
Set Localise = Cells.Find("Hello", Cells(Rows.Count, "A"), xlValues, , , xlNext)
If Not Localise Is Nothing Then MsgBox Localise.Offset(-1, 0).Address
End Sub

Upvotes: 2

Related Questions