VBA - Find text and replace with value of cell above it

[jmcginley3]

I'm trying to figure out how I can use Cells.Replace along with Offset to locate and replace the cells that contain the text "Auto Adjustments" and replace it with the value of the cell directly above it.


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]450[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Auto Adjustments[/TD]
[TD]<--- this Cell is changed to the value of the cell right above it (450)[/TD]
[/TR]
[TR]
[TD]564[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]45433[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]56788[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9084[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Auto Adjustments[/TD]
[TD]<---- this Cell is changed to the value of the cell right above it (9084)[/TD]
[/TR]
[TR]
[TD]56165[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8765[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Auto Adjustments[/TD]
[TD]<---- this Cell is changed to the value of the cell right above it (8765)[/TD]
[/TR]
</tbody>[/TABLE]

Can I use this and insert some kind of offset coding into the Replacement?

    Cells.Replace What:="Auto Adjustments", Replacement:="?????", LookAt:= xlPart, SearchOrder:=xlByRows

 

[Fluff]

How about

Sub replaceSpec()
With Range("a2", Range("A" & Rows.Count).End(xlUp))
   .Value = Evaluate(Replace(Replace("if(@=""Auto Adjustments"",#,@)", "@", .Address), "#", .Offset(-1).Address))
End With
End Sub

 

[jmcginley3]

Hey that worked! Thanks for your help! Now I'm off to dissect and understand what it's doing. I've just recently jumped into using macros since it helps a great deal with my new job.

 

[Fluff]

Glad to help& thanks for the feedback

 

[ttran123]

Sorry to jump in on your thread. I'm still new to this but can some on help me how to set goal seek must be in ascending


For example cell B83 "set cell" is the sum of B71:B82

and cell B85 is the "to value"

and cell b70 is "by changing cell"


also, cell b71 is a hard code cell
cell b72 =B71*(1+$B$70)
cell b73 =B72*(1+$B$71)

and so far


thank you

 

[Fluff]

@ttran123
As this is a completely different question, please start your own thread.