Find a cell in a range based on a condition & change the value of the cell beneath

[ROBJ]

Hello & Happy Holidays

I started the below VBA code to find a specific sheet based on a condition (if the last letter of the sheet meets that condition, in this case = A).
Formula in cell O3 pulls the name of the sheet=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)
Formula in cell U3 gets rid of the last letter (A) from the sheet name=LEFT(O3,LEN(O3)-1) ....

Column T (cells T5 to T200) contains the name of the rest of the sheets in the WB. What would be the most efficient way to find the cell in range T5:T200 that matches the value of cell U3 and replace the cell immediately below it with the value from cell O3?

Thank you in advance.

Sub pickSheet()
Application.ScreenUpdating = False
Dim ws As Worksheet
Dim pwd As String
pwd = "password"
For Each ws In Worksheets
    ws.Unprotect Password:=pwd
Next ws
For Each ws In ThisWorkbook.Worksheets
    If Right(ws.Name, 1) = "A" Then
        ws.Activate
         End If
          Range("U3").Formula = "=LEFT(O3,LEN(O3)-1)"
          Range("O3").Select
         
Next ws

For Each ws In Worksheets
    ws.Protect Password:=pwd
Next ws
End Sub

[p45cal]

Presuming you have the T5:T200 range in all the sheets ending in 'A' then this for the middle of your code might do it:

For Each ws In ThisWorkbook.Worksheets
  If Right(ws.Name, 1) = "A" Then
    'ws.Activate 'not needed
    ws.Range("U3").Formula = "=LEFT(O3,LEN(O3)-1)" 'you may not need this either?
    'Range("O3").Select 'not needed
    Set myRng = Nothing
    Set myRng = ws.Range("T5:T200").Find(Left(ws.Name, Len(ws.Name) - 1), LookIn:=xlFormulas, lookat:=xlWhole, searchformat:=False)
    If Not myRng Is Nothing Then myRng.Offset(1) = ws.Name
  End If
Next ws

[ROBJ]

Hi p45cal.
I've declared myRng as Range & with:
ws.Range("U3").Formula = "=LEFT(O3,LEN(O3)-1)"

all sheets ending in "A" will have cell U3 displaying the above formula.
What I need to find the cell in range T5:T200 matching the value of cell U3 and replace that particular cell's value with cell O3's value.

Thank you in advance.

[p45cal]

What I need to find the cell in range T5:T200 matching the value of cell U3 and replace that particular cell's value with cell O3's value.

I've done that, or very nearly, because originally you asked to:

replace the cell immediately below it

What you still haven't told me is whether the range T5:T200 is on all the sheets or not:

Presuming you have the T5:T200 range in all the sheets ending in 'A'

[ROBJ]

Hi p45cal.

Sorry for the misunderstanding... It appears that I was a bit tired when I originally wrote the post.
What I need, is to find the cell in range T5:T200 matching the value of cell U3 and replace that particular cell's value with cell O3's value. All sheets in the WB have the range T5:T200.

Again, apologies.
Thank you

[p45cal]

Tweak the code I gave in msg#2 by changing
.Offset(1)
to:
.Value

[ROBJ]

Hello p45cal,
Brilliant!
& I realized where I was going wrong.
As I left the formula in:
ws.Range("U3").Formula = "=LEFT(O3,LEN(O3)-1)" 'you may not need this either?
I was able to see that part of the code working & cell U3 populating but nothing was happening to the cell in range T5:T200 that has the corresponding value. Of course, I forgot to mention that the cells in range T5:T200 contain formulas & not values. I've changed the LookIn:=xlFormulas to look LookIn:=xlValues & the code worked immediately.
Apologies for the extra trouble & thank you for all your kind help.
Cheers

[p45cal]

I've changed the LookIn:=xlFormulas to look LookIn:=xlValues

Be aware with that setting, that if any rows in 5 to 200 are hidden, say by an autofilter, the code won't search amongst the hidden rows, and so may not find the value it's looking for..

[ROBJ]

Hello.

Noted & thank you again for your help.

Wishing you a Happy New Year.