Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Select the next one in the list that is different

Posted on 2016-09-22
5
Medium Priority
?
54 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
5 Comments
 
LVL 27

Assisted Solution

by:Glenn Ray
Glenn Ray earned 1000 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 27

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 53

Accepted Solution

by:
Rgonzo1971 earned 1000 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

In this post, I will showcase the steps for how to create groups in Office 365. Office 365 groups allow for ease of flexibility and collaboration between staff members.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

916 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