CODEWITHSUNDEEP
vbaexcel
Laurie Wax
Laurie Wax

Reputation: 11

Using Case Is with Named Column

I cant get this (simple) VBA formula to work (in Excel 2010). I want the formula to evaluate cell B2. If B2 is blank, I want C2 to be blank. If B2 > 0, I want C2 to populate with the word "success". Then I want the program to evaluate B3, B4, B5, etc (through B20) using the same logic as above. In other words, for each Bx, I want one of two cases. Case 1) a blank value in Bx generates a blank in the corresponding Cx or Case 2) a value greater than 0 in Bx generates the word "success" in the corresponding Cx. When I run the macro, I don't get an error message, but nothing happens in column C. The debugger tells me the problem is in the Comment.Name = "Comment" line but I cant figure out what's wrong. thanks! 

Sub AutoComplete()
    Dim Comment As Range
    Set Comment = Range("C2:C20")
    Comment.Name = "Comment"
    For i = 2 To 20
        Select Case Range("B" & i).Value
            Case Is > 0
                StartDate = "Success"
            Case Is = " "
                StartDate = " "
        End Select
    Next i

End Sub

Upvotes: 0

Views: 52

Answers (1)

Paul Kelly
Paul Kelly

Reputation: 985

You're not writing anything to column C. You're putting a value in the variable StartDate but not doing anything with it. You're also not doing anything with the Comment range. The following will write to Column C

Range("C" & i).Value = "Success"

Upvotes: 2

Related Questions