Find first value in column and Offset 1

[Bilberry]

Hi all,
I want to search the value: Myword in column E and want to do an offset. I want to get the address of the cell, for example E8. And from here i want to do an offset (-4, 0), which is = A8. I have tried several things but without success, below an example:

Cells.Find(What:="Myword", After:=ActiveCell, LookIn:=xlValues, LookAt _
        :=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Offset(-4, 0)

Any idea? Is there a need to declare range variables?

 

[ClulessChris]

Something like:

Cells.Find(What:="myword", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
            :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
            False, SearchFormat:=False).Offset(0, -4).Activate

Never knock on Death's door: ring the bell and run away! Death really hates that!

 

[ClulessChris]

Sorry I'll try again

Cells.Find(What:="Myword", After:=ActiveCell, LookIn:=xlValues, LookAt _
        :=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Offset(0, -4).Activate

Never knock on Death's door: ring the bell and run away! Death really hates that!

 

[Bilberry]

I want to select the address of this value, because i want to select a range, eg:

 firstvalue = Cells.Find(What:="Balans", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
            :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
            False, SearchFormat:=False).Offset(0, -4).Activate
    ActiveSheet.Range(firstvalue, "E10").Select
    
    
    Selection.Copy

Firstvalue should have for example the value A2, how can i get this value?

 

[ClulessChris]

debug.print Cells.Find(What:="Myword", After:=ActiveCell, LookIn:=xlValues, LookAt _
        :=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Offset(0, -4).Address

Never knock on Death's door: ring the bell and run away! Death really hates that!

 

[Bilberry]

Thanks a lot. Im using this to find the first value. How can i find the last value.address (the cells can contain empty rows). Something with xlup??

 

[ClulessChris]

Sorry I don't understand what you want to find. This will find the last used row in Colunm A, hope it helps

Worksheets(1).range("A65536").end(xlup).row

Never knock on Death's door: ring the bell and run away! Death really hates that!

 

[Bilberry]

Hi,
Again i want to search the value "myword" from .range("A65536").end(xlup). How can i arrange that?

 

[Bilberry]

With other words i want to search the LATEST "myword" within a column

 

[ClulessChris]

How about

debug.print Cells.Find("Myword", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Row

Never knock on Death's door: ring the bell and run away! Death really hates that!