?
Solved

Select the next one in the list that is different

Posted on 2016-09-22
5
Medium Priority
?
53 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 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 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 52

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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

718 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