Sorting data in VBA

Hi

See how this goes

Please Login or Register  to view this content.

rylo

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

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

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!

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

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

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

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.

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

Hi

Try changing

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

rylo

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

Works fine now :-)


Many Thanks!!!