PDA

View Full Version : [SOLVED] Find a cell in a range based on a condition & change the value of the cell beneath



ROBJ
12-27-2017, 01:05 AM
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
12-27-2017, 11:23 AM
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
12-27-2017, 06:06 PM
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
12-27-2017, 06:25 PM
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 itWhat 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
12-27-2017, 07:43 PM
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
12-27-2017, 11:53 PM
Tweak the code I gave in msg#2 by changing
.Offset(1)
to:
.Value

ROBJ
12-28-2017, 12:33 AM
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
12-28-2017, 04:42 AM
I've changed the LookIn:=xlFormulas to look LookIn:=xlValuesBe 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
12-28-2017, 11:03 PM
Hello.

Noted & thank you again for your help.

Wishing you a Happy New Year.