code problems

[bluefeather8989]

Sheets("Division").Select
If C3 = 0 Then
Range("E3").Select
    ActiveCell.FormulaR1C1 = "second grade"
Range("E4").Select
    ActiveCell.FormulaR1C1 = "third - fifth"
Range("E5").Select
    ActiveCell.FormulaR1C1 = "middle"
Range("E6").Select
    ActiveCell.FormulaR1C1 = "high"
Range("E7").Select
    ActiveCell.FormulaR1C1 = ""
 Range("E3").Select
Application.ScreenUpdating = True
End If

End Sub

There is something wrong with code I just cant figure it out plz HELP

 

[pgc01]

... as you can see E7 is "" with the vba and is blank with no text.

Hi

As I wrote, this happens when the cell is formatted as text. Maybe you did not format the cell as text before running the statement?

If you run the vba code I posted that's the first thing it does.


Remark: I see that you are using CountBlank() and it does not count just empty cells.

It's important to know that CountBlank() and IsBlank() do not target the same type of cells.

IsBlank() tests if the cell is empty
CountBlank() counts the cells that are empty or have a null string values

This is a simple test that shows this difference.

With A1 and A2 formatted as General do the following

- Select A1 and press delete. This empties A1
- Select A2 and enter the formula: ="". A2 has a formula that results in an empty string.

- B1: =CountBlank(A1)
- C1: =IsBlank(A1)

Copy the formulas in B1:C1 to B2:C2

As you can see in both cases CountBlank() counts 1. Although A2 has a formula CountBlank() still counts it.

The IsBlank(), however, will return False in the case of A2, recognising it's not empty.

<table border="1" cellpadding="1" style="background:#FFF; border-collapse:collapse;border-width:2px;border-color:#CCCCCC;font-family:Arial,Arial; font-size:10pt" ><tr><th style="border-width:1px;border-color:#888888;background:#9CF " > </th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >A</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >B</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >C</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >D</th></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>1</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">1</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:center;border-width: 1px;border-color:#888888; ">TRUE</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>3</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">1</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:center;border-width: 1px;border-color:#888888; ">FALSE</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>4</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td colspan=5 style="background:#9CF; padding-left:1em" > [Book1]Sheet2</td></tr></table>

 

[bluefeather8989]

ok i stand corrected. But if you have it in General format it will work as i have it. With that in mind is there a difference between text and general format?

 

[pgc01]

I'm glad it worked now.

About the formats:

1-
If you have a cell formatted as General, this means that you trust excel to make the right choice about the format of the contents of the cell, and whether or not a conversion is made.

For ex.:

- you enter a number with 12 digits and excel converts it to scientific notation

- you enter 1/7 and excel converts it to a date

In the case we are looking into, the excel developers decided that if you write a null string to a cell formatted as general it will convert it to a variant empty.

2-
If you format the cell with a specific format, you are decide which format you want.

- you format a cell as a number with 0 decimals and even if the number has 15 digits it does not go into scientific notation because you've decided that that's not what you want.

- you format a cell as fraction and if you enter 1/7, excel will interpret it as a fraction, not as a date


In our case, you formatted the cell as text. When you use vba to write the null string to the cell you are not giving excel the choice whether or not to convert the value into another thing. The cell is formatted as text and so a null string is written.