Solved: PLZ HELP...muliple criteria in Excel

[Nunu]

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

[Bob Phillips]

[vba]

Sub BosiddendeUdvalg()
Dim sh As Worksheet
Dim UserType As String
Dim i As Long
Dim LastRow As Long
Dim NextRow As Long
Dim RightChar As Long

With ThisWorkbook.Sheets("Members")

Set sh = Worksheets("Result")
LastRow = sh.Cells(sh.Rows.Count, "A").End(xlUp).Row
sh.Range("A2").Resize(LastRow, 5).ClearContents
Do

UserType = InputBox("Male of female users (M/F)?")
Loop Until UserType = "M" Or UserType = "F"
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
NextRow = 2

For i = 2 To LastRow

If Left(.Cells(i, "E").Value, 6) = "Odense" Then

If .Cells(i, "I").Value > 29 And .Cells(i, "I").Value < 36 Then

RightChar = Val(Right(.Cells(i, "H").Value, 1))
If (UserType = "F" And (RightChar \ 2) * 2 = RightChar) Or _
(UserType = "M" And (RightChar \ 2) * 2 <> RightChar) Then
sh.Cells(NextRow, "A").Value = .Cells(i, "A").Value & " " & .Cells(i, "B").Value
sh.Cells(NextRow, "B").Value = .Cells(i, "C").Value
sh.Cells(NextRow, "C").Value = .Cells(i, "D").Value
sh.Cells(NextRow, "D").Value = .Cells(i, "E").Value
sh.Cells(NextRow, "E").Value = .Cells(i, "I").Value
NextRow = NextRow + 1
End If
End If
End If
Next i
End With

End Sub
[/vba]

[Nunu]

XLD - you're the MAN!! The Lord of VBAX, I bow to thee

Thanks for taking the time, works like a charm...


Btw. I received a friendly warning earlier that I should not expose peoples' data like that, so I would like to take this opportunity to express my gratitute to everybody and say that the data in the attached file is ALL dummy data, so no worries about it being misused in any way...

VBA Express Forum Rocks!

Nunu

[Bob Phillips]

Thanks for that clarification Nunu, I must admit that it did concern me a little when I saw it.

[NanaHawk]

[vba]


LastRow = sh.Cells(sh.Rows.Count, "A").End(xlUp).Row
sh.Range("A2").Resize(LastRow, 5).ClearContents
Do

UserType = InputBox("Male of female users (M/F)?")
Loop Until UserType = "M" Or UserType = "F"
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

[/vba]

Looks like a great solution XLD (as your code always is) but why determine LastRow twice? Does it change between the two points where it is assessed?

Hawk

[Bob Phillips]

Hawk,

I am determining the LastRow on 2 different sheets, that is why I do it twice. I suppose that it could be argued that I should have used 2 separate variables, but I didn't need to retain the first, it was done with after the resize, so I re-used the variable.