Hi Guys,

I've got 2 problems with my vba code.

Description of my spreadsheet : The spreadsheet has 2 worksheets.
Worksheet A has 4 columns (descr, cl, il, and a1). All the columns
except for the descr column contain crosses (x). Worksheet B is a
worksheet where I have to post results (it's an output page).

Problems :
1. I've got to filter the descriptions (column descr) based on the
crosses in the columns cl, il and a1. However, filtering should be
based on the following rules :
Descr A : cl x, il, a1 x -- output because there is a cross in column
cl
Descr B : cl, il, a1 -- not sent to output because there are no
crosses
Descr C : cl, il x, a1 x -- output because there is a cross in columns
il and a1

2. I want to put the output from problem 1 in a single field in
worksheet B. The output will contain a list of descriptions.

Can someone help me to build this VBA code please ?

Floep


--
floep
------------------------------------------------------------------------
floep's Profile: http://www.excelforum.com/member.php...o&userid=36853
View this thread: http://www.excelforum.com/showthread...hreadid=565584

Sub ABC()
Dim cell as Range, rng as Range
Dim s as String
with worksheets("worksheet A")
set rng = .Range(.Cells(2,1),.Cells(rows.count,1).End(xlup))
End with
for each cell in rng
if cell.offset(0,1).Value = "x" or _
cell.offset(0,2).Value = "x" and _
cell.offset(0,3).Value = "x" then
s = s & cell.value & vbNewLine
end if
Next
Worksheets("Worksheet B").Range("B9").Value = s
End Sub

--
Regards,
Tom Ogilvy


"floep" wrote:


Hi Guys,

I've got 2 problems with my vba code.

Description of my spreadsheet : The spreadsheet has 2 worksheets.
Worksheet A has 4 columns (descr, cl, il, and a1). All the columns
except for the descr column contain crosses (x). Worksheet B is a
worksheet where I have to post results (it's an output page).

Problems :
1. I've got to filter the descriptions (column descr) based on the
crosses in the columns cl, il and a1. However, filtering should be
based on the following rules :
Descr A : cl x, il, a1 x -- output because there is a cross in column
cl
Descr B : cl, il, a1 -- not sent to output because there are no
crosses
Descr C : cl, il x, a1 x -- output because there is a cross in columns
il and a1

2. I want to put the output from problem 1 in a single field in
worksheet B. The output will contain a list of descriptions.

Can someone help me to build this VBA code please ?

Floep


--
floep
------------------------------------------------------------------------
floep's Profile: http://www.excelforum.com/member.php...o&userid=36853
View this thread: http://www.excelforum.com/showthread...hreadid=565584

Hi Tom,

Thanks for the script ... it works great. I want to make the
spreadsheet a little bit more advanced though.

These are the columns for worksheet A :
column 1 : item description
column 2 - 5 : type. Each item can be of one or more specific types. A
cross is added if the item belongs to a specific type. There are a
total of 4 possible types.
column 6 - 8 : C. This is a property of the item. There are 3 different
classes of this property. A cross is added if an item has a specific
property.
column 9 - 11 : I. This is a property of the item. There are 3
different classes of this property. A cross is added if an item has a
specific property.
column 12 - 16 : A. This is a property of the item. There are 3
different classes of this property. A cross is added if an item has a
specific property.

The previous script can cope with the filtering in columns 2-5 but I've
got a new problem for columns 6 - 14. The previous script gives me a
list of items for a specific type (eg item 1 (type N), item 2 (type N),
item3 (type N)). These items have different properties based on
parameters.

Eg if the parameters are CL, IM, A3 I have to filter all the crosses in
column 6, 10 and 14. If the parameters are CH, IH, A1 I have to filter
in column 8, 11 and 12. Therefor the final result could have less items
in the output (eg item 1 (type N), item 2 (type N))

Can you integrate this in the script please ?
Floep


--
floep
------------------------------------------------------------------------
floep's Profile: http://www.excelforum.com/member.php...o&userid=36853
View this thread: http://www.excelforum.com/showthread...hreadid=565584

Of course someone else, other than Tom Ogilvy, may also answer this
question :-)

Floep


--
floep
------------------------------------------------------------------------
floep's Profile: http://www.excelforum.com/member.php...o&userid=36853
View this thread: http://www.excelforum.com/showthread...hreadid=565584