Solved

Select the next one in the list that is different

Posted on 2016-09-22
5
42 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 48

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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
My experience with Windows 10 over a one year period and suggestions for smooth operation
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

744 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now