Advanced Find and Replace Question

[Ryan]

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

 

[somethinglikeant]

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

 

[Dave Peterson]

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]

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

Ryan