Sorting data in VBA

[Dr Mambo]

Hi All,

I am encountering numerous problems compiling a macro to sort some pretty messy data. Hoping some of you could take a look and offer some suggestions....

The Data I need sorting is shown in the 'DATARAW' worksheet, this comes in every day and shows positions for each traded period. Usually 48 periods which I have highlighted although the data download could somtimes consist of 46 or 50 periods.

Sheet 1 (DATA RAW) contains data which I download daily.

Sheet 2 (Data Sort) is how I need it to look after sorted (I only need the data in DATARAW replacing, not another worksheet creating).

So for example:

Where column A = SP8 and the adjacent column B = 1 I would need:

All values which = ECN to concatenate the value in B to the reference in column B directly adjacent to the value next to it, until the folowing rows are presented:

So for instance the first value reported would be 131964, second 131979 etc... until the following text is shown in the rows below

IDD
CAT
ACE
ACT
ACE
ACT


When these appear I need the entire rows to be deleted.

Then basically the same for the next period where SP8 appears in column A and 2 appears in Column B.

also, the final result should contain no formulas so it can be referenced by lookups etc... also, all numeric colums should be stored as number so excel doesn't get confused.

This will basically repeat itself until the end of the file showing the same split as aove but with ZZZ as a footer. Which I also need to delete. :-S

IDD
CAT
ACE
ACT
ACE
ACT
ZZZ

Rows containing the values below also need deleting.

AAA
NOH
DSI
AED


I've used colours in the attachment but the actual file doesn't have any.

Am sure this would be a simple macro for many of you but I am totally stumped, any help would be very much apprieciated.

[rylo]

Hi

See how this goes

Sub aaa()
  Sheets("Data Raw").Select
  Range("A1:L1").Value = Array("H1", "H2", "H3", "H4", "H5", "H6", "H7", "H8", "H9", "H10", "H11", "H12")
  
  Rows("2:4").Delete
  arr = Array("IDD", "CAT", "ACE", "ACT", "ZZZ")
  
  For i = LBound(arr) To UBound(arr)
    Range("A:L").AutoFilter field:=1, Criteria1:=arr(i)
    Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row).SpecialCells(xlCellTypeVisible).EntireRow.Delete
  Next i
  ActiveSheet.ShowAllData
  ActiveSheet.AutoFilterMode = False
  
  For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
    If Cells(i, 1) = "SP8" Then
      holder = Cells(i, 2).Value
    Else
      Cells(i, 1).Value = holder & Cells(i, 2).Value
      Cells(i, 3).Value = holder
    End If
  
  Next i
  
  Range("A:L").AutoFilter field:=1, Criteria1:="SP8"
  Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row).SpecialCells(xlCellTypeVisible).EntireRow.Delete
  ActiveSheet.AutoFilterMode = False
  Rows("1:1").Delete
  Range("A:L").Sort key1:=Range("A1"), order1:=xlAscending
  
  
End Sub

rylo

[Dr Mambo]

Hi,

Thanks... that works brilliantly, however when I copy the macro to my Existing Project (containing other pages with macros) I am encountering a compiler error, it just wont let me run it in a different spreadsheet. Any Ideas?

Ta

[rylo]

Hi

1) What is the error
2) Do you have an option explicit? If so, then all the variables in my macro will have to be defined.
3) Can you replicate the problem in an example file. If so, please attach..


rylo

[Dr Mambo]

The error seems to relate to this section in the code and stops here.

arr = Array("IDD", "CAT", "ACE", "ACT", "ZZZ")

I have the working example attached as 'Working Macro' The content is exactly the same and it works.

This consists of 3 worksheets

1) Go Button (button running the macro)
2) Data to copy (nothing attached to this it's just got a copy of the raw data file to sort)
3) DataCont

Pressing the SORT EOD button runs the macro fine.


The worksheet I'm trying to add it to is 'EODReportCheckv1.1WIP (most of the buttons won’t work as they are relative to file locations so the test buttons are:

Copy EOD (gets a copy of the file data and places it into the DataCont worksheet).

Sort EOD (this contains a carbon copy of the macro in the Working macro workbook). Clicking the macro button produces the error mentioned above... even more bizarrely, if I open the Working Macro workbook and assign that macro to the button in the 'EODReportCheckv1.1WIP workbook against the Copy EOD button and then close the Working Macro Workbook down. The working macro workbook will open and the macro will run perfectly in the 'EODReportCheckv1.1WIP workbook.

Again, thanks for your help and hope to hear from you soon!

[rylo]

Hi

I opened your EOD file, and pressed CopyCOD then SortCOD and both ran fine. No errors just went straight through...

Try just having that opened with no other workbook and see if that helps.

rylo

[Dr Mambo]

Nope still doesn't work on any of my machines, I do have an option explict on quite a few of my macros (may not have included them) I need to define the varibles in the header but have been playing around and still havent got them. Could anyone define these and show me?

Ta

[Dr Mambo]

PS I know I'm being a pain :-S

[Dr Mambo]

Hi,

Still Having issues, have copied the names from Rylo's macro and changed for the approrriate worksheets etc as below:

Sub EODSort()




Sheets("DataSort").Select
Range("A1:L1").Value = Array("H1", "H2", "H3", "H4", "H5", "H6", "H7", "H8", "H9", "H10", "H11", "H12")

Rows("2:4").Delete
arr = Array("IDD", "CAT", "ACE", "ACT", "ZZZ")

For i = LBound(arr) To UBound(arr)
Range("A:L").AutoFilter Field:=1, Criteria1:=arr(i)
Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row).SpecialCells(xlCellTypeVisible).EntireRow.Delete
Next i
ActiveSheet.ShowAllData
ActiveSheet.AutoFilterMode = False

For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
If Cells(i, 1) = "SP8" Then
holder = Cells(i, 2).Value
Else
Cells(i, 1).Value = holder & Cells(i, 2).Value
Cells(i, 3).Value = holder
End If

Next i

Range("A:L").AutoFilter Field:=1, Criteria1:="SP8"
Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row).SpecialCells(xlCellTypeVisible).EntireRow.Delete
ActiveSheet.AutoFilterMode = False
Rows("1:1").Delete
Range("A:L").Sort Key1:=Range("A1"), Order1:=xlAscending


End Sub


As my current workbook contains an option expilct with named variables (these are from recorded macros), it still wont work with my project.

All I literally need is the variables assigning for the abov project and I'm sure that would make it work in my current workbook. Just havent got a clue. Have tried numerous times and it just kills the macro.

[Dr Mambo]

Have tried with the following variables inserted

Sub EODSort()

Dim arr As String
Dim i As Integer
Dim Holder As Integer

But it kills the macro at LBound, have no experience with defining variables so am stabbing in the dark here.

ta

[rylo]

Hi

Try changing

Dim arr As String

to

Dim arr As variant

rylo

[Dr Mambo]

Rylo, you're a gentleman and a Scholar...

Works fine now :-)


Many Thanks!!!