Place Bets VBA

[jrarmstrong]

Hi,

I have written a simple macro to insert text (a "1") into the bet ref column to use the CANCEL-ALL trigger in the Place Bets sheet. When the conditions are met the macro fires but the CPU goes crazy making my PC run slowly.

Presumably this is because my code wants to keep inserting a 1 into the bet ref column and the existing code wants to clear it?

I had hoped adding the following line into the existing code would sort this out but I just get a "Type Mismatch" Runtime Error 13 message. Can anyone see where I am going wrong?

If Cells(R, 17) = "1" Then Exit Sub

Thanks,

James

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim selecRow As Integer
    If Target.Columns.Count = 16 Then
        Application.EnableEvents = False
        For R = 5 To 55
            If Cells(R, 17) = "1" Then Exit Sub       
              If Cells(R, 28) > 0 Then
                If Cells(R, 20) <> "" And Cells(R, 20) <> "PENDING" Then
                    selecRow = Cells(R, 28) + 1
                    If ThisWorkbook.Sheets("Selections").Cells(selecRow, 10) = "" Then
                        ThisWorkbook.Sheets("Selections").Cells(selecRow, 10) = Cells(R, 20)
                        Cells(R, 20) = ""
                    End If
                End If
            End If
        Next
        Application.EnableEvents = True
    End If
End Sub

Code: Select all

[jrarmstrong]

Hi,

I've been struggling to resolve this error. The following is intended to achieve the same result but still just gives me the same error message. Can anyone point out what is wrong?

Thanks,

James

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
Dim selecRow As Integer
If Target.Columns.Count = 16 Then
    If Range("Q5").Value <> "CANCEL" Then
        Application.EnableEvents = False
        For R = 5 To 55
            If Cells(R, 28) > 0 Then
                If Cells(R, 20) <> "" And Cells(R, 20) <> "PENDING" Then
                    selecRow = Cells(R, 28) + 1
                    If ThisWorkbook.Sheets("Stock").Cells(selecRow, 10) = "" Then
                        ThisWorkbook.Sheets("Stock").Cells(selecRow, 10) = Cells(R, 20)
                        Cells(R, 20) = ""
                    End If
                End If
            End If
        Next
        Application.EnableEvents = True
    Else
        Call Cancel
    End If
End If
End Sub

[alrodopial]

The "" that you are inserting with the

Code: Select all

Cells(R, 20) = ""

is different than an empty cell.
Use the

Code: Select all

Cells(R, 20).ClearContents

[jrarmstrong]

Thanks alrodopial I'll try it out.

James