School exercise, i'm desperate

[Poeli]

Hi all, I'm new here and I really suck at VBA... I study airplane engineering but we also have vba excel, we made an excercise in class, but I really don't get it... This is the code:
Option Explicit

Function berekenTijd _
(aankomsthuidig As Date, landingvorig As Date) As Date
Dim landinghuidig As Date
landinghuidig = landingvorig + TimeValue("0:10:0")
If aankomsthuidig > landinghuidig Then
landinghuidig = aankomsthuidig
End If
berekenTijd = landinghuidig
End Function

Sub brengDataSamenEnSorteer()
Dim aantalbaan1 As Integer
Dim aantalbaan2 As Integer

Sheets("eindresultaat").Select
Range("A2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents

Sheets("Landingsbaan1").Select
Range("A2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
aantalbaan1 = Selection.Rows.Count

Sheets("Eindresultaat").Select
Range("A2").Select
ActiveSheet.Paste

Sheets("Landingsbaan2").Select
Range("A2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
aantalbaan2 = Selection.Rows.Count

Sheets("Eindresultaat").Select
Range("A" & (aantalbaan1 + 2)).Select
ActiveSheet.Paste

Range("D1").Select
Application.CutCopyMode = False
ActiveWorkbook.Worksheets("Eindresultaat").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Eindresultaat").Sort.SortFields.Add Key:=Range( _
"D1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Eindresultaat").Sort
.SetRange Range("A2:F" & (aantalbaan1 + aantalbaan2 + 1))
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

End Sub

I know why we use the range and select certain cells, but i don't know why you go for example xtoright.select, can't you say for example range (cellyouwanttoselect).slect instead of range(selection,selection.end(x1toright)).select?

What I also don't understand is the complete last paragraph.. Certainly the .setrange, .header and ect not.
Pff I completely suck at it and this is the kind of excercise we get on our exam... I do know what most of the things mean, but i don't see it as a whole..Does anyone has some tips for me? We don't even have a decent book about it, only excercises...
Thank you

[mdmackillop]

Do you know how to step through code?

This code replicates keyboard actions

Range("A2").Select
'Select cell A2
Range(Selection, Selection.End(xlToRight)).Select
'Hold Shift and press Right Arrow
Range(Selection, Selection.End(xlDown)).Select
'Hold Shift and press Down Arrow
Selection.ClearContents
'Clear selected area

We use End.Down, ToRight etc. when we don't know the range we will be dealing with. They could be in different sheets, diiferent workbooks and could comprise more than one distinct area. If you get your head round it, you will find it vey useful.

[Poeli]

Do you know how to step through code?

This code replicates keyboard actions

Range("A2").Select
'Select cell A2
Range(Selection, Selection.End(xlToRight)).Select
'Hold Shift and press Right Arrow
Range(Selection, Selection.End(xlDown)).Select
'Hold Shift and press Down Arrow
Selection.ClearContents
'Clear selected area

We use End.Down, ToRight etc. when we don't know the range we will be dealing with. They could be in different sheets, diiferent workbooks and could comprise more than one distinct area. If you get your head round it, you will find it vey useful.
Yes, so this is just the code to select certain cells? But what I don't get why we select all those cells. Is it just to make sure that there is no text in it?
Thank you!

[mdmackillop]

It is clearing an area before pasting in new data. You could clear the sheet, but you may wish to retain Headers in Row1, so this more complicated method.

[mdmackillop]

If you want a good free book, try here (http://www.mrexcel.com/marketwatch.html)

[Poeli]

ok thank you!

[rbrhodes]

re: 'Whole last paragraph..." Is an Excel 2007 Sort. If you use the Macro Recorder and sort data that's the code you'll get.

It could be re-written simpler of course.




'Select D1
Range("D1").Select

'Kill the 'marching ants'
Application.CutCopyMode = False

'//Begin sort

'Clear old sort
ActiveWorkbook.Worksheets("Eindresultaat").Sort.SortFields.Clear

'Build base for new one
ActiveWorkbook.Worksheets("Eindresultaat").Sort.SortFields.Add Key:=Range( _
"D1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal

'Set range, has header row?, Match case in sort, etc
With ActiveWorkbook.Worksheets("Eindresultaat").Sort
.SetRange Range("A2:F" & (aantalbaan1 + aantalbaan2 + 1))
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin

'Do sort
.Apply
End With

'//End sort


'Alternative sort (NOT by recorder)

'//Begin sort
With ActiveWorkbook.Worksheets("Eindresultaat").Sort

'Clear old sort
.SortFields.Clear

'Build new one
.SortFields.Add Key:=Range("D1"), SortOn:=xlSortOnValues, _
Order:=xlAscending, DataOption:=xlSortNormal

.SetRange Range("A2:F" & (aantalbaan1 + aantalbaan2 + 1))
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin

'Do sort
.Apply
End With

[Poeli]

thank you! I'm beginning to get it!
Just some more specific questions:
In some cases when we select we use range and select our cells, but then we do
'application.cutcopymode=false' Why is this? is this to make sure that excel doesn't do anything when there's nothing in the cells? After this comes selection.copy so...

And then there is this:
ActiveWorkbook.Worksheets("Eindresultaat").Sort.SortFields.Add Key:=Range( _
"D1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Eindresultaat").Sort
.SetRange Range("A2:F" & (aantalbaan1 + aantalbaan2 + 1))
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
What is the add key in the beginning? and the data option? For the rest I really don't know where the setrange, header, matchcase,orientation and sorthmethod comes from...Can anyone help? If I know why we use those I fully understand my excercice!
Thank you

[rbrhodes]

Hi Po,

1) CutcopyMode = false is usually to simply get rid of the Excel selection indicator, commonly known as 'marching ants'


2) Sort is a monster of options

- ActiveWorkbook.Worksheets("Eindresultaat").Sort.SortFields.Add Key:=Range("D1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

- Add Key: = What to sort, in this case Column D. Could be more keys added eg sort first on Col D then Col E then Col E ...each of which would be an added key. All the rest are options on HOW to sort the key (Column)


With ActiveWorkbook.Worksheets("Eindresultaat").Sort
.SetRange Range("A2:F" & (aantalbaan1 + aantalbaan2 + 1))

-this is the total area (columns) to include when sorting. for instance I can include ### cols based on 1 key


.Header = xlNo

- Is there a header row which should not be sorted? Y/N/Guess


.MatchCase = False

- Sort by Caps?

.Orientation = xlTopToBottom

-Ascending or descending?

.SortMethod = xlPinYin

- PinYin or stroke (Romanization... google it!)

.Apply

- Do the damn sort already!

End With

Try help or Object Browser in the VBA (or just ignore it all and record aMacro that does what you want, <grin> )


d