Solved

Select the next one in the list that is different

Posted on 2016-09-22
5
49 Views
Last Modified: 2016-09-23
I have a macro that leave the active cell on the one it start with.  How can I move down the list and have it select the next one in the list that is different?  The procedure will start on the first one in the dataset, (I35SYSLOAD022) then when procedure runs and is done that active cell is still on the original selected cell.  What I would like is that in ends on the next available different number (I35SYSLOAD023).  I would write this in a Do While loop...  but that is slow on larger sub sets.  Please advise and thanks.

Sample dataset
I35SYSLOAD022
I35SYSLOAD022
I35SYSLOAD022
I35SYSLOAD022
I35SYSLOAD022
I35SYSLOAD022
I35SYSLOAD023
I35SYSLOAD024
I35SYSLOAD025
I35SYSLOAD025
I35SYSLOAD026
I35SYSLOAD026
I35SYSLOAD026
I35SYSLOAD026
I35SYSLOAD026
0
Comment
Question by:RWayneH
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 27

Assisted Solution

by:Glenn Ray
Glenn Ray earned 250 total points
ID: 41811590
I think you would just insert another loop inside your Do...While loop like so:
Sub YourProcedure()
    Dim strItem As String
    
    Do While ActiveCell.Value <> ""
        'capture the value before starting procedure
        'the procedure probably assigns a variable already,
        'so that can be used
        strItem = ActiveCell.Value
        'Do your stated procedure
        'xxxxxxx
        'end of procedure
        Do Until ActiveCell.Value <> strItem
            ActiveCell.Offset(1, 0).Select
        Loop
    Loop
End Sub

Open in new window


Using ActiveCell.Offset is less-efficient than selecting the range and using For...Each...Next, but it should work here.

Regards,
-Glenn
0
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 41811944
You can easily do this by selecting the whole column then Go to special select column different and it will jump to the next difference. To learn here is a video from my friend Mike excelisfun https://m.youtube.com/watch?v=bIuDEFBxabE

One more tip for every change you don't have to repeat it by going to Gotospecial again simply pressing F4 key in the keyboard will repeat the action for you
0
 
LVL 50

Accepted Solution

by:
Rgonzo1971 earned 250 total points
ID: 41811957
Hi,

You do not have to select every cell
Sub YourProcedure()
    Dim strItem As String
    
    Do While ActiveCell.Offset(1, 0).Value <> ""
        'capture the value before starting procedure
        'the procedure probably assigns a variable already,
        'so that can be used
        strItem = ActiveCell.Value
        'Do your stated procedure
        'xxxxxxx
        'end of procedure
        Idx = 1
        Do Until ActiveCell.Offset(Idx, 0).Value <> strItem
            Idx = Idx + 1
        Loop
        ActiveCell.Offset(Idx, 0).Select
    Loop
End Sub

Open in new window

Regards
0
 

Author Comment

by:RWayneH
ID: 41812603
Landed on:
Sub MoveToNextOrderAfterCreate()
        strItem = ActiveCell.Value
        Idx = 1
        Do Until ActiveCell.Offset(Idx, 0).Value <> strItem
            Idx = Idx + 1
        Loop
        ActiveCell.Offset(Idx, 0).Select
End Sub

Open in new window


Thanks all for the help.
0
 

Author Closing Comment

by:RWayneH
ID: 41812604
Thanks for help.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question