Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macros and empty cells
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(cell0;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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
skip cells with zero values in chart (cells not empty) | Charts and Charting in Excel | |||
Excel - Autom. Filter "Empty / Non Empty cells" should come first | Excel Discussion (Misc queries) | |||
When I SUM cells & 1 is empty I need the result to be empty not 0 | Excel Discussion (Misc queries) | |||
How can I convert empty strings to empty cells? | Excel Discussion (Misc queries) | |||
how do populate empty cells with the contents of populated cells . | Excel Discussion (Misc queries) |