PDA

View Full Version : Criteria Problem



sooty8
11-20-2008, 07:55 AM
Hi

I cannot get my head around "Criteria" and I have attached
a very simple example that I need help with -- I want to place
a CommandButton on an exsisting UserForm and on Click it will
run a Sub to solve my problem

Many Thanks

Sooty 8

nst1107
11-20-2008, 11:57 AM
Here's a sample of code that will make your example do what I think you are looking for:
Option Base 1
Private Sub CommandButton1_Click()
Dim criteria11(2, 2) As String
Dim criteria3() As String
'Tests first criteria.
i = 1
For x = 1 To Sheet5.UsedRange.Rows.Count
If Sheet5.Range("F" & x) = 11 Then
criteria11(1, i) = Sheet5.Range("B" & x) & " " & Sheet5.Range("A" & x) '<<Little discrepancy between the data _
'in column B of Sheet5 and Sheet12. _
'One sheet has "J." and the other "J. ".
criteria11(2, i) = Sheet5.Range("E" & x)
i = i + 1
End If
If i = 3 Then Exit For
Next
'Tests second criteria.
j = 1
ReDim criteria3(2, 1)
For x = 1 To Sheet12.UsedRange.Rows.Count
If Sheet12.Range("F" & x) = 3 Then
criteria3(1, j) = Sheet12.Range("B" & x) & Sheet12.Range("A" & x)
criteria3(2, j) = Sheet12.Range("E" & x)
j = j + 1
ReDim Preserve criteria3(2, j)
End If
Next
'Compares criterias and writes results.
For i = 1 To j
If criteria3(1, i) = criteria11(1, 1) Then
Sheet14.Range("B13") = criteria11(1, 1)
Sheet14.Range("F13") = criteria11(2, 1)
End If
If criteria3(1, i) = criteria11(1, 2) Then
Sheet14.Range("B14") = criteria11(1, 2)
Sheet14.Range("F14") = criteria11(2, 2)
End If
Next
End Sub

Paste it in the declarations of a userform with a CommandButton1, and it should work for you.

sooty8
11-21-2008, 04:41 AM
Hi Nate

Thanks for the reply and I apologise for the delay in replying been off colour with a bad head cold - your help was invaluable and worked OK however probably because of not feeling to good I missed loads of stuff off what should have been my original full question -- want to work on it over the weekend and then post again - in this post and perhaps you can help me again.

Many Thanks - your help is much appreciated.

Sooty 8