Free Microsoft Excel 2013 Quick Reference

Column sort using vba Results

I have a table of data. I want to pull one column of data below the
table to sort and use to create a chart. I also want to be able to
select the rows of data included in the sorted table to be graphed.

I have been using a pivot tableto select the rows and to
automatically(updated with the change event with VBA) sort the data.
However since a PT is usually used to summarize data not just sort, it
sums or averages the numbers therefore putting the limitation of not
having duplicates.

I am using the index and match functions to link the sorted data to the
table above.

I tried to just sort the data with VBA however the index/match function
and the list boxes get mixed up when sorted.

Any solutions with the sort or the PT would be appreciated. Is it
possible to just pull numbers with a PT, I just want to sort the
numbers.

Using Excel 2003

Carl

Hi All,

I've to write a VBA code which would sort the contents of column A (the code should not align the contents of Column 1 alphabetically but write 1,2,3.. . against each cell according to their alphabetical alignment)

I am trying to do a horizontal sort for 6 columns of data per row. I then want to concat all 6 columns into one single cell. The VBA code I am using works fine except for a situation in which there is an empty cell between two columns of data.

As per the example below, code works fine for first two records. I get a run-time error at the third record because the cell in second column is blank. (sample attached with expected results, if needed). Thanks

Column 1.........Column 2..............Column3..........Expected Result
2222...............5555.............................................22225555
5555...............2222.............................................22225555
5555............................................2222................22225555

Sub HorizontalRepSort()

With Sheets("FullFile")
    
    Lastrow = .Cells(.Rows.Count, 1).End(xlUp).Row
    .Range("W2:W" & Lastrow).ClearContents
    
    For i = 2 To Lastrow
    
        Application.StatusBar = "Analyzing " & i - 1 & " of " & Lastrow - 1 & "
records for rep IDs 1-6."
        ActiveRepCount = WorksheetFunction.CountA(.Range("Q" & i & ":V" & i))
        
        'Concat reps IDs 1-6 and sort ascending
        For y = 1 To ActiveRepCount
            MinimumRepValue = Application.WorksheetFunction.Small(.Range("Q" & i).Resize(, ActiveRepCount), y)
            .Range("W" & i) = "'" & .Range("W" & i) & MinimumRepValue
        Next
        
    Next
    
End With

End Sub
For example:

Hi,

I need to create a macro and I have no idea what I'm doing using VBA and recording doesn't really work. Can anyone help??

To help, I've attached some files. The first file (Macro template) is the file I want data pasted into. The second and third files contain the data I need.

I think I will need several macros but I want to be able to look at a cell in the macro template file (in this case cells B8, B47 and B87) which has my "Pack ID" and then open the second file which has the data. The data needs to be sorted in alphabetical order (currently A01, C01, E01 etc etc, needs to be A01, A02, A03 etc). and then paste just the data (column B) into the 'tube sent' column in the original file. It then does the same for the second data file and pastes into the 'tube received' column.

I have no experience of VBA, so I really don't know how to open the area I need to edit the VBA or enter any VBA or anything. Recording a macro at the moment does everything I want but is limited to only opening the file I use while recording the macro, where I want it to open the file specific to B8, B47, B87 etc.

cheers

Hi
Quite often I use VBA code to autofill some formulas and normally it works more or less ok. But I'm having a problem with autofill down to populate value "1" from G2 as far as the rows go in column A.The code that I currently have is as below:

Sub autofill_down()
    Range("G2").ClearContents
    Range("G2").Value = "1"
    Range("G2").Select
    Dim K2 As Long
    K2 = Cells(Rows.Count, "A").End(xlUp).Row
    Range("G2:G" & K2).FillDown
End Sub

If there is some information in col A further than A2 then its not a problem and the code populates as below:

-------------- -------------- Col A ------------------------------------------------ Col G
-------------- --------- Header text 1 ----------------------------------------- Header text 7
Row 1 -------------- Text 1 ------------------------------------------------------ 1
Row 2 -------------- Text 2 ------------------------------------------------------ 1
Row 3 -------------- Text 3 ------------------------------------------------------ 1
Row 4 -------------- Text 3 ------------------------------------------------------ 1
Row 5 -------------- Text 4 ------------------------------------------------------ 1
Row 6 -------------- Text 5 ------------------------------------------------------ 1

The problem is when there is a information only on one line (e.i. only on A2 and not any further). if this is the scenario it populates the value 1 in G2, but as soon as the autofilldown kicks in, for some reason it copies the line above. Please see below.

-------------- -------------- Col A ------------------------------------------------ Col G
-------------- --------- Header text 1 ----------------------------------------- Header text 7
Row 1 -------------- Text 1 --------------------------------------------------- Header text 7
Row 2 -------------- ---------- --------------------------------------------- ----------
Row 3 -------------- ---------- --------------------------------------------- ----------
Row 4 -------------- ---------- --------------------------------------------- ----------
Row 5 -------------- ---------- --------------------------------------------- ----------
Row 6 -------------- ---------- --------------------------------------------- ----------

Is there any way to have a code that would populate value 1 as per Column A but would not have this sort of drawback.

Any ideas?

Hello,

I am in the process of putting together a spreadsheet to do some of my admin work for me, and I have run into an issue with sorting and auto-formatting that seems to be a little bit over my head. I have no VBA or macro experience.

Essentially, I am looking to stack rank rows based on data in several columns, and then conditionally format the row to change to red, yellow, or green based on proximity to the current day's date. I do not need the spreadsheet to be constantly sorting, but just to complete the sort and formatting each time I open the file, based on the data entered in the previous session. I am looking for the same sort/format combination to repeat through several sub headings throughout the spreadsheet.

To be more specific, I am looking to sort rows 5-28 first by "postal code," listed in column G, then by "Next Date to Call" listed in column I, then by "priority" listed in column J. Further, I am looking for the individual rows to change to green if the "Next Date to Call" (column I) is today's date, yellow if it is in the future, and red if the date has passed. I can use cell Z1 as the reference cell to put the current date in.

To complicate matters, I would like to be able to repeat this same sort process with rows 30-50 under a separate sub-category, rows 52-73 under another, rows 75-94 under another sub heading, 96-119 under another, 121-141 under another sub heading. All of this within the same spreadsheet.

Finally, if all of this is possible, I would ideally like to be able to add or cut individual rows without crippling (or having to manually adjust) the auto-sort/formatting. For example, maybe I would need to add 5 rows to the first heading as new businesses open, or to delete them as businesses close. I would like Excel to auto-recognize that I have added a row in the appropriate range and adjust accordingly, similarly to how it does when there is say a formula like sum(A1:A20) and I paste a new row in between A10 and A11. The formula automatically adjusts to read sum(A1:A21).

While this seems rather complicated, it also seems like it should be possible. I know I could set it up by using conditional formatting for the color changing, and I could manually select the individual headings each time I open the sheet and set up the sort using the "custom sort" function. This would be rather time-consuming, however, with 5-10 sub-headings per page, and 6 separate identically formatted spreadsheet tabs (by area) to go through. I would like to eliminate the need for manually sorting every time I open the file.

Any help would be greatly appreciated.

Thanks.

Hi All.

I found the following function on-line. It outputs the Column Number for a given Column Letter.
'Function to calculate column number from column letter
Function ColumnIndex(ColumnLetter As String) As String
    Dim singlechar As String
    Dim intCode As Integer
    Dim loopCounter As Integer
        loopCounter = 0
        intCode = 0
    Do While Len(ColumnLetter) > 0
        singlechar = Right(ColumnLetter, 1)
        intCode = intCode + (26 ^ loopCounter) * (Asc(singlechar) - 64)
        loopCounter = loopCounter + 1
        ColumnLetter = Left(ColumnLetter, Len(ColumnLetter) - 1)
    Loop
        ColumnIndex = intCode
End Function
The user inputs a Column Letter into a userform textbox, 'TBCOL'. From the resulting column number from the function calculation, I wish to refer to this code in the following:

ColNum = Me.TBCOL.Text
ColNumber = ColumnIndex("" & ColNum & "")
MsgBox ColNumber
StartRange = Me.TBSTART.Text
    Sheets(SheetName).Select
    Set IDRowStart = Columns(??????).Find(what:="" & StartRange & "",
After:=Range("B180"))
I have tried the following in the Columns() section:
Columns("& ColNumber &")
Columns(""& ColNumber &"")
Columns("'"& ColNumber &"'")
Columns(& ColNumber &)
Columns & ColNumber
None of the above seem to work. Hopefully the power of the 'Excel Forum' will sort this in a jiffy.

Thanks in advance
Mark

Hi there

I used the below script which is fabulous tip on your forum, I have set up about 20 document registers to run on different folders for contracts. however the problem that I have is the when you run an update on the register once it opens, it recreates the lists that are already captured, I need it to just capture the new information when it comes into the folders. I thought it was doing this however I was incorrect. any help would be appreciated - some of these registers have 1 to 2 thousand files in them, so updating them again and again takes time and not to mention too much information to sort through

Sub TestListFilesInFolder()
      With Range("A1")
        .Formula = "Folder contents:"
        .Font.Bold = True
        .Font.Size = 12
    End With
    Range("A3").Formula = "Doc Number:"
    Range("B3").Formula = "Direction:"
    Range("C3").Formula = "File Type:"
    Range("D3").Formula = "Date Created:"
    Range("E3").Formula = "TO:"
    Range("F3").Formula = "FROM:"
    Range("G3").Formula = "Notes:"
    Range("H3").Formula = "Short File Name:"
    Range("I3").Formula = "Hyperlink:"
    Range("J3").Formula = "Full Document Path:"
    Range("A3:J3").Font.Bold = True
    ListFilesInFolder "G:CFSAdministrationContractsProject 2020CTR-530148 - Howdens Vent fan installation", True
    ' list all files included subfolders
End Sub

Sub ListFilesInFolder(SourceFolderName As String, IncludeSubfolders As Boolean)
' lists information about the files in SourceFolder
' example: ListFilesInFolder "C:FolderName", True
Dim FSO As Scripting.FileSystemObject
Dim SourceFolder As Scripting.Folder, SubFolder As Scripting.Folder
Dim FileItem As Scripting.File
Dim r As Long
    Set FSO = New Scripting.FileSystemObject
    Set SourceFolder = FSO.GetFolder(SourceFolderName)
    r = Range("J65536").End(xlUp).Row + 1
    For Each FileItem In SourceFolder.Files
        ' display file properties
      
        Cells(r, 3).Formula = FileItem.Type
        Cells(r, 4).Formula = FileItem.DateCreated
        Cells(r, 8).Formula = FileItem.Name
        Cells(r, 10).Formula = FileItem.path
        ' use file methods (not proper in this example)
'        FileItem.Copy "C:FolderNameFilename.txt", True
'        FileItem.Move "C:FolderNameFilename.txt"
'        FileItem.Delete True
        r = r + 1 ' next row number
    Next FileItem
    If IncludeSubfolders Then
        For Each SubFolder In SourceFolder.SubFolders
            ListFilesInFolder SubFolder.path, True
        Next SubFolder
    End If
    Columns("A:H").AutoFit
    Set FileItem = Nothing
    Set SourceFolder = Nothing
    Set FSO = Nothing
    ActiveWorkbook.Saved = True
End Sub


Hi there

Sorry, i'm not a coder or expert - hope this helps

I used the below script which is fabulous tip on your forum, I have set up about 20 document registers to run on different folders for contracts. however the problem that I have is the when you run an update on the register once it opens, it recreates the lists that are already captured, I need it to just capture the new information when it comes into the folders. I thought it was doing this however I was incorrect. any help would be appreciated - some of these registers have 1 to 2 thousand files in them, so updating them again and again takes time and not to mention too much information to sort through

Sub
TestListFilesInFolder()
With Range("A1")
.Formula = "Folder contents:"
.Font.Bold = True
.Font.Size = 12
End With
Range("A3").Formula = "Doc Number:"
Range("B3").Formula = "Direction:"
Range("C3").Formula = "File Type:"
Range("D3").Formula = "Date Created:"
Range("E3").Formula = "TO:"
Range("F3").Formula = "FROM:"
Range("G3").Formula = "Notes:"
Range("H3").Formula = "Short File Name:"
Range("I3").Formula = "Hyperlink:"
Range("J3").Formula = "Full Document Path:"
Range("A3:J3").Font.Bold = True
ListFilesInFolder "G:CFSAdministrationContractsProject 2020CTR-530148 - Howdens Vent fan installation", True
' list all files included subfolders
End Sub

Sub ListFilesInFolder(SourceFolderName As String, IncludeSubfolders As Boolean)
' lists information about the files in SourceFolder
' example: ListFilesInFolder "C:FolderName", True
Dim FSO As Scripting.FileSystemObject
Dim SourceFolder As Scripting.Folder, SubFolder As Scripting.Folder
Dim FileItem As Scripting.File
Dim r As Long
Set FSO = New Scripting.FileSystemObject
Set SourceFolder = FSO.GetFolder(SourceFolderName)
r = Range("J65536").End(xlUp).Row + 1
For Each FileItem In SourceFolder.Files
' display file properties

Cells(r, 3).Formula = FileItem.Type
Cells(r, 4).Formula = FileItem.DateCreated
Cells(r, 8).Formula = FileItem.Name
Cells(r, 10).Formula = FileItem.path
' use file methods (not proper in this example)
' FileItem.Copy "C:FolderNameFilename.txt", True
' FileItem.Move "C:FolderNameFilename.txt"
' FileItem.Delete True
r = r + 1 ' next row number
Next FileItem
If IncludeSubfolders Then
For Each SubFolder In SourceFolder.SubFolders
ListFilesInFolder SubFolder.path, True
Next SubFolder
End If
Columns("A:H").AutoFit
Set FileItem = Nothing
Set SourceFolder = Nothing
Set FSO = Nothing
ActiveWorkbook.Saved = True
End Sub


Hi All,

I have had a good look around this and various other forums which has got me to the current point and would like it if someone could assist me to progress further on this project.

I need to create a VBA script to help monitor the delivery times of goods we send out on courier. Basically at the end of each month we get a list of all the consignments we sent out (connote number, send date, weight, number of cartons, etc). This courier company has the ability to look up the delivery time on their website however to look up each connote manually is a time consuming process.

Now on the website we need to enter the connote number into a search box and press submit. This then loads up information on the shipment including shipping date, destination depot, delivery date etc. The only information I need to pull from the website is the date it was delivered.

Now I am only a small way into this but have an issue where when I press (or it is simulated in the code) the submit button the resulting page opens in a new window (but only the first time the search button is pressed). Resulting times in the new new window this appears to not happen) Therefore the exporting of the page data exports the original page instead of the new page which has the delivery information.

So firstly I either need to know how I can disable the function which is causing it to open in a new window or alternatively find a way to reference the new instance of Internet Explorer.

Once this is done my next real issue is finding a way to reference the last scan date where it states item delivered. Note sometimes the delivery will have multiple scannings done so need to reference the first line where delivery is confirmed. Suspect this may be quite difficult and if so can reference just the first record though this will induce a small margin of error in my reporting that is not ideal.

Lastly how can this date be copied and pasted into a cell in the excel spreadsheet. Which is a matter of parsing the data. Not too sure on how to do this but should hopefully be able to work this part out myself if need be.

Then I just need to make it scroll through all the records in a column looking for this information. I should be right to sort this part out.

Sub Webquery()

Dim IE As Object
Dim rng As Range
Dim frm As Object
Dim srch As Object
 
    Set IE = CreateObject("InternetExplorer.Application")
    Set rng = Range("A1")
    IE.Visible = True
    IE.navigate "http://www2.startrackexpress.com.au/scripts/webtracktrace.dll/conget"
    Do While IE.Busy: DoEvents: Loop
    Do While IE.readyState <> 4: DoEvents: Loop
    Set frm = IE.document.forms("track")
    Set srch = frm.all("Connote")
    srch.Value = rng
    frm.submit
                
    Range("B1").Select
    [B1] = IE.document.Body.innertext
    
 End Sub
A connote you can try if required to see layout etc. of the page is HUN81000. Note this is not a connote which relates to my company but the formatting etc. is exactly the same. Cell A1 in my sample sheet is the first connote number.

Any help people can offer for any of the above queries would be appreciated.

I use my VBA code to sort the data in certain columns. I have been doing it with the following code:

In this particular case I am sorting the data in column E. However, I also have "...Range("A1") " in the
function call and don't know why I have to include the "Range" portion. I can put any valid range that I want in
the "Range" call, but then I have to specify which column to sort also. 

Can I make this happen with only using 1 range reference?

Hello Forum,

I'm doing a macros that should sort a range by 3 columns, like the sort from excel,I'm block at the part were i have to make the code for the sort, because i use 3 combo to select the columns used to sort the range.
Here is what I've done till now .

I'm trying to sort a whole list of data that's been separated into blocks using a blank row and a row containing the value "a". I want to sort each block of data using VBA code because I'll need to add in extra code when this has worked.

Columns A-E have values:

Column A is empty until the last row which has a value of X.
Column C is the one I'm using as my active column.
All other columns have values that I want to examine.

At the beginning of each of the blocks of rows that I want examine is a row containing no values.
At the end of each of the blocks of rows that I want examine is a row containing the value "a" in column C.

My code is:
Dim c As Object
Set c = ActiveCell
Do Until Not IsEmpty(c.Offset(0, -2))
' this should stop code executing when it reaches the X

With c

If c.Value = "" Then
Set c = c.Offset(1, 0)

Do
ActiveCell.Rows.Select
Set c = c.Offset(1, 0)
Loop Until c.Value = "a"

ElseIf c.Value = "a" Then

Selection.Sort Key1:=Range(c.Offset(0, 2)), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Set c = c.Offset(1, 0)
End If

End With
Loop

End Sub
I can't get this code to work and I've tried so many variations that I'm going insane. Anyone help?

Part I ComboBox

I have a spreadsheet set up that has 2 worksheets. On WS2 I have a list of employees with their full names in one column starting on D5. On WS1 I have a combobox set up that is pulling the names from WS2. For cboEmployeeList I named a range on WS2 (EmployeeNames = $D$5:$D$1200) and added EmployeeNames to cbo's ListFillRange property. Right now I only have 20 names added and will be adding/deleting names frequently. Is there a way to have cboEmployeeList just show the used cells in the column starting at D5 so I dont have a bunch of blank space in my combobox?

Part II Sorting:

With the same spreadsheet when I select a name from cboEmployeeList I added a button (btnInsert) that inserts a blank row at row 6 and puts the name selected in B6. I would like to be able to have this list automatically sort at the end of the click event. The starting point of the range for one row is B6:E6. With 8 names on the list the range would be B6:E13. I would like to be able to resort everytime I insert a name to the list. Again I will be adding names frequently so the sorted range will change frequently. Currently, I have it set so when I click btnInsert it inserts a blank row and adds the name. I cannot figure out the sorting part though.

Here is my code so far.....keep in mind that I have just started my VB.NET intro class (also my first venture into any kind of programming) so be kind if the code is ugly.

Private Sub btnInsert_Click()
Dim list As String
list = cboEmployeeList.Text

'Insert row when Insert Name button is clicked.
Worksheets("Transactions").Cells(6, 5).EntireRow.Insert

'Add selected name from cboEmployeeList to new row.
Worksheets("Transactions").Cells(6, 2) = list

End Sub

Hello,

I have got the following code in VBA however I wondered if there is any way to sort without the screen flashing all over the place (I assume because I am using Range("").Select).

Here is my code

Sub Sort()
    Rows("5:50").Select
    ActiveWindow.SmallScroll Down:=-39
    ActiveWorkbook.Worksheets("New").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("New").Sort.SortFields.Add Key:=Range("E6:E25") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("New").Sort
        .SetRange Range("A5:H30")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("A1").Select
End Sub
It is just an alphabetical sort of column E (between A5:H30)

Hello - - I have a question that I don't know if it's possible. I'm looking to copy the content of emails coming from the same email address (about 5 to 10 per day) and paste them into an Excel file. The goal is to compare records counts...day to day to day...and then flag ones where the variance is plus or minus x number of records.

The email body is very basic, 4 lines: Date, Filename, File size and Record Count.

Here's an example of the message:

Date: Thu 15 Sep 2011 09:01:09 AM EDT
Filename: AUTO_C12345_20012356874.dat
File size: 162887
Record Count: 475

Is there some starting code someone might have to share that would get me started?

As I think of my question, I think I'm about to make it a bit more complicated. In addition of puting this data into a specific excel file, the excel file would be the same day after day, where the newest data is placed in the first empty row. The data elements of the email: Date, Filename, File Size and Record Count become headers, so I think theres some sort of transpose thing I would need to consider as well. The emails should also be from the same address, but the ones that are unopened and ignore the already opened emails. Again, any thoughts and/or suggestions would be great.

Wasn't sure if should have marked it solved or not - - I moved on and tried something else. Basically I took the 4 lines from the email and pasted them into a spreadsheet - - from there I ran and recorded a series of steps like: text to columns, copy/paste special/transpose, moved cell values to final columns and rows, deleted empty rows, sorted the file by filename...etc. I put all my recorded code together and ran them all as one macro and it did what I needed. The only thing I do manually is copy the original email and paste it into this excel file. Then I run my code, then repeat. So it's fast enough for my situation.

If anyone is interested, here is my code from Excel:

Sub DataExchangeEmails()

' Section A - this section will format via text to columns plus uses copy/paste special to Transpose for columns
'             Saves the group of data to the 50th row

 Selection.TextToColumns Destination:=Range("I1"), DataType:=xlFixedWidth, _
        OtherChar:="_", FieldInfo:=Array(Array(0, 1), Array(15, 1)), _
        TrailingMinusNumbers:=True
    Range("I1:L4").Select
    Selection.Copy
    Range("A50").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    Range("A49").Select
    Range("H1").Select
    
' Section B - this will remove or delete any blank rows between row 50 and the last blank row above

Dim bye As Long

With ActiveSheet
    For bye = .Cells.SpecialCells(xlCellTypeLastCell).Row _
        To 1 Step -1

        If WorksheetFunction.CountA(.Rows(bye)) = 0 Then
            ActiveSheet.Rows(bye).Delete
        End If

    Next
End With

' Section C - this deletes the newest data exchange entries header row, searches for DATE: in column A and deletes it,
starting in A2
 
Dim x As String
Dim b As Long
x = "Date:"
y = Cells(Rows.Count, 1).End(xlUp).Row
        For b = y To 2 Step -1
            If InStr(Cells(b, 1), x) > 0 Then ' the number here represents the column 1 being col A, 2 would be col B
            Rows(b).EntireRow.Delete
            End If
        Next b
MsgBox "Delete Complete"

' Secion D - this removes the original copied data values from the email that was copyed in I1.

Range("I1:L4").Select
    Selection.ClearContents
    
' section E - This resorts the rows by Filename

Range("g1").Select
    Range("A1").Select
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("B:B") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A:D")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub


I'm new to VBA and have been able to do what I needed until now. I wasn't sure how to word my question to easily explain my problem.

I've attached a spreadsheet showing what I need to accomplish, without using VBA. Columns A and B will contain given numbers. Column a will range from 0 to X in increments of 1, column B will start at 0 and contain increasing values from there. Column C will perform a calculation on the values in A & B and with each subsequent row will need to perform a summation of previous rows. I'm assuming this needs some sort of loop.

The equation would look something like this and the summation would continue until after a cell with value 0 was encountered.

At any row = n

Cn = (Bn - B(n-1))*(An - A(n-1)) + (B(n-1) - B(n-2))*(An - A(n-2)) + (B(n-2) - B(n-3))*(An - A(n-3)) . . . . .etc

Hopefully I've explained it well enough, let me know if you need any clarification. Thanks in advance for any help you can provide!

Hi,
I have a table with eleven names (one name per column) each name has 90 rows associated with it. I would like to be able to sort the columns by names and their order will be defined in a custom sort list. When the names are sorted, their associated rows will "follow".

I am having a bit of trouble figuring out code that will use a custom sort list that is dynamic. I can create a custom list ok, and I can sort using the list just fine. I am able to get the code needed from the recorder. My trouble is that the CustomOrder field is literally what is in the range the list is created from. The names in quotes will change time to time, so what I need ideally is the proper way to use the range in the CustomOrder field.I tried CustomOrder:=Application.CustomListCount + 1 in place of "Mike,Fred,Pete,John,Steve,Cecil,Joe,Rob,Bryan,Rick,Phil", but it did not work. This seems like it would be easy, and probably is to those more schooled in VBA. However, I am stumped.

Thanks,

Sub SortMLCptn()
'
'
'Sort Cptn
    ' custom list created then deleted after sort
    Sheets("Seniority ML").Select
    Application.AddCustomList Listarray:=Range("D3:D16")
    Sheets("Crew").Select
    Range("AE3:AN98").Select
    ActiveWorkbook.Worksheets("Crew").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Crew").Sort.SortFields.Add Key:=Range("AE3:AN3"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
        "Mike,Fred,Pete,John,Steve,Cecil,Joe,Rob,Bryan,Rick,Phil" _
        , DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Crew").Sort
        .SetRange Range("AE3:AN98")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlLeftToRight
        .SortMethod = xlPinYin
        .Apply
    End With
    MsgBox "Sort Complete"
    ' remove latest custom sort entry order
    Application.DeleteCustomList Application.CustomListCount
 

End Sub


Hello everyone! I am new at using VBA and macros, but think that they are the answer to my problems. I have tried a number of formulas but cannot seem to find one that will work for me. And now in going through the tutorials on macros online, am still confused! Here is my problem.

Within my one excel spreadsheet I need to split it into two spreadsheets based on which state the row is associated with. Additionally within a few states there are exceptions based on the "company" columns criteria which would need to be further sorted. I currently am using another excel spreadsheet which serves as a reference for when I am splitting the one worksheet into two. I have attached both sheets below- the two different color highlights show the breakdown of how I need to split the sheet into two green and yellow. The darker yellow highlight are the exceptions to the state sorting.

Hi everyone,

I have a spread sheet that is used for staff rotas with a wide range of shift start and end times and I need some help with the data input.

In a separate spread sheet shift start and end times are entered in separate cells from drop-down lists as text.

Example Start: 10:00 End: 18:00

The times are then concatenated and sorted to form another drop-down list so that a complete shift can be selected in another part of the workbook –

Example: 10:00-18:00.

This side of things is working fine.

The problem I have is at the input stage of the shift start and end times. When there are a lot of different shift combinations (up to 100) the page looks unsightly with just long columns of start and end times..

What I would like to be able to do is to tidy up the spreadsheet by just having 3 cells:

1 to input the start time using my existing drop-down list
2 to input the shift end time using my existing drop-down list
3 a cell to confirm or O.K. the selection

I would like these 3 cells to automatically populate my data list that combines the start and end times.

This is a large work book and so far I haven’t used VBA – but if that’s the only solution lets give it a try.

Any suggestions please?

Thank you

Thornwick

Can anyone help with the following problem?
I have a worksheet 'StockList' which I am using to perform a calculation on
how many times a specific text value appears on another worksheet column.
My 'Stocklist' worksheet has the following layout
Item CurrentStock Issued MaxStock ReOrderLevel StockInput
StockInput ->repeated

Item is a text description of my stock item
CurrentStock is a simple SUM of the current row range from all the
'StockInput' columns
Issued is a formula to count the number of times Item description from
another worksheet within the range C2:C500 is made
=COUNTIF(Issue!C2:C500,A2)
=COUNTIF(Issue!C2:C500,A3)
etc etc going down the cells.

As I may need to add more stock 'Item'(s) there is a need to take the whole
range A1:IV500 and sort it by column A (Item) but on doing so, any new
'item' rows have the COUNTIF formula altered with the table cell row number.
For example a new item gets sorted and appears in the list in row 10 the
formula gets changed to

=COUNTIF(Issue!C10:C510,A10) and I need the references to the C column to
remain constant.
Is there any way to re-populate the column C from C2:C500 with the correct
formula?


No luck finding an answer? You could always try Google.