Find cell matching text then get last row before blank

[Buzzen812]

I need to find a cell that matches a text, and then update the last row in the group. Doing my best to not use select.

Thanks



A
A
A


B
B
B


C
C
C


D
D
D







E
E
E


F
F
F


G
G
xxxxx







I
I
I


J
J
J


K
K
K

[Leith Ross]

Hello Buzzen812,

Which cell is your input cell?

Will this search happen immediately after you input text?

[Buzzen812]

sorry the formatting was lost.
I want to search for E then go down to g and replace g with new content.


Hello Buzzen812,

Which cell is your input cell?

Will this search happen immediately after you input text?

[snb]

Sub M_snb()
columns(1).find("E").offset(2,2)="xxx"
End Sub

[Buzzen812]

what if the range is a different size? So a fixed offset doesn’t work.

[snb]

What if you formulate your question properly, iillustrated by a file ?

[Buzzen812]

similar to the set of ranges I posted. there are unique ranges sizes in the file that use the first 3 columns. Each group has a unique header but each range is a variable in size. I need to find the last row in that range after providing a search criteria for the range header.

What if you formulate your question properly, iillustrated by a file ?

[paulked]

What if you formulate your question properly, iillustrated by a file ?

Full of Christmas cheer :rotflmao:

Fair play, you are consistent :yes

BTW, your site http://www.snb-vba.eu/ is a fantastic reference to VBA, thank you :thumb

[p45cal]

something like:
Set x = Columns(1).Find(What:="E", LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False, SearchFormat:=False).End(xlDown).EntireRow
x.Cells(3) = "Hi there"
or like:
Set y = Columns(1).Find(What:="E", LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False, SearchFormat:=False).CurrentRegion
Set x = y.Rows(y.Rows.Count)
x.Cells(3) = "Hi there again"

[Buzzen812]

That’s it.

I struggled using .xldown without using select. Best Christmas present ever. I might actually be able to get out of work before 5 PM.