Series of IF conditions

[headache]

Hi guys, first, a big thank to everybody who take the time to reply to beginners (or too old to learn like me!) questions, that's great support and help, cheers!

My issue is linked to a series of IF, so i'd like a code that gives me all references triggering certain conditions.

Conditions definition is in Index tab. Database is DATA tab.

In index tab, the input are the name (B2), the VLW less than (B3), the Sales more than (B4). Once defined the conditions, I would like to run the macros, and get as an output the countries (column A in tab DATA) that trigger the conditions just defined.

I guess it's easier to understand with the example provided.

Any idea???

[CodeNinja]

Hi,
Try the following:


[VBA]Sub test()
Dim sName As String
Dim iVLW As Integer
Dim lSales As Long
Dim l As Long
Dim iResult As Integer

iResult = 8
With Sheets("Index")
sName = .Cells(2, 2)
iVLW = .Cells(3, 2)
isales = .Cells(4, 2)
End With


With Sheets("Data")

For l = 2 To .Range("A" & Rows.Count).End(xlUp).Row
If sName = .Cells(l, 2) And iVLW > .Cells(l, 3) And isales < .Cells(l, 4) Then
Sheets("Index").Cells(iResult, 2) = .Cells(l, 1)
iResult = iResult + 1
End If
Next l
End With
End Sub[/VBA]

[CatDaddy]

[VBA]Sub test()
Dim cell As Range
Dim lr As Long, r As Long, in2 As Long, in3 As Long
Dim in1 As String
ActiveWorkbook.Sheets("INDEX").Activate
in1 = Range("B2").Text
in2 = Range("B3").Value
in3 = Range("B4").Value
ActiveWorkbook.Sheets("DATA").Activate
lr = Range("A" & Rows.Count).End(xlUp).Row
r = 8
For Each cell In Range("B2:B" & lr)
If cell.Text = in1 And cell.Offset(0, 1).Value < in2 And cell.Offset(0, 2).Value > in3 Then
cell.Offset(0, -1).Copy Destination:=Sheets("INDEX").Range("B" & r)
r = r + 1
End If
Next cell
ActiveWorkbook.Sheets("INDEX").Activate
End Sub[/VBA]

[headache]

cheers guys,
Catdaddy, thanks, not sure the code is working - i probably do a mistake somewhere but it doesn't show any result for me.

CodeNinja, thanks, it was definitely working, but I tried to add one string and one long (2 others conditions, one text, one value), and changed a bit the range and the r for the output, and unfortunately, I couldn't get it work!!!

I am sooooo bad in VB!

can you pls have another look? I attach the new file
many thanks again guys!

[CatDaddy]

tested and successful on your posted workbook
[VBA]Sub test()
Dim cell As Range
Dim lr As Long, r As Long
Dim in3 As Double, in4 As Double
Dim in1 As String, in2 As String
Dim in5 As Integer

ActiveWorkbook.Sheets("INDEX").Activate
in1 = Range("B18").Text
in2 = Range("B19").Text
in3 = Range("B20").Value
in4 = Range("B21").Value
in5 = Range("B22").Value
ActiveWorkbook.Sheets("DATA").Activate
lr = Range("A" & Rows.Count).End(xlUp).Row
r = 25
For Each cell In Range("B2:B" & lr)
If cell.Text = in1 And cell.Offset(0, 1).Text = in2 And cell.Offset(0, 2).Value < in3 And _
cell.Offset(0, 3).Value > in4 And cell.Offset(0, 4).Value < in5 Then
cell.Offset(0, -1).Copy Destination:=Sheets("INDEX").Range("B" & r)
r = r + 1
End If
Next cell
ActiveWorkbook.Sheets("INDEX").Activate
End Sub[/VBA]

[CodeNinja]

Headache,
Glad to be of service. A couple of things are slightly off in your code.

First: In your for loop, You need to change all of the .cells(1,#) to .cells(l,#).
I know 1 looks like l, but it is a very fine difference. You have a long variable the letter "EL" and if you mistakenly put a 1 (one) there, it will look for whether cell 1,2 = sName not whatever row you are in the loop on...

Second: iREV is a percentage and needs to be a double instead of an integer.
When you put iREV = 10%, VB reads that as .1. Since you are putting that into an integer, it will read as 0. To be accurate, you should change iREV to a double (and probably change it to dREV)

Third: is REV .1 < -.37?
In your sample provided, you suggest that Monday2 should appear, however, according to what you put in the data, Monday2 REV = -.37 or -37% and you want a return if REV < .1... .1 is not less than -.37, so it will not return true. You need to decide whether you want negative numbers to be converted to positive (you can do this with the abs() function)

This same logic applies to all of the logical operators. I know it is counter intuitive, but you have reversed the <> sign in your calculations.

I hope this has been of help...

To get the results you want, the code should look like this:

[VBA]Sub test()
Dim sName As String
Dim sCategory As String
Dim iREV As Double
Dim lSF As Long
Dim lLOC As Long
Dim l As Long
Dim iResult As Integer

iResult = 25
With Sheets("Index")
sName = .Cells(18, 2)
sCategory = .Cells(19, 2)
iREV = .Cells(20, 2)
iSF = .Cells(21, 2)
iLOC = .Cells(22, 2)

End With


With Sheets("Data")

For l = 2 To .Range("A" & Rows.Count).End(xlUp).Row
If sName = .Cells(l, 2) And sCategory = .Cells(l, 3) And iREV > .Cells(l, 4) And iSF < .Cells(l, 5) And iLOC > .Cells(l, 6) Then
Sheets("Index").Cells(iResult, 2) = .Cells(l, 1)
iResult = iResult + 1
End If
Next l
End With
End Sub[/VBA]

[headache]

sorry guys, i was on leave. It all works very well, many thanks, for the explanations too, very appreciated.
Cheers