Nunu
10-04-2008, 04:31 PM
:banghead: :banghead: :banghead:
Hello everybody:)
I have a HUGE problem with a .xls spreadsheet, and I'm a total newbie at this so any help will be appreciated...
The attached file contains two spreadsheets "Members" and "Result", respectively. The "Members" spreadsheet contains info concerning all the members, and the "Result" spreadsheet is empty and needs to be filled with the results of "my" findings. The assignment is to write a program in VBA that can select the members based on following criteria:
- The member needs to live in Odense (a town in Denmark) which includes following: Odense C, Odense M, Odense S, Odense SV, Odense N, Odense NV, Odense S?, Odense N?, Odense V.
- The age of the members situated in all abovementioned areas of the town of Odense must be between 30 and 35 years of age, (including both the years, 30 and 35)
- Furthermore the user must be able to choose the gender of the members by using an InputBox. ( In Denmark the gender of a person can be determined by the last digit in the Civil Registration Number, CVR. The even numbers represent the females and the odd numbers are the guys).
The members that qualify all the above conditions are then to be copied to the "Result" spreadsheet, and just to make things even more complicated ONLY the following criteria should be copied:
- The full name of the member
- The address
- The zip code
- Town
- Age
If you have trouble understanding any of the above, feel free to contact me.
This is what I've got so far, and its probably all wrong:(
Option Explicit
Sub BosiddendeUdvalg()
With ThisWorkbook.Sheets("Medlemmer").Range("A1")
Dim ByRange As Range
Dim i As Integer
Dim Number As Integer
Dim ByRangeValue As Range
Dim AlderRange As Range
'.Offset(1, 4) End(xlDown)
Set ByRange = Range(.Offset(1, 4), .End(xlDown))
i = ByRange.Rows.Count
For Number = 1 To i
Set ByRangeValue = Range("E18")
'Range(.Offset(Number, 4), 0)
If ByRangeValue = "Odense C" Then
'Or "Odense V" Or "Odense NV" Or "Odense S?" Or "Odense M" Or "Odense N?" Or "Odense SV" Or "Odense S" Or "Odense N" Then
Set AlderRange = Range("I18")
If AlderRange > 29 And AlderRange < 36 Then
Range(.Offset(i, 0), .Offset(i, -4)).Copy _
Destination:=ThisWorkbook.Sheets("Resultat").Range("A2")
Range(.Offset(i, 4)).Copy Destination:=ThisWorkbook.Sheets("Reslutat").Range("A5")
End If
End If
Next Number
End With
End Sub
Hello everybody:)
I have a HUGE problem with a .xls spreadsheet, and I'm a total newbie at this so any help will be appreciated...
The attached file contains two spreadsheets "Members" and "Result", respectively. The "Members" spreadsheet contains info concerning all the members, and the "Result" spreadsheet is empty and needs to be filled with the results of "my" findings. The assignment is to write a program in VBA that can select the members based on following criteria:
- The member needs to live in Odense (a town in Denmark) which includes following: Odense C, Odense M, Odense S, Odense SV, Odense N, Odense NV, Odense S?, Odense N?, Odense V.
- The age of the members situated in all abovementioned areas of the town of Odense must be between 30 and 35 years of age, (including both the years, 30 and 35)
- Furthermore the user must be able to choose the gender of the members by using an InputBox. ( In Denmark the gender of a person can be determined by the last digit in the Civil Registration Number, CVR. The even numbers represent the females and the odd numbers are the guys).
The members that qualify all the above conditions are then to be copied to the "Result" spreadsheet, and just to make things even more complicated ONLY the following criteria should be copied:
- The full name of the member
- The address
- The zip code
- Town
- Age
If you have trouble understanding any of the above, feel free to contact me.
This is what I've got so far, and its probably all wrong:(
Option Explicit
Sub BosiddendeUdvalg()
With ThisWorkbook.Sheets("Medlemmer").Range("A1")
Dim ByRange As Range
Dim i As Integer
Dim Number As Integer
Dim ByRangeValue As Range
Dim AlderRange As Range
'.Offset(1, 4) End(xlDown)
Set ByRange = Range(.Offset(1, 4), .End(xlDown))
i = ByRange.Rows.Count
For Number = 1 To i
Set ByRangeValue = Range("E18")
'Range(.Offset(Number, 4), 0)
If ByRangeValue = "Odense C" Then
'Or "Odense V" Or "Odense NV" Or "Odense S?" Or "Odense M" Or "Odense N?" Or "Odense SV" Or "Odense S" Or "Odense N" Then
Set AlderRange = Range("I18")
If AlderRange > 29 And AlderRange < 36 Then
Range(.Offset(i, 0), .Offset(i, -4)).Copy _
Destination:=ThisWorkbook.Sheets("Resultat").Range("A2")
Range(.Offset(i, 4)).Copy Destination:=ThisWorkbook.Sheets("Reslutat").Range("A5")
End If
End If
Next Number
End With
End Sub