Solved

Select the next one in the list that is different

Posted on 2016-09-22
5
47 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 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 25

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 49

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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
IT Desktop Support 11 65
Automatic sort formula 8 50
locking multiple column ranges 10 23
sumifs excel 2013 3 10
Outlook Free & Paid Tools
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

786 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