Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. ?? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. ?? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
split post code (zip code) out of cell that includes full address | Excel Discussion (Misc queries) | |||
Drop Down/List w/Code and Definition, only code entered when selec | Excel Worksheet Functions | |||
Protect Sheet with code, but then code will not Paste error. How do i get around this. Please read for explainations.... | Excel Programming | |||
Modification in the CODE to HIDE rows and columns that start with ZERO (code given) | Excel Programming | |||
Excel code convert to Access code - Concat & eliminate duplicates | Excel Programming |