No solution - Macros

[LiAD]

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?

 

[jigsawsthoughts]

Does this help?

If Application.WorksheetFunction.IsNumber(Range("A2").Value) Then
MsgBox "Yes"
End If

Cheers
Julie

 

[Rick Rothstein]

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.

 

[LiAD]

Hi thanks very much for your help.

I can't get that to run for some reason, it doesn't like the cell ref.

The macro I'm using is:

Sub Man()
' gsnuxx
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 Not IsEmpty(Cells(2, j)) Then simbol = "W"
If Not IsEmpty(Cells(3, j)) Then simbol = "WS"
If Not IsEmpty(Cells(4, j)) Then simbol = "OW"
If Not IsEmpty(Cells(5, j)) Then simbol = "WM"
If Not IsEmpty(Cells(6, j)) Then simbol = "Wa"

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

For k = 1 To times
Cells(23, k + n- 1).Value = simbol
Next
Next
End Sub

For info the table I have looks something like

A 1 5
B 2 3
C 1

The macro (should then if i remove all the IFs) generate a horizontal text
string such as W WS WS W W W W W WS WS WS OW. In each coumn there is only
one numerical value and the cells that appear as empty are in fact filled
with the IF(cell>0;cell;""). If I copy paste as values it doesn't like that
either. The macro generates a string of constant values.

Any ideas?

Thanks a million

 

[LiAD]

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

Thanks

 

[SeanC UK]

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.

 

[LiAD]

Sorry if its not clear.

I will try your code and see if it works then we'll take it from there.

Is your code supposed to replace all of my previous code? If so I am
getting a compile error at the moment with it.

Thanks for your help
LD

 

[SeanC UK]

No, sorry, that was just one line to replace your lines:

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

Using IIF in such circumstances can use less code, that's all.

The rest of your code should remain, but my comments might help you to
understand what your code IS doing, rather than what it is supposed to be
doing.

If you are only having problems with the lines:

If Not IsEmpty(Cells(2, j)) Then simbol = "W"
If Not IsEmpty(Cells(3, j)) Then simbol = "WS"
If Not IsEmpty(Cells(4, j)) Then simbol = "OW"
If Not IsEmpty(Cells(5, j)) Then simbol = "WM"
If Not IsEmpty(Cells(6, j)) Then simbol = "Wa"

not finding "" cells, then replace with:

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"

But read my other comments as you still might find that you have undesired
charachters as you never set simbol = "No Empty Cells Characters", so a full
column (one containg 5 cells with numbers) will show one of your simbol
values from the previous column repeated MAX times.

Sean.

 

[LiAD]

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(cell>0;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 – I’ve
tried and I can’t find a way.
2- use a macro that can deal with the not really empty cells. I’m not
familiar with VBA hence this one is way past my design skills. The code I’m
using came from some-else.

That’s seemed like a long description so I hope it helped!
Thanks again for helping

 

[SeanC UK]

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.

 

[Rick Rothstein]

This description is better, but you still have left something unclear;
mainly, I think, because it sounds like you are mixing your description of
what you have with how you think you need to code the macro... we just need
to know what you want the final end result to look like (don't tell us how
you think you need to get there, just tell us what the final end result
needs to be). Here is some of the points that I am still unclear on.

Do you actually need the text string "W W W WS WS OW OW..." or is that
something you think you need to get to your final destination (you used the
phrase "I think" when describing it)?

What gets colored? Something in the table (if so, what)? Or the codes in the
text string (assuming you actually need the text string)?

Are you replacing something in the table itself with those one and two
letter codes?

 

[LiAD]

Hi,

Thanks for the replies, sorry i've not been around for few days. I am
seeing that I have an idea of what I need just not getting down the info you
guys need, plus I'm confusing what I need with want and what I started with.
Heres goes:-

What I want is to go from the input table you have seen to a range of
coloured cells. The coloured cells go horizontal, left to right, one colour
per cell. The number of times a colour repeats is dependent on the entries
in the table. So using the code you have quoted (W W W WS WS OW OW.....) it
would go something like, 3 blue cells, then 2 yellow, 2 red....... I repeat
all I need is to go from the data table to a multicoloured cell range.

The colour is applied to the text string W W..etc, not the table. The
reason I generate the eventually useless text string is becuase originally I
had thought the easiest way was to use a conditional format. However then my
ranges got expanded past the limit of four and I found the macro empty cell
issue. It is also a useful check to make sure its as its supposed to be.

The data table never changes.

Hope this is more useful and fixes the last few points.

 

[LiAD]

Thanks very much,

now its flying. works perfectly.

Thanks a million.

 

[Rick Rothstein]

This macro is how I would do it. I've made it easy to modify if conditions change... just set the various conditions in the five Const statements (the color list is just a space delimited list of color indexes) and the code will take care of the rest.

Sub ConvertDataToColors()
Dim C As Range
Dim X As Long
Dim LastRow As Long
Dim LastColumn As Long
Dim ColorStartColumn As Long
Dim NameAddresses As String
Dim Colors() As String

Const NameStartRow As Long = 2
Const NameEndRow As Long = 6
Const ColorDisplayRow As Long = 23
Const SheetName As String = "Sheet2"
Const NameColorIndexes As String = "3 10 5 7 4"

Colors = Split(NameColorIndexes)
With Worksheets(SheetName)
LastRow = .Range("A:A").Find(What:="*", _
SearchDirection:=xlPrevious).Row
NameAddresses = NameStartRow & ":" & NameEndRow
LastColumn = .Range(NameAddresses).Find(What:="*", _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column
Set C = .Range(NameAddresses).Find(What:="*", _
After:=Range("A" & NameEndRow), _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext)
If Not C Is Nothing And C.Column <> 1 Then
ColorStartColumn = 1
.Range(ColorDisplayRow & ":" & ColorDisplayRow).ClearFormats
Do
For X = ColorStartColumn To ColorStartColumn + C.Value - 1
.Cells(ColorDisplayRow, X).Interior.ColorIndex = _
Colors(C.Row - NameStartRow)
Next
ColorStartColumn = ColorStartColumn + C.Value
Set C = .Range(NameAddresses).FindNext(C)
Loop While C.Column <> 1
End If
End With
End Sub

 

[Larry]

-- I created a form in 2004, when I open it in 2007 3 to 5 rows of cell
highlight when I clicked in on ecell only. Any suggestions?
Thank you,
Larry

 

[CurlyDave]

-- I created a form in 2004, when I open it in 2007 3 to 5 rows of cell
highlight when I clicked in on ecell only.  Any suggestions?
Thank you,
Larry

Larry,
You may get help on this if you started your own thread, instead of
Hijacking others. Also responding to replies in your own threads lets
others know if the question has been answered.

 

[Bob Umlas]

unmerge the cell(s)

-- I created a form in 2004, when I open it in 2007 3 to 5 rows of cell
highlight when I clicked in on ecell only. Any suggestions?
Thank you,
Larry

Larry,
You may get help on this if you started your own thread, instead of
Hijacking others. Also responding to replies in your own threads lets
others know if the question has been answered.