Re: Limit .find to one pass

Hi Robert

Thanks for your reply, I'm glad you made it work.

Regards,
Per

"Robert H" <robert.hatcher@xxxxxxxxxx> skrev i meddelelsen news:fcff7a51-0295-4191-8f8d-08e11453151e@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Per, It works now. I had to make one more modification:
Cells.findnext(ActiveCell).Activate
otherwise it would just go back an work the same column one time and
then exit.
Thanks very much for the help
Robert

Final code:

Sub ConvPMFLTSMoveLCIDtoNextCol()

Dim r As Long
Dim r2
Dim startCell As Range
Dim findnext

r = Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row - 1

Range("a1").Select

'Code mosdifications from Per Jessen

Cells.Find(what:="LC", After:=ActiveCell, LookIn:=xlValues, _
lookat:=xlPart, searchorder:=xlByColumns, SearchDirection:=xlNext,
_
MatchCase:=False, SearchFormat:=False).Activate

Set startCell = ActiveCell

Do
ActiveCell.Offset(1, 0).Select
Do Until ActiveCell.Value = ""
'Copy the right-most character to the next column
ActiveCell.Offset(0, 1).Value = Right$(ActiveCell.Value,
1)
'Cut off the right-most character
ActiveCell.Value = Left$(ActiveCell.Value, Len
(ActiveCell.Value) - 1)
'ActiveCell = ActiveCell.Offset(1, 0)
ActiveCell.Offset(1, 0).Select
Loop
ActiveCell.Offset(-r, 1).Select 'move up and to next column
Cells.findnext(ActiveCell).Activate
Loop Until ActiveCell.Address = startCell.Address

End Sub

On Dec 19, 9:04 am, Per Jessen <perjesse...@xxxxxxxxxxx> wrote:
Robert

Change
FindNext.Activate

to:

Cells.FindNext.Activate

Regards,
Per

On 19 Dec., 14:19, Robert H <robert.hatc...@xxxxxxxxxx> wrote:



> Per,
> the code manipulates the first column correctly but at the
> FindNext.Activate line I'm receiving a Runtime Error 424 "Object
> Required" error. I tried to fix it but did not make any progress.
> thanks
> Robert

> On Dec 18, 11:57 am, "Per Jessen" <per.jes...@xxxxxxx> wrote:

> > Hi Robert

> > I think this should do it:

> > Cells.Find(what:="LC", After:=ActiveCell, LookIn:=xlValues, _
> > lookat:=xlPart, searchorder:=xlByColumns, SearchDirection:=xlNext, _
> > MatchCase:=False, SearchFormat:=False).Activate
> > Set Startcell = ActiveCell
> > Do
> > ActiveCell.Offset(1, 0).Select
> > Do Until ActiveCell.Value = ""
> > 'Copy the right-most character to the next column
> > ActiveCell.Offset(0, 1).Value = Right$(ActiveCell.Value, 1)
> > 'Cut off the right-most character
> > ActiveCell.Value = Left$(ActiveCell.Value, Len(ActiveCell.Value) -
> > 1)
> > 'ActiveCell = ActiveCell.Offset(1, 0)
> > ActiveCell.Offset(1, 0).Select
> > Loop
> > ActiveCell.Offset(-r, 1).Select 'move up and to next column
> > FindNext.Activate
> > Loop Until ActiveCell.Address = Startcell.Address

> > Regards,
> > Per

> > "Robert H" <robert.hatc...@xxxxxxxxxx> skrev i > > meddelelsennews:57675f4c-b093-4114-a906-8aab30843e8d@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

> > > The following code loops through certain columns and removes the > > > last
> > > character in the data. My data arrangement has changed a little and
> > > now the code continuously loops through the data removing the last
> > > character until there is not data. Its funny to watch but I need to
> > > fix it...

> > > How can I limit the code to make just one pass? Too bad there is not > > > a
> > > "before" attribute.

> > > thanks
> > > Robert

> > > Do Until ActiveCell.Value = ""

> > > Cells.Find(what:="LC", After:=ActiveCell, LookIn:=xlValues,
> > > lookat:= _
> > > xlPart, searchorder:=xlByColumns, SearchDirection:=xlNext,
> > > MatchCase:=
> > > False, SearchFormat:=False).Activate

> > > ActiveCell.Offset(1, 0).Select

> > > Do Until ActiveCell.Value = ""

> > > 'Copy the right-most character to the next column
> > > ActiveCell.Offset(0, 1).Value = Right$
> > > (ActiveCell.Value, 1)
> > > 'Cut off the right-most character
> > > ActiveCell.Value = Left$(ActiveCell.Value, Len
> > > (ActiveCell.Value) - 1)
> > > 'ActiveCell = ActiveCell.Offset(1, 0)
> > > ActiveCell.Offset(1, 0).Select
> > > Loop
> > > ActiveCell.Offset(-r, 1).Select 'move up and to next column

> > > Loop- Hide quoted text -

> > - Show quoted text -- Skjul tekst i anførselstegn -

> - Vis tekst i anførselstegn -- Hide quoted text -

- Show quoted text -

.

Relevant Pages

  • Re: Limit .find to one pass
    ... Dim r As Long ... 'Copy the right-most character to the next column ... Loop Until ActiveCell.Address = startCell.Address ...     Loop ...
    (microsoft.public.excel.programming)
  • RE: Need help with an If then statement before a loop
    ... have it loop through all the tables with a "G" in the table name and put ... I can't figure out how to use the InStr function to define the recset. ... Dim db As Database ... > Instrfunction to determine whether the table name has the character in it: ...
    (microsoft.public.access.modulesdaovba)
  • RE: Need help with an If then statement before a loop
    ... recset1 hard coded and outside both your loops. ... > have it loop through all the tables with a "G" in the table name and put ... > Dim db As Database ... >> Instrfunction to determine whether the table name has the character in it: ...
    (microsoft.public.access.modulesdaovba)
  • Re: stop selection when finding certain font
    ... meaning that each iteration of the loop cuts the remaining ... empty string if the range contains more than one font. ... Dim nStart As Long, nEnd As Long ... >> code (assumes the cursor is at the first character of font x): ...
    (microsoft.public.word.vba.general)
  • Re: looking for implementation of strtok
    ... contains a ',' character. ... we enter the ugly loop that simply implements strspn: ... loop sets spanp to delim, then loops while seting sc (the "span ... If there are no more tokens, we set *lastp to NULL (this is, I ...
    (comp.lang.c)