my Franken-code
 

Hi,
I'm brand new to macros and coding in general. I've sifted through this
site for an answer, but there was no one post to cover what I needed, so I
pieced this baby together from different posts.

I will be getting these excel files frequently, and they can contain
thousands of rows. I need a macro that will search all rows and columns in
the active worksheet of any workbook I may choose, regardless of how many
rows it contains.

I need to replace each cell that contains only a single " mark with the
contents of the cell above it (somebody thinks it's a good idea to use ditto
marks in their database). There is legitimate use of quotes elsewhere in the
worksheet


Dim r As Range
Set r = ActiveSheet.UsedRange
r.Replace What:=""", Replacement:=MyCell.Value = ActiveCell.FormulaR1C1 =
"=R[-1]C", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
End Sub


This will not run. But you could probably tell that from reading it, huh?
It says there's a syntax error, but I think it's more than that.

p.s.
I did read a post with a warning that worried me. I can't remember the exact
warning, but it was something about not using a whole worksheet as a range
because the code would remember the amount of rows used each time you ran it
and adjust the code to reflect that. The post said it was better to name a
specific range, but I want to be able to run this code no matter how many
rows.
Is that post true?

PLEASE HELP ME!
Thanx
jen

my Franken-code
 

Try the following:
Set r = ActiveSheet.UsedRange
For Each mycell In r
If mycell.Row 1 Then
ReplaceMe = Cells(mycell.Row - 1, mycell.Column).Value
mycell.Replace What:="""", Replacement:=ReplaceMe, LookAt:=xlWhole,
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
ReplaceFormat:=False
End If
Next mycell
End Sub

"justme" wrote:

Hi,
I'm brand new to macros and coding in general. I've sifted through this
site for an answer, but there was no one post to cover what I needed, so I
pieced this baby together from different posts.

I will be getting these excel files frequently, and they can contain
thousands of rows. I need a macro that will search all rows and columns in
the active worksheet of any workbook I may choose, regardless of how many
rows it contains.

I need to replace each cell that contains only a single " mark with the
contents of the cell above it (somebody thinks it's a good idea to use ditto
marks in their database). There is legitimate use of quotes elsewhere in the
worksheet


Dim r As Range
Set r = ActiveSheet.UsedRange
r.Replace What:=""", Replacement:=MyCell.Value = ActiveCell.FormulaR1C1 =
"=R[-1]C", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
End Sub


This will not run. But you could probably tell that from reading it, huh?
It says there's a syntax error, but I think it's more than that.

p.s.
I did read a post with a warning that worried me. I can't remember the exact
warning, but it was something about not using a whole worksheet as a range
because the code would remember the amount of rows used each time you ran it
and adjust the code to reflect that. The post said it was better to name a
specific range, but I want to be able to run this code no matter how many
rows.
Is that post true?

PLEASE HELP ME!
Thanx
jen

my Franken-code
 

Okay,

I tried this and it partially works, though I don't know why, because I did
use 4 quote marks even though I think I should only need 3:


Sub Macro13()
'
' Macro13 Macro
' Macro recorded 12/5/2006 by jeny
'
' Keyboard Shortcut: Ctrl+q
'
Do
Cells.Select
Range("A63").Activate
Selection.Find(What:="""", After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
ActiveCell.FormulaR1C1 = "=R[-1]C"
Loop
End Sub


It seems to replace single quotes, but I get this error:
"Run-time error '91': Object Variable or With Block variable not set"
Also, the shortcut doesn't work.

??

my Franken-code
 

It also removes legitimate quotes around words!
and if I use it more than a few times it stop working. :(

PLEASE help give life to my Frankencode!

my Franken-code
 

Thanx for the explanations,
That was driving me crazy!

You are an angel

;)

jeny

"Neily" wrote:

I think the quote mark issue is because if you put quotemarks inside a set of
quotemarks, you have to double them up. Hence the double quote in the middle
is the representation of a single ".

ie It you write code to put a formula into a cell it goes like this...
ActiveCell.Formula="=TEXT(NOW(),""dd-mmm-yyyy"")"

When you run this, it converts the double "s into singles on the worksheet.

"justme" wrote:


Okay,

I tried this and it partially works, though I don't know why, because I did
use 4 quote marks even though I think I should only need 3:


Sub Macro13()
'
' Macro13 Macro
' Macro recorded 12/5/2006 by jeny
'
' Keyboard Shortcut: Ctrl+q
'
Do
Cells.Select
Range("A63").Activate
Selection.Find(What:="""", After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
ActiveCell.FormulaR1C1 = "=R[-1]C"
Loop
End Sub


It seems to replace single quotes, but I get this error:
"Run-time error '91': Object Variable or With Block variable not set"
Also, the shortcut doesn't work.

??

my Franken-code
 

no probs - happy to help :)

"justme" wrote:

WOOOWWW!
Ok, Marion,
That was WAY TOO COOL!

I pasted your second code over my current macro and it was like a symphony,
watching the wave of quotes change to dates all the way down!!

Please note that all those other little notes about the code not working
were in reference to my second post where I posted another attempt of my own.


You are truly amazing, and i thank you for your time and caring ;.(
(tears),
and *magic* code, as opposed to my mad Frankencode!

jeny
OOO