Macros and empty cells

[LiAD]

Hi,

I am using the macro below to turn a table into a text string. Example:

A 1 4
B 2
C 1

Into A B B C A A A. I am then using a conditional format to colour the
cells according to the letter inside so A is red, B green etc.

The table above is generated by an =if(cell>0;cell;â€â€) from another
worksheet to avoid zeros in the table. However the macro does not like cells
with the IF
formula and no number. So for example in the above table if the formula is
in the first column against B and the cell is empty the macro will not work.
The cell must either have a number or be completely empty. The only way it
works is if I go through by hand and delete the formula from the empty cells
then run the macro.

Is it possible to adapt the macro or use some other automatic way to do this
delete operation for me? The tables are quite big and will get changed quite
often so the hand delete is time consuming.

Sub Machine1()
' gsnuxx
For j = 2 To Columns.Count
Set r = Range(Cells(9, j), Cells(12, j))
If Application.WorksheetFunction.CountA(r) = 0 Then
Exit Sub
End If
times = Application.WorksheetFunction.Max(r)
If Not IsEmpty(Cells(9, j)) Then simbol = "R"
If Not IsEmpty(Cells(10, j)) Then simbol = "L"
If Not IsEmpty(Cells(11, j)) Then simbol = "W"
If Not IsEmpty(Cells(12, j)) Then simbol = "N"

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

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


Thanks

 

[Gary''s Student]

Why:
A B B C A A A
why not:
A B B C A A A A

 

[LiAD]

Sorry your right, I didnt count properly.

Why:
A B B C A A A
why not:
A B B C A A A A