Macros and empty cells




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
n = Cells(25, Columns.Count).End(xlToLeft).Column + 1
End If

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


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question