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?

Does this help?

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

Cheers
Julie

On Jan 30, 4:26*pm, LiAD wrote:
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?

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(cell0;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

" wrote:

Does this help?

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

Cheers
Julie

On Jan 30, 4:26 pm, LiAD wrote:
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?

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?

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?

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?