VBA Select Case Statement

[jtbrolly]

Trying to make a macro similar to the one you guys helped me with the other day, but I'm getting a Compile error: Statments and labels invalid between Select Case and first Case at the first match.Offset. Can anybody help me out?

Sub Trues()
 
    Dim match
    match = "TRUE"
For Each match In Range("l1:l")
Select Case match
match.Offset(0, -11) = "Matches - Good"
match.Offset(0, -11).Interior.ColorIndex = 4
Case Else
End Select
Next match
End Sub

 

[mikerickson]

If you want to loop though Range("A1:A11") looking from all the cells that contain true, the syntax would be

Dim oneCell As Range

For Each oneCell in Range("A1:A11")
    Select Case oneCell.Value
        Case "True"
            oneCell.Offset(0, 1) = "is good"
        Case Else
    End Selct
Next oneCell

What cell do you want green with "Matches - Good"?
Range("1:11").Offset(0, -11) is not clear, and might be to the left of column A.

 

[jtbrolly]

I'm looking for the Trues in Column L, hence the -11. I want the Matches - Good being written in Column A.

 

[jtbrolly]

Also, not sure if this affects it but the TRUE is a formula result.

I'm looking at the formula results in column L and if the result is a TRUE, I want to write Matches - Good in green in column A. And if it's not TRUE, I don't want it to write anything.

 

[mikerickson]

This should do that.

Dim oneCell As Range

For Each oneCell in Range("L1:L11")
    Select Case oneCell.Value
        Case "True"
            oneCell.EntireRow.Range("A1") = "Match - good"
            oneCell.EntireRow.Range("A1").Interior.ColorIndex = 4
        Case Else
    End Select
Next oneCell

Note that the .Range is relative to oneCell.EntireRow, hense the .Range("A1")

SelectCase is slower than If..ElseIf..End If. And adding a With...End With gives this code:

Dim oneCell As Range

For Each oneCell in Range("L1:L11")
    With oneCell
        If CStr(.Value) = "True"
            With.EntireRow.Range("A1")
                .Value = "Match - good"
                .Interior.ColorIndex = 4
            End With
        End If
    End With
Next oneCell

The CStr is there to protect against there being an error value (e.g. #DIV/0) in the cell. (Never trust what the user might put in a cell.)

 

[jtbrolly]

The If-Then you provided didn't write anything. The Select Case is close:

Dim oneCell As Range
For Each oneCell In Range("L:L")
    Select Case oneCell.Value
        Case "True"
        On Error Resume Next
            oneCell.EntireRow.Range("A1") = "Matches - Good"
            oneCell.EntireRow.Range("A1").Interior.ColorIndex = 4
        Case Else
    End Select
Next oneCell
End Sub

The only problem I ran into is I'm trying to skip over the N/A errors. It skips the FALSE cells, but it's still writing Matches - Good for N/A, which I don't want it to do. I tried putting On Error Resume Next which let it ran without stopping on the N/As, but now it applies it for them.

 

[jtbrolly]

Do you know why the If Then isn't working? It's running without any errors, but nothing happens.

The SelectCase runs but when I tried On Error Resume Next it skipped the N/As but then wrote the Match - Good in the corresponding A cells as well.

Is it possible to skip the N/As without doing anything? I know this sounds bad, but we have recurring N/A errors which we look at after the TRUEs.

 

[mikerickson]

Try changing this

If LCase(CStr(.Value)) = "true"

If you still want the Select Case, try

Select Case CStr(oneCell.Value)

 

[jtbrolly]

The If Then didn't work, but the SelectCase did!

Thank you so much!!!!