PDA

View Full Version : Solved: PLZ HELP...muliple criteria in Excel



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

Bob Phillips
10-05-2008, 02:39 AM
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

Nunu
10-05-2008, 07:25 AM
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
10-05-2008, 08:01 AM
Thanks for that clarification Nunu, I must admit that it did concern me a little when I saw it.

NanaHawk
10-05-2008, 10:04 AM
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

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
10-05-2008, 12:28 PM
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.