Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find a cell based on value?
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!! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to find and replace content of a specific cell based on its ad | Excel Discussion (Misc queries) | |||
Find a cell based on current month | Excel Programming | |||
Need to find a row in a worksheet based on the value of a cell | Excel Worksheet Functions | |||
vba help pls - find min based on a condition and return val of an offset cell | Excel Programming | |||
Find Max and Min based on cell reference | Excel Discussion (Misc queries) |