#1
|
|||
|
|||
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 |
#2
|
||||
|
||||
1. Best to attach a workbook with all this in.
2. Are you still using Office 2013 and Win 8? |
#3
|
|||
|
|||
I'm using win 10 and office 2016- However, I can upgrade to a different office version m, if necessary.
|
#4
|
||||
|
||||
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. |
#5
|
|||
|
|||
Will do.
Thank you for you help |
#6
|
||||
|
||||
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] |
#7
|
|||
|
|||
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. |
#8
|
||||
|
||||
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. |
#9
|
|||
|
|||
Thank you for your support
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
custom sort macro | ewso | Excel Programming | 7 | 10-10-2017 12:08 PM |
Macro to sort columns | SerenityNetworks | Excel Programming | 4 | 09-02-2016 06:20 AM |
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 |