Advanced Find and Replace Question

I have a spreadsheet that includes ditto marks in several cells as a
way to make it more "readable". I need to dump this into Access so I
need to get rid of the ditto marks. My question is, how do I get excel
to replace all ditto marks with the value of the cell directly above
it? When I go to "Find and Replace" it wants me to enter the actual
value, but this value will change according to the value of the cell
directly above it.

Thanks!

Ryan

select an area of cells where you need to replace values with values
from cells above

and run this code

:======================

Sub ReplaceWithAbove()

Dim cl As Range

For Each cl In Selection
If cl.Value = "@" Then
cl.Value = cl.Offset(-1, 0).Value
End If
Next cl

End Sub

:======================

As you can see in my code I have had to use @ to switch
You may first have to perform a Find Replace to change all ditto marks
to @

Let us know hope you get on

cheers

somethinglikeant
http://www.excel-ant.co.uk

You have a few ways...

I'd do this:

tools|options|General Tab|check R1C1 reference style
select the range to fix
edit|Replace
what: " (I used double quotes for ditto marks)
with: =r[1]c
replace all

This replaces the value with a formula that says to use the value in the
previous row, but same column.

Then
tools|options|General Tab|uncheck R1C1 reference style
(to set it back to normal)

Finally, change the formulas to values.
Select the range
edit|copy
edit|paste special|values

========================

Another way if you don't have any empty cells to worry about:
Select the range
edit|replace
what: " (your ditto marks)
with: (leave blank)
replace all

Now you can use the tecniques at Debra Dalgleish's site to fill those empty
cells.
http://www.contextures.com/xlDataEntry02.html
Ryan wrote:
>
> I have a spreadsheet that includes ditto marks in several cells as a
> way to make it more "readable". I need to dump this into Access so I
> need to get rid of the ditto marks. My question is, how do I get excel
> to replace all ditto marks with the value of the cell directly above
> it? When I go to "Find and Replace" it wants me to enter the actual
> value, but this value will change according to the value of the cell
> directly above it.
>
> Thanks!
>
> Ryan

--

Dave Peterson

This worked like a charm! Thanks so much for your help!!!

Ryan

somethinglikeant wrote:
> select an area of cells where you need to replace values with values
> from cells above
>
> and run this code
>
> :======================
>
> Sub ReplaceWithAbove()
>
> Dim cl As Range
>
> For Each cl In Selection
> If cl.Value = "@" Then
> cl.Value = cl.Offset(-1, 0).Value
> End If
> Next cl
>
> End Sub
>
> :======================
>
> As you can see in my code I have had to use @ to switch
> You may first have to perform a Find Replace to change all ditto marks
> to @
>
> Let us know hope you get on
>
> cheers
>
> somethinglikeant
> http://www.excel-ant.co.uk