Find a cell based on value?

[Mr B]

Howdy all,

I don't think this would be hard but I don't know how to code it...

Here's the scenario.
I have a worksheet with static data on it and one that calculates totals for
various things that change all the time. In the static sheet there is a cell
that contains the value "1 Total". In the other sheet there is also a cell
that contains "1 Total" but it's not always in the same place.

What I need to do is take the cell to the right of the 1 Total cell in the
static sheet and get the value of the cell to the right of the 1 Total cell
in the sheet that has things move around.

How can I do that?

Thanks!!

 

[john]

Place this code in standard module - rename worksheets & ranges as shown.

Sub FindTotal()

Dim StaticWS As Worksheet
Dim MovingWS As Worksheet
Dim StaticValue
Dim Foundcell As Range

Set StaticWS = Worksheets("Static") '<< change name as required
Set MovingWS = Worksheets("Moving") '<< change name as required

'change range as required
StaticValue = StaticWS.Range("B1").Value

'this is what you are searching for
'on moving sheet. Value in the cell
'must be exactly as written here.
Search = "1 Total"

Set Foundcell = MovingWS.Cells.Find(Search, _
LookIn:=xlValues, _
LookAt:=xlWhole)


If Foundcell Is Nothing = False Then

'found search value
'place static value 1 col to right
Foundcell.Offset(0, 1).Value = StaticValue

Else
'did not find search value
'tell user
msg = MsgBox(Search & " Not Found!", 16, "Search")

End If