Thanks very much,

now its flying. works perfectly.

Thanks a million.

"SeanC UK" wrote:

OK, so try this, I've add colours based on the ColourIndex property of the
Cells.Interior, you might well wish to amend the colours, I was random in my
choice.

This is not the best solution, it is still possible that you will run out of
columns and so on, but it is based upon your original solution, so you shoud
easily be able to follow it. I have incorperated a check that (times 0) so
you will only affect changes when the column contains a number somewhere, but
I am guessing from your datatable that there will always be a number in one
of the rows. There would also be better ways of selecting the simbol/colour
than using multiple If statements, but, again, for simplicity I kept it as
you had it.


Public Sub Man()
For j = 2 To Columns.Count
Set r = Range(Cells(2, j), Cells(6, j))
If Application.WorksheetFunction.CountA(r) = 0 Then
Exit Sub
End If
times = Application.WorksheetFunction.Max(r)
If times 0 Then
If Cells(2, j) < "" Then simbol = "W"
If Cells(3, j) < "" Then simbol = "WS"
If Cells(4, j) < "" Then simbol = "OW"
If Cells(5, j) < "" Then simbol = "WM"
If Cells(6, j) < "" Then simbol = "Wa"

If Cells(2, j) < "" Then MyColour = 3
If Cells(3, j) < "" Then MyColour = 10
If Cells(4, j) < "" Then MyColour = 5
If Cells(5, j) < "" Then MyColour = 7
If Cells(6, j) < "" Then MyColour = 4

n = IIf(IsEmpty(Cells(23, 3)), 3, Cells(23,
Columns.Count).End(xlToLeft).Column + 1)

For k = 1 To times
Cells(23, k + n - 1).Value = simbol
Cells(23, k + n - 1).Interior.ColorIndex = MyColour
Cells(23, k + n - 1).Interior.Pattern = xlSolid
Next
End If
Next
End Sub

That should work as you expect, although you will probably find that some
lines have wrapped over, so you will have to delete a line break here and
there so that the code works (like the n= line, it should all be on one line
ending with the +1) ).


I hope this makes sense, and you can see why you were having problems
(mainly with the IsEmpty command)

Sean.



--
(please remember to click yes if replies you receive are helpful to you)


"LiAD" wrote:

An attempted description for what I am hoping to get.

I have a table, assume for whatever reason is displayed as below with the
empty box at the top left starting in cell A2. In each box in the table I
have an formula IF(cell0;cell;€�€�), all the cells in another worksheet. Each
column will only ever have one numerical value and every column will have a
numerical value. The purpose of this is just to take the non-zero values
from another worksheet. My table has five rows and a lot but not huge number
of columns of data at the moment so below is a cut down version.

B C D E .......
Fred 3 .....
Bill 2 1 ...
Ben 4 ....

I then want excel to turn the above table into a text string from which I
can apply an auto colour format to produce effectively a multicoloured
horizontal line.

Excel scans column B, sess that there are 3 entries against Fred and
produces a text that repeats three times in row 23. It then looks in col C
and sees two entries against bill, so it adds two entries of the code
corresponding to bill after the code for Fred, and so on. In order to
produce the text string I ask excel to assign a code for each name, so using
the same format as I was using before Fred is assigned - W, Bill - WS, Ben €“
OW. I need to generate the text string, (I think), so I can get the colour
format to run on auto as well. So the job of the macro is to turn the above
table into

W W W WS WS OW OW OW OW WS€¦€¦and so on (one item per cell)

I then tell excel if the text in the cell is W make it red, WS make it green
etc etc. I will not have an issue, (yet anyway), with the number of cols in
excel.

The problem
As you say the cells that do not display a number are not empty so the macro
will produce a continous one letter string. For the example above the macro
will produce Wa Wa Wa Wa Wa€¦€¦.(10 times €“ the same number of entries in the
table). So the macro is not dealing with non zero cells correctly.

The solution

1 €“ find a way to generate the table correctly with just numbers €“ Ive
tried and I cant find a way.
2- use a macro that can deal with the not really empty cells. Im not
familiar with VBA hence this one is way past my design skills. The code Im
using came from some-else.

Thats seemed like a long description so I hope it helped!
Thanks again for helping


"SeanC UK" wrote:

Hi LiAD

I agree with Rick, even though you have given your problem and the macro you
have created, it is not entirely clear what you are trying to achieve. This
is what I see:

You are checking columns from 2 to the end of the worksheet, rows 2 to 6
inclusive. You test to see if each range r (current column, rows 2 to 6)
contains anything (COUNTA) which will result in 0 unless all 5 cells are
empty (I presume you are using this to stop the marco when it comes to the
end of the populated data, in which case there are probably better ways to do
this).

For each populated column you then calculate the MAX number (times), which
you use to repeatedly populate your text string.

Next you try to determine which characters you are to use (simbol) for the
string. I think there is a potential flaw here. You are using a hierarhcical
test, so that the last empty row in your If Not IsEmpty set of statements
will set 'simbol'. If row 3 is empty and row 5 is empty then simbol will
represent the empty row 5. I presume this is your intention. However, if your
results from your cell formulae result in "" then IsEmpty will return FALSE.
If you want cells containing "" to represent an Empty cell then you should
test by using something like:
IF Cells(1,j) = "" Then simbol = ""
Also, you never reset simbol! If all the cells in the current column contain
a number then simbol will still contain the value it had from the previous
test, and will be repeated 'times' number of times in your text string.

Finally, depending on the number of populated columns and the size of the
numbers stored in the cells being tested, it is possible that you are going
to run out of columns to store your text string! For instance, if you are
using Excel prior to version 12 then you'll have 256 available columns. If
the you have 64 columns populated and the maximum number in most of them is
5, then you will not have enough columns to contain your simbol characters.

Here's a tiny piece of code to reduce your code:
n = IIf(IsEmpty(Cells(23, 3)), 3, Cells(23,
Columns.Count).End(xlToLeft).Column + 1)

Although it is not entirely clear what your problem is, I hope my
description of your code might enable you to solve it. If this didn't help
then please give more information. You have tried to explain what you want to
happen, but you haven't really described the problem, as in what IS
happening. I imagine, when you say that the macro cannot successfully read
the table due to the "", you mean that it sees all cells as being populated
(IsEmpty = FALSE), which is what I would expect as a formula returning "" is
not an empty cell.

If you're still having problems after reading this then please post again
(post again even if you're successful, make us happy :) ).

Good luck,

Sean.




--
(please remember to click yes if replies you receive are helpful to you)


"LiAD" wrote:

I have attached an example table and the macro in reply to jigsawthoughts.

Thanks

"Rick Rothstein" wrote:

Your question is not entirely clear. What do you mean by "read the table"?
What did you want to do after you "read the table"? Also, showing us the
macro would help too.

--
Rick (MVP - Excel)


"LiAD" wrote in message
...
Good Morning,

I have posted a question several times regarding using a macro to read a
table but without any lucky answers so far. Inisde the table, in every is
an
IF formula which sometimes returns nothing ("") or a number (5,6 etc).
The
macro cannot successfully read this table due to the "" and I can't find
anything to use so that it just reads the numbers.

Am I correct in thinking a solution does not exist for this problem?