Small updates to VBA Macro code

krazyness

New Member
Joined
Jan 31, 2017
Messages
23
Office Version
  1. 365
Platform
  1. Windows
Hi all - i hired someone years ago and they created the below code. Now with COVID i need a small change and cannot locate said person that created this for me.

Column A in the data now has a time in it. I need that time to be cleaned like he did with Column AB. Column A always shows date and time like this:
7/31/2020 13:00:00

I would like to sort by that time first before the other sorts also.

Then have the remaining "time" data printed just like AB's remaining is after being split... but in column A.

I hope that makes sense. and anyone can help as everything i've tried can never get it done correctly.


VBA Code:
Sub Macro1()
Dim LastRow As Long, r As Long
Dim myInput
myInput = InputBox("Enter Docket Date", "Enter Docket Date")
ActiveWorkbook.Worksheets("Jan 12 docket").PageSetup.LeftHeader = "&""Arial,Bold""&28 DOCKET: " & myInput
ActiveWorkbook.Worksheets("Jan 12 docket").PageSetup.RightHeader = "&P"
Sheets(Sheet1.Name).Activate
LastRow = Range("A" & Range("A:A").Rows.Count).End(xlUp).Row
Range("AB2:AB" & LastRow) = Evaluate("=Index(Mid(" & Range("AB2:AB" & LastRow).Address & ", " & "find("":""," & Range("AB2:AB" & LastRow).Address & ") + 2,find("",""," & Range("AB2:AB" & LastRow).Address & ")-find("":""," & Range("AB2:AB" & LastRow).Address & ") -2),)")
'Sort all of the data
ActiveWorkbook.Worksheets("Jan 12 docket").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Jan 12 docket").Sort.SortFields.Add Key:=Range( _
    "AB2:AB" & LastRow), SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:="Answer Hearing,Aid of Execution,Order Back,Citation,Bond Appearance", DataOption:= _
    xlSortNormal
ActiveWorkbook.Worksheets("Jan 12 docket").Sort.SortFields.Add Key:=Range( _
    "D2:D" & LastRow), SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:="Answer Hearing,Aid of Execution,Order Back,Citation,Bond Appearance", DataOption:= _
    xlSortNormal
ActiveWorkbook.Worksheets("Jan 12 docket").Sort.SortFields.Add Key:=Range( _
    "U2:U" & LastRow), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
    xlSortNormal
With ActiveWorkbook.Worksheets("Jan 12 docket").Sort
    .SetRange Range("A1:AB" & LastRow)
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
'Delete columns that are not needed
Columns("A:C").Delete Shift:=xlToLeft
Columns("B:Q").Delete Shift:=xlToLeft
Columns("D:H").Delete Shift:=xlToLeft
'Add Notes column
Range("D1") = "Notes"
'Arrange columns correctly
Columns("B:B").Cut
Columns("A:A").Insert
Application.CutCopyMode = False
'Split case_style_comp into two lines
r = 2
Do
    Rows(r + 1).Insert
   
    'Split case sides if "vs." is found
    If InStr(1, Cells(r, 3), "vs.") > 0 Then
        Cells(r + 1, 3) = Trim(Right(Cells(r, 3), Len(Cells(r, 3)) - InStr(1, Cells(r, 3), "vs.") - 4))
        Cells(r, 3) = Trim(Left(Cells(r, 3), InStr(1, Cells(r, 3), "vs.") + 3))
    End If
   
    Cells(r + 1, 2) = Cells(r, 4)
   
    Range("A" & r).Resize(2, 4).BorderAround ColorIndex:=1, Weight:=xlThin
    Range("A" & r).Resize(2, 3).BorderAround ColorIndex:=1, Weight:=xlThin
    r = r + 2
Loop While Cells(r, 1) <> ""
'Clear notes
Range("D2:D" & r).ClearContents
'fontsize
Columns("A:D").Font.Size = 14
'orientation
ActiveSheet.PageSetup.Orientation = xlLandscape
'fit all columns
With Worksheets("Jan 12 docket").PageSetup
.Zoom = False
.FitToPagesWide = 1
End With
'Autofit columns
Columns("A:C").AutoFit
Columns("D").ColumnWidth = 60
End Sub
 
Last edited by a moderator:

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Caleeco

Well-known Member
Joined
Jan 9, 2016
Messages
968
Hello krazyness,

This does sound possible, however, it would be very useful to get some sample data from you. It would help me to understand the intent of each action. I can then clean up the code to make it a but more streamlined.

Cheers
Caleeco
 

krazyness

New Member
Joined
Jan 31, 2017
Messages
23
Office Version
  1. 365
Platform
  1. Windows
Hello krazyness,

This does sound possible, however, it would be very useful to get some sample data from you. It would help me to understand the intent of each action. I can then clean up the code to make it a but more streamlined.

Cheers
Caleeco

Hi thank you for the response! This is killing me... i can't get where I need to go.

start_datetimeend_datetimejudgetypecourtroomtype_commentdefendant_sequencehearing_sequenceappearedsealedcase_subtypedisplay_typecase_typecase_yearcase_numbercase_sequencec_defendantlanguagereserved_attorneyreserved_partycase_fmtcase_style_compreserved_namelanguage_displaycustody_of_partydescriptionjudge_namecase_attorneys
7/24/2020 13:00:007/24/2020 13:00:00
1​
Order BackASusie Q
0​
29​
NCSXXCV
2020​
123​
49126​
2020-XX-000123MREXCEL vs. Susie QLarge CourtroomJones,StevePlaintiff Attorney: DeBerg, Steve


There is my header, and one line of data. There could be anywhere from 1 - 150 lines each with the series of different "type" and different Attorney names.

The output then gives me:

case_fmttypecase_style_compNotes
2020-XX-000123Order BackMREXCEL vs.
DeBergSusie Q

I need to take the time in A2 (13:00:00) and put it in A3 on output.

Ideally I'd also like it to sort that time in order first before any of the other sorting criteria that happened.
 

Caleeco

Well-known Member
Joined
Jan 9, 2016
Messages
968
Thanks for the sample row. That will certainly help. Could you clarify a couple things:
  • For the sample data you have supplied. What is the sheet name? Jan 12 Docket?
  • Where does the output end up? on the same sheet as the source data?
  • Do you have multiple cases per day? If so, doe they just stack below each other on the Output?
Thanks
Caleeco
 

krazyness

New Member
Joined
Jan 31, 2017
Messages
23
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

yes, sheet(1) is titled Jan 12 Docket

Sheet (2) is calld Prep Docket, and the only thing there is the button to run macro.

The output ends on Jan 12 Docket sheet.

That is also correct. there are between 1-150 cases on that listing.
 

krazyness

New Member
Joined
Jan 31, 2017
Messages
23
Office Version
  1. 365
Platform
  1. Windows
start_datetimeend_datetimejudgetypecourtroomtype_commentdefendant_sequencehearing_sequenceappearedsealedcase_subtypedisplay_typecase_typecase_yearcase_numbercase_sequencec_defendantlanguagereserved_attorneyreserved_partycase_fmtcase_style_compreserved_namelanguage_displaycustody_of_partydescriptionjudge_namecase_attorneys
8/7/2020 14:30:008/7/2020 14:30:00
1​
Answer HearingAname,name
0​
8​
NCSxxCV
2020​
123​
62481​
2020-xx-000123name vs. nameLarge Courtroomjudge, judgePlaintiff Attorney: deberg, steve
8/7/2020 14:00:008/7/2020 14:00:00
1​
Answer HearingAname,name
0​
7​
NCSxxCV
2020​
124​
62485​
2020-xx-000124name vs. nameLarge Courtroomjudge, judgePlaintiff Attorney: deberg, steve
8/7/2020 13:30:008/7/2020 13:30:00
1​
Answer HearingAname,name
0​
11​
NCSxxCV
2020​
125​
62467​
2020-xx-000125name vs. nameLarge Courtroomjudge, judgePlaintiff Attorney: deberg, steve
8/7/2020 13:30:008/7/2020 13:30:00
1​
Answer HearingAname,name
0​
9​
NCSxxCV
2020​
126​
62479​
2020-xx-000126name vs. nameLarge Courtroomjudge, judgePlaintiff Attorney: deberg, steve
8/7/2020 13:00:008/7/2020 13:00:00
1​
Bond AppearanceAname,name
0​
18​
NCSxxCV
2020​
127​
53130​
2020-xx-000127name vs. nameLarge Courtroomjudge, judgePlaintiff Attorney: deberg, steve
8/7/2020 13:00:008/7/2020 13:00:00
1​
Answer HearingAname,name
0​
16​
NCSxxCV
2020​
128​
62493​
2020-xx-000128name vs. nameLarge Courtroomjudge, judgePlaintiff Attorney: deberg, steve
8/7/2020 14:00:008/7/2020 14:00:00
1​
Answer HearingAname,name
0​
4​
NCSxxCV
2020​
129​
62480​
2020-xx-000129name vs. nameLarge Courtroomjudge, judgePlaintiff Attorney: deberg, steve
8/7/2020 14:30:008/7/2020 14:30:00
1​
Answer HearingAname,name
0​
1​
NCSxxCV
2020​
130​
62466​
2020-xx-000130name vs. nameLarge Courtroomjudge, judgePlaintiff Attorney: deberg, steve
8/7/2020 14:00:008/7/2020 14:00:00
1​
Answer HearingAname,name
0​
1​
NCSxxCV
2020​
131​
62469​
2020-xx-000131name vs. nameLarge Courtroomjudge, judgePlaintiff Attorney: deberg, steve
8/7/2020 14:00:008/7/2020 14:00:00
1​
Answer HearingAname,name
0​
3​
NCSxxCV
2020​
132​
62478​
2020-xx-000132name vs. nameLarge Courtroomjudge, judgePlaintiff Attorney: deberg, steve
8/7/2020 13:00:008/7/2020 13:00:00
1​
Answer HearingAname,name
0​
7​
NCSxxCV
2020​
133​
62401​
2020-xx-000133name vs. nameLarge Courtroomjudge, judgePlaintiff Attorney: deberg, steve
8/7/2020 14:30:008/7/2020 14:30:00
1​
Answer HearingAname,name
0​
12​
NCSxxCV
2020​
134​
62468​
2020-xx-000134name vs. nameLarge Courtroomjudge, judgePlaintiff Attorney: deberg, steve
8/7/2020 14:00:008/7/2020 14:00:00
1​
Answer HearingAname,name
0​
5​
NCSxxCV
2020​
135​
62482​
2020-xx-000135name vs. nameLarge Courtroomjudge, judgePlaintiff Attorney: deberg, steve
8/7/2020 13:00:008/7/2020 13:00:00
1​
Answer HearingAname,name
0​
15​
NCSxxCV
2020​
136​
62474​
2020-xx-000136name vs. nameLarge Courtroomjudge, judgePlaintiff Attorney: deberg, steve
8/7/2020 13:30:008/7/2020 13:30:00
1​
Answer HearingAname,name
0​
5​
NCSxxCV
2020​
137​
61804​
2020-xx-000137name vs. nameLarge Courtroomjudge, judgePlaintiff Attorney: deberg, steve
8/7/2020 13:00:008/7/2020 13:00:00
1​
HearingAname,name
0​
12​
NCSxxCV
2020​
138​
50303​
2020-xx-000138name vs. nameLarge Courtroomjudge, judgePlaintiff Attorney: deberg, steve
8/7/2020 13:00:008/7/2020 13:00:00
1​
Answer HearingAname,name
0​
13​
NCSxxCV
2020​
139​
61910​
2020-xx-000139name vs. nameLarge Courtroomjudge, judgePlaintiff Attorney: deberg, steve
8/7/2020 13:00:008/7/2020 13:00:00
1​
Aid of ExecutionAname,name
0​
1​
NCPxxCV
2020​
140​
46589​
2020-xx-000140name vs. nameLarge Courtroomjudge, judgePlaintiff Attorney: deberg, steve
8/7/2020 13:00:008/7/2020 13:00:00
1​
Aid of ExecutionAname,name
0​
4​
NCPxxCV
2020​
141​
52181​
2020-xx-000141name vs. nameLarge Courtroomjudge, judgePlaintiff Attorney: deberg, steve
8/7/2020 13:00:008/7/2020 13:00:00
1​
Aid of ExecutionAname,name
0​
2​
NCPxxCV
2020​
142​
53956​
2020-xx-000142name vs. nameLarge Courtroomjudge, judgePlaintiff Attorney: deberg, steve
8/7/2020 13:00:008/7/2020 13:00:00
1​
Aid of ExecutionAname,name
0​
3​
NCPxxCV
2020​
143​
58075​
2020-xx-000143name vs. nameLarge Courtroomjudge, judgePlaintiff Attorney: deberg, steve
8/7/2020 13:00:008/7/2020 13:00:00
1​
Answer HearingAname,name
0​
6​
NCSxxCV
2020​
144​
62446​
2020-xx-000144name vs. nameLarge Courtroomjudge, judgePlaintiff Attorney: deberg, steve
8/7/2020 13:00:008/7/2020 13:00:00
1​
HearingAname,name
0​
17​
NCSxxCV
2020​
145​
46826​
2020-xx-000145name vs. nameLarge Courtroomjudge, judgePlaintiff Attorney: deberg, steve
8/7/2020 13:00:008/7/2020 13:00:00
1​
Answer HearingAname,name
0​
10​
NCSxxCV
2020​
146​
62452​
2020-xx-000146name vs. nameLarge Courtroomjudge, judgePlaintiff Attorney: deberg, steve
8/7/2020 14:00:008/7/2020 14:00:00
1​
Bench TrialAname,name
0​
10​
NCSxxCV
2020​
147​
61868​
2020-xx-000147name vs. nameLarge Courtroomjudge, judgePlaintiff Attorney: deberg, steve
8/7/2020 14:30:008/7/2020 14:30:00
1​
Answer HearingAname,name
0​
10​
NCSxxCV
2020​
148​
62489​
2020-xx-000148name vs. nameLarge Courtroomjudge, judgePlaintiff Attorney: deberg, steve
8/7/2020 13:00:008/7/2020 13:00:00
1​
Answer HearingAname,name
0​
9​
NCSxxCV
2020​
149​
62449​
2020-xx-000149name vs. nameLarge Courtroomjudge, judgePlaintiff Attorney: deberg, steve
8/7/2020 13:00:008/7/2020 13:00:00
1​
Answer HearingAname,name
0​
5​
NCSxxCV
2020​
150​
62445​
2020-xx-000150name vs. nameLarge Courtroomjudge, judgePlaintiff Attorney: deberg, steve
8/7/2020 13:30:008/7/2020 13:30:00
1​
Answer HearingAname,name
0​
4​
NCSxxCV
2020​
151​
61915​
2020-xx-000151name vs. nameLarge Courtroomjudge, judgePlaintiff Attorney: deberg, steve
8/7/2020 14:30:008/7/2020 14:30:00
1​
Answer HearingAname,name
0​
11​
NCSxxCV
2020​
152​
62471​
2020-xx-000152name vs. nameLarge Courtroomjudge, judgePlaintiff Attorney: deberg, steve
8/7/2020 13:30:008/7/2020 13:30:00
1​
Answer HearingAname,name
0​
2​
NCSxxCV
2020​
153​
62455​
2020-xx-000153name vs. nameLarge Courtroomjudge, judgePlaintiff Attorney: deberg, steve
8/7/2020 14:30:008/7/2020 14:30:00
1​
Answer HearingAname,name
0​
7​
NCSxxCV
2020​
154​
62477​
2020-xx-000154name vs. nameLarge Courtroomjudge, judgePlaintiff Attorney: deberg, steve
8/7/2020 13:30:008/7/2020 13:30:00
1​
Answer HearingAname,name
0​
6​
NCSxxCV
2020​
155​
62461​
2020-xx-000155name vs. nameLarge Courtroomjudge, judgePlaintiff Attorney: deberg, steve
8/7/2020 13:00:008/7/2020 13:00:00
1​
Answer HearingAname,name
0​
14​
NCSxxCV
2020​
156​
62040​
2020-xx-000156name vs. nameLarge Courtroomjudge, judgePlaintiff Attorney: deberg, steve
 

Attachments

  • 1596656024165.png
    1596656024165.png
    240.5 KB · Views: 4

krazyness

New Member
Joined
Jan 31, 2017
Messages
23
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

above here is the full "edited" list for 8/7.

Below is the output once i run the macro.

1596656141004.png
 

Caleeco

Well-known Member
Joined
Jan 9, 2016
Messages
968
Thanks for the screenshot, it makes the problem much clearer 👍

Could you please check if the DATE in column A is a DATE or TEXT in the source data? The way to check, would be to inset a column to the right of it. And in Cell B2 type the formula
VBA Code:
=A2 + 1

What does B2 return?
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,610
Messages
5,765,412
Members
425,285
Latest member
andypandypoo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top