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

 

[hiker95]

bluefeather8989,


Try this:

If Range("C3") = 0 Then

Or this:

If [C3] = 0 Then

And, try:

Range("E3") = "second grade"

 

[pgc01]

Hi

You don't usually select objects in vba.

You can do it, for ex. like this:

With Worksheets("Division")
    If .Range("C3").Value = 0 Then
        .Range("E3").Value = "second grade"
        .Range("E4").Value = "third - fifth"
        .Range("E5").Value = "middle"
        .Range("E6").Value = "high"
        .Range("E7").Clear
    End If
End With

 

[bluefeather8989]

bluefeather8989,


Try this:

If Range("C3") = 0 Then

Or this:

If [C3] = 0 Then

And, try:

Range("E3") = "second grade"

OMG. I feel so stupid I don't know how i miss that one [ ].
Thanks

 

[hiker95]

bluefeather8989,

You are very welcome.

Glad I could help.

Come back anytime.

 

[bluefeather8989]

Hi

You don't usually select objects in vba.

You can do it, for ex. like this:

With Worksheets("Division")
    If .Range("C3").Value = 0 Then
        .Range("E3").Value = "second grade"
        .Range("E4").Value = "third - fifth"
        .Range("E5").Value = "middle"
        .Range("E6").Value = "high"
        .Range("E7").Clear
    End If
End With

this will not work with the "." before Range.
Second when you use "clear" this will clear all info in that cell including Format, and conditional formatting. as far as i know using "" will clear text only.
just thought i should let you know. Live and learn.

 

[pgc01]

Hi bluefeather8989

this will not work with the "." before Range.

Yes it does.

That's the way to indicate that the method/property refers to the object in the With construct.

Ex.

Sub Test()
 
With Worksheets("Sheet2")
    .Range("B3") = "CDE"
    Range("A1") = "ABC"
    .Rows(2).Hidden = True
End With
 
End Sub

This code sets the value for B3 and hides row 2 in the worksheet Sheet2. This is what the "." is telling excel, you call it qualify the object.

The Range("A1"), however, is not qualified. This means that it will write the value in whatever the worksheet is active at the moment of the execution of the code.

As you see, qualifying the object may help you to clarify the code.

Second when you use "clear" this will clear all info in that cell including Format, and conditional formatting. as far as i know using "" will clear text only.

You are right. I chose to clear everything.

The Range object has many clear methods:

Clear
ClearComments
ClearContents
ClearFormats
ClearHyperlinks
ClearNotes
ClearOutline

You can choose the one more adequate to your needs.

In this case, since it seems that you only want to clear the contents of the cell, use the ClearContents method.

Note that if you use:

Range("A1").Value = ""

This will not necessarily clear the contents of the cell. For ex., format A1 as text and then execute the statement.

Now in other cells use the formulas

=ISBLANK(A1) ' returns False, the cell is not empty
=ISTEXT(A1) ' returns true, the cell has a text value

This means that you have not cleared the cell. The cell has a null string.

Repeat it, still with the cell formatted as text:

Range("A1").ClearContents

You'll see that now the cell has really no contents, IsBlank() returns True, the cell is empty, and IsText() returns False, no text value.

The cell now has really be cleared.


You can also use vba to do the test. Just execute this code and it will show you the difference.

Sub x()
 
Range("A1").NumberFormat = "@" ' Sets the cell format to Text
 
' The option you used:
Range("A1") = ""
 
MsgBox _
    "After executing: Range(""A1"") = """"" & vbNewLine & _
    "A1 is " & IIf(IsEmpty(Range("A1")), "empty", "not empty") & vbNewLine & _
    "A1 is " & IIf(VarType(Range("A1").Value) = vbString, "", "not ") & "a text value"
 
Range("A1").ClearContents
 
MsgBox _
    "After executing: Range(""A1"").ClearContents" & vbNewLine & _
    "A1 is " & IIf(IsEmpty(Range("A1")), "empty", "not empty") & vbNewLine & _
    "A1 is " & IIf(VarType(Range("A1").Value) = vbString, "", "not ") & "a text value"
End Sub

These are small details, but it's good to know, so that we can decide exactly what we want.

Live and learn.

That's a good motto.

 

[bluefeather8989]

wow! great info pgc01. but when i put in that code with the "." it wold not let me run it until i got rid of the "." (excel 2007) . again thank for the great info.

 

[bluefeather8989]

ok with that in mind how would i use that in a formula say

Excel Workbook
	A	B
1	1	TRUE
2		0
Sheet1

Excel Workbook
	A	B
1
2		1
Sheet1

so this work only in a formula but not in vba?

 

[bluefeather8989]

Excel Workbook
	E	F	G	H
7			1	FALSE
Division

Range("E7") = ""

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