I have 5 "rules" in 5 adjacent cells, for example A1, A2,
A3, A4, A5

Each cell contains a formula which displays "Warning" if a
rule is broken, in the corresponding cell
eg if rule 1 is broken A1 will show "Warning" and if rule
4 is broken A4 will show "Warning"

on the next sheet is a cell which is to show which rules
have been broken. So for the example above, it will show
1&4 to show that rules 1 and 4 have been broken.

What I need is some code that will look at each cell and
see if it says "Warning" then I need to put the rule
numbers in the next sheet and separate them if there is
more than on with a &

so far my code is

dim Rule1, Rule2 etc
Rule1 = sheet1.range("a1")
etc

if Rule1 = "Warning" then
sheet2.range("g3") = "1"
end if
if Rule2 = "Warning" then
sheet2.range("g3") = sheet2.range("g3") & "2"
end if

The problem is separating the numbers with an & an leaving
it out if there is only one.

Any help would be much appreciated

can't you just use COUNTIF

--
Don Guillett
SalesAid Software

"Libby" wrote in message
...
I have 5 "rules" in 5 adjacent cells, for example A1, A2,
A3, A4, A5

Each cell contains a formula which displays "Warning" if a
rule is broken, in the corresponding cell
eg if rule 1 is broken A1 will show "Warning" and if rule
4 is broken A4 will show "Warning"

on the next sheet is a cell which is to show which rules
have been broken. So for the example above, it will show
1&4 to show that rules 1 and 4 have been broken.

What I need is some code that will look at each cell and
see if it says "Warning" then I need to put the rule
numbers in the next sheet and separate them if there is
more than on with a &

so far my code is

dim Rule1, Rule2 etc
Rule1 = sheet1.range("a1")
etc

if Rule1 = "Warning" then
sheet2.range("g3") = "1"
end if
if Rule2 = "Warning" then
sheet2.range("g3") = sheet2.range("g3") & "2"
end if

The problem is separating the numbers with an & an leaving
it out if there is only one.

Any help would be much appreciated

Hi
not tested but try the following

sub foo()
dim source_wks as worksheet
dim target_wks as worksheet
Dim row_index as long
Dim result

set source_wks=worksheets("Sheet1")
set target_wks=activesheet
for row_index = 1 to 5
if source_wks.cells(row_index,"A").value="Warning" then
if result="" then
result=row_index
else
result=result & "-" & row_index
end if
end if
end if

target_wks.range("B1").value=result
end sub

--
Regards
Frank Kabel
Frankfurt, Germany


Libby wrote:
I have 5 "rules" in 5 adjacent cells, for example A1, A2,
A3, A4, A5

Each cell contains a formula which displays "Warning" if a
rule is broken, in the corresponding cell
eg if rule 1 is broken A1 will show "Warning" and if rule
4 is broken A4 will show "Warning"

on the next sheet is a cell which is to show which rules
have been broken. So for the example above, it will show
1&4 to show that rules 1 and 4 have been broken.

What I need is some code that will look at each cell and
see if it says "Warning" then I need to put the rule
numbers in the next sheet and separate them if there is
more than on with a &

so far my code is

dim Rule1, Rule2 etc
Rule1 = sheet1.range("a1")
etc

if Rule1 = "Warning" then
sheet2.range("g3") = "1"
end if
if Rule2 = "Warning" then
sheet2.range("g3") = sheet2.range("g3") & "2"
end if

The problem is separating the numbers with an & an leaving
it out if there is only one.

Any help would be much appreciated