Results 1 to 6 of 6
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    run macro do until value change in column (2000 sr 1)

    ... assuming i have this sheet CODICI, and i would want to run "mymacro" do until the value change in column J...
    Example: run mymacro for 4 time because into column J the value A in present in 4 line, and the process, rum mymacro for 1 time because into column J the value D in present in 1 line....

    Before to start mymcaro i ordering the value with the first key in column J...

  2. #2
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: run macro do until value change in column (2000 sr 1)

    Try something like this:
    <pre>Option Explicit

    Sub LoopMacro()
    Dim StartCell As Range
    Dim ParaLetter As String
    ParaLetter = InputBox("Supply the letter to find", , "A")
    Application.ScreenUpdating = False
    Set StartCell = Columns("J:J").Find(What:=ParaLetter, _
    After:=Columns("J:J").Cells(1), _
    LookIn:=xlFormulas, _
    LookAt:=xlPart, _
    SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, _
    MatchCase:=False, _
    SearchFormat:=False)
    StartCell.Select
    Do Until ActiveCell.Value <> ParaLetter
    'Set macro code here
    Loop
    Application.ScreenUpdating = True
    End Sub</pre>

    Regards,
    Rudi

  3. #3
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: run macro do until value change in column (2000 sr 1)

    ... go in error see the images
    and, into first cell 2 of column J not is always present a letter A... it can change

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: run macro do until value change in column (2000 sr 1)

    Rudi (And Sal)

    Sal is using Excel 2000. Searching for formatting was not available there, as far as I know, so he should omit the part
    <code>
    , _
    SearchFormat:=False</code>

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: run macro do until value change in column (2000 sr 1)

    Tx...I see the error is pointing to an argumento of the Find Method! <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>
    Regards,
    Rudi

  6. #6
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: run macro do until value change in column (2000 sr 1)

    <P ID="edit" class=small>(Edited by Rudi on 30-Jan-06 16:45. Modified code to remove error argument! Tx Hans)</P>The letter that is prompted for can be changed in the input box. You can type any letter in there.
    With regards to the error...Could it be translation ??

    <pre>Option Explicit

    Sub LoopMacro()
    Dim StartCell As Range
    Dim ParaLetter As String
    On Error GoTo EH
    ParaLetter = InputBox("Supply the letter to find", , "A")
    If ParaLetter <> "" Then
    Application.ScreenUpdating = False
    Set StartCell = Columns("J:J").Find(What:=ParaLetter, _
    After:=Columns("J:J").Cells(1), _
    LookIn:=xlFormulas, _
    LookAt:=xlPart, _
    SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, _
    MatchCase:=False)
    StartCell.Select
    Do Until ActiveCell.Value <> ParaLetter
    'Set macro code here
    ActiveCell.Offset(1, 0).Select 'This line must stay here to loop
    Loop
    Application.ScreenUpdating = True
    End If
    Exit Sub

    EH:
    MsgBox "Unexpected error" & vbCrLf & vbCrLf & Err.Description, vbExclamation
    End Sub

    </pre>

    Regards,
    Rudi

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •