CODEWITHSUNDEEP
vbaexcel
Christian M
Christian M

Reputation: 235

EXCEL/VBA: converting boolean text to checks

I've exported data from access to excel.
Some columns where boolean values, and excel displays them as "true" or "false".
Not very readable, so I want to change the false values to blank, and the true values to "x".

Here is the VBA code I wrote:

Sub BoolToCheck()
Dim myRow As Integer
Dim myCol As Integer

For myCol = 7 To 38
    For myRow = 2 To 458

    Select Case Cells(myRow, myCol)
        Case "TRUE"
         Cells(myRow, myCol) = "x"
        Case "FALSE"
         Cells(myRow, myCol) = ""
    End Select

    DoEvents
    Next myRow
Next myCol
End Sub

but it doesn't work; when I'm running it step by step, neither case happens.
tried switching from "true" and "false" to true and false, but it didn't change anything.

Anyone knows why this isn't working ? I don't get it...

Upvotes: 2

Views: 3418

Answers (3)

Dick Kusleika
Dick Kusleika

Reputation: 33145

Sub MarkTrue()

    Dim rCell As Range

    For Each rCell In ActiveSheet.Range("G2:AR458").Cells
        If rCell.Value = True Then
            rCell.Value = "X"
        ElseIf rCell.Value = False Then
            rCell.ClearContents
        End If
    Next rCell

End Sub

Another method using For Each and no Select Case, both of which should be faster. Although I'll defer to Charles Williams whether that's true.

Upvotes: 2

Charles Williams
Charles Williams

Reputation: 23520

Using .Text is dangerous because it reads the formatted value of the cell, which might be ### if the user has reduced the column widths.
True and False are handled as Boolean values rather than text/strings so this code works:

Select Case ActiveSheet.Cells(j, k)
    Case True
       ActiveSheet.Cells(j, k) = "x"
    Case False
       ActiveSheet.Cells(j, k) = ""
 End Select

Upvotes: 3

Christian M
Christian M

Reputation: 235

changed from 

Select Case Cells(myRow, myCol)

to

Select Case Cells(myRow, myCol).text

works now.

Upvotes: 0

Related Questions