Find a cell based on value?
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
--
jb
"Mr B" wrote:
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!!
|