Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-27-2022, 06:25 AM
ntldr123 ntldr123 is offline Macro or VBA to sort values Windows 8 Macro or VBA to sort values Office 2013
Novice
Macro or VBA to sort values
 
Join Date: Mar 2020
Posts: 5
ntldr123 is on a distinguished road
Default Macro or VBA to sort values

Hi


My spreadsheet has 100 values on column B from B6 to B106the values have the below form



AB, BC, CD, DE

I need to copy the column D the AB values, in column E the BC values, in column F the CD values, in column G the DE values


I tried to create a macro however, i didn't succeed. can anyone help?


Thanks
Reply With Quote
  #2  
Old 04-27-2022, 06:33 AM
p45cal's Avatar
p45cal p45cal is offline Macro or VBA to sort values Windows 10 Macro or VBA to sort values Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

1. Best to attach a workbook with all this in.
2. Are you still using Office 2013 and Win 8?
Reply With Quote
  #3  
Old 04-27-2022, 06:53 AM
ntldr123 ntldr123 is offline Macro or VBA to sort values Windows 10 Macro or VBA to sort values Office 2016
Novice
Macro or VBA to sort values
 
Join Date: Mar 2020
Posts: 5
ntldr123 is on a distinguished road
Default

I'm using win 10 and office 2016- However, I can upgrade to a different office version m, if necessary.
Attached Files
File Type: xlsx temp.xlsx (8.9 KB, 7 views)
Reply With Quote
  #4  
Old 04-27-2022, 08:45 AM
p45cal's Avatar
p45cal p45cal is offline Macro or VBA to sort values Windows 10 Macro or VBA to sort values Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

I've prepared a solution, but liking to see some netiquette and consideration for others, I'll post it as soon as you provide links to all cross posts, at all cross posts as suggested here: Macro or VBA to sort values.
Even if you don't do this, I'll post a solution in a few days anyway.


ps. you could update your profile to reflect what versions of Excel and Office you're now using.
Reply With Quote
  #5  
Old 04-27-2022, 09:51 AM
ntldr123 ntldr123 is offline Macro or VBA to sort values Windows 10 Macro or VBA to sort values Office 2016
Novice
Macro or VBA to sort values
 
Join Date: Mar 2020
Posts: 5
ntldr123 is on a distinguished road
Default

Will do.
Thank you for you help
Reply With Quote
  #6  
Old 04-27-2022, 03:25 PM
macropod's Avatar
macropod macropod is offline Macro or VBA to sort values Windows 10 Macro or VBA to sort values Office 2016
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Also cross-posted at:
Macro or VBA to sort values
Macro or VBA to sort values | MrExcel Message Board
For cross-posting etiquette, please read: Excelguru Help Site - A message to forum cross posters
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #7  
Old 04-27-2022, 03:52 PM
ntldr123 ntldr123 is offline Macro or VBA to sort values Windows 10 Macro or VBA to sort values Office 2016
Novice
Macro or VBA to sort values
 
Join Date: Mar 2020
Posts: 5
ntldr123 is on a distinguished road
Default

Solution was offed by
carlmon
at Macro or VBA to sort values
Code:
Sub carlmon()
Dim D As Range
Dim E As Range
Dim F As Range
Dim G As Range



    For Each c In Sheet1.Range("B6:B1000")
    
        Set D = Sheet1.Range("D" & Rows.Count).End(xlUp)
        Set E = Sheet1.Range("E" & Rows.Count).End(xlUp)
        Set F = Sheet1.Range("F" & Rows.Count).End(xlUp)
        Set G = Sheet1.Range("G" & Rows.Count).End(xlUp)
        
        Select Case Left(c, 2)
        Case Is = "AB"
            Set D = D.Offset(1)
            D = c.Value
        Case Is = "BC"
            Set E = E.Offset(1)
            E = c.Value
        Case Is = "CD"
            Set F = F.Offset(1)
            F = c.Value
        Case Is = "DE"
            Set G = G.Offset(1)
            G = c.Value
        End Select
    Next c

End Sub

Last edited by macropod; 05-02-2022 at 04:02 PM. Reason: Corrected code formatting, added code tags & link.
Reply With Quote
  #8  
Old 05-02-2022, 08:24 AM
p45cal's Avatar
p45cal p45cal is offline Macro or VBA to sort values Windows 10 Macro or VBA to sort values Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

Also at https://www.msofficeforums.com/excel...rt-values.html

Code:
Sub blah()
StartChars = Array("AB", "BC", "CD", "DE") 'what you're looking for
Set Destn = Range("D2")    'top left of area where results will go.
'Find range to process:
Set Rng = Range("B6").End(xlDown)
If IsEmpty(Rng.Value) Then
  Set Rng = Range("B6")
Else
  Set Rng = Range(Range("B6"), Rng)
End If
'Create the results in-memory:
ReDim Results(1 To UBound(StartChars) - LBound(StartChars) + 1)
For Each cll In Rng.Cells
  colm = Application.Match(Left(Application.Trim(cll.Value), 2), StartChars, 0)
  If Not IsError(colm) Then
    If IsEmpty(Results(colm)) Then Set Results(colm) = CreateObject("Scripting.Dictionary")
    Results(colm).Add cll.Value & Rnd, cll.Value
  End If
Next cll
colm = 0
'determine size of area to clear for the results:
maxRows = 0
For Each result In Results
  maxRows = Application.Max(result.Count, maxRows)
Next result
'clear that area (+1 blank row):
Destn.Resize(maxRows + 1, UBound(Results)).Clear
For Each result In Results
  Destn.Offset(, colm).Resize(result.Count).Value = Application.Transpose(result.items)
  colm = colm + 1
Next result
End Sub

I suspect I'll give ntldr123 a wide berth in the future since he'll probably already have a solution elsewhere.
Reply With Quote
  #9  
Old 05-09-2022, 10:58 AM
ntldr123 ntldr123 is offline Macro or VBA to sort values Windows 10 Macro or VBA to sort values Office 2016
Novice
Macro or VBA to sort values
 
Join Date: Mar 2020
Posts: 5
ntldr123 is on a distinguished road
Default

Thank you for your support
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
custom sort macro ewso Excel Programming 7 10-10-2017 12:08 PM
Macro or VBA to sort values Macro to sort columns SerenityNetworks Excel Programming 4 09-02-2016 06:20 AM
Macro or VBA to sort values Selecting values from different rows in a table and sort them in order in a single row FromF Excel 2 09-09-2014 02:30 AM
Looking for Help to Create a Macro (Sort) rsrasc Word VBA 5 04-16-2014 03:25 AM
Sort table using macro saslotteroy Word VBA 2 09-15-2011 02:41 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 11:31 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft