CODEWITHSUNDEEP
vbaexcelselectcase
N. Volchko
N. Volchko

Reputation: 33

VBA Select Case Runtime Error 13

I am trying to run a select case for when a cell is changed in a range to run a macro when specific text is entered. I have this working for a single cell (and the code that follows) for "F50", but when I try to use the same style of code a few lines later, it doesn't work.

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("F50")) Is Nothing Then
Select Case Range("F50")
    Case "MPR-9A": Resize9
    Case "MPR-8A": Resize8
    Case "MPR-6A": Resize6
    Case "MPR-3A": Resize3
End Select
End If

If Not Intersect(Target, Range("F4:F45")) Is Nothing Then
Select Case Range("F4:F45")
    Case "M-20A": M20A
    Case "M-2X20A": M2X20A
    Case "M-20A-SP": M20ASP
End Select
End If

End Sub

I am trying to have this code run when I put a new value somewhere in the range of F4:F45. When that value is entered, the case checks for which macro to run. The macros copy a range on another sheet and paste it to the cell that just triggered the macro (which also includes a few cells to the right and below), and those macros run fine when I fire them manually.

I went the copy/paste route because I gave up on plan A, which was trying to merge the cell that just received the new value with 1 or 3 below it and then enter text into the cells just to the right. If I should return to trying that route I'm more than happy to try and avoid the copy and paste.

Upvotes: 2

Views: 437

Answers (2)

R3uK
R3uK

Reputation: 14537

You can use Range("F4:F45").Cells(1,1) which will in facts be F4 cell.

But I guess that the thing you want to test is Target.Value, the value of the cell that changed :

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub

If Not Intersect(Target, Range("F50")) Is Nothing Then
Select Case Range("F50")
    Case "MPR-9A": Resize9
    Case "MPR-8A": Resize8
    Case "MPR-6A": Resize6
    Case "MPR-3A": Resize3
End Select
End If

If Not Intersect(Target, Range("F4:F45")) Is Nothing Then
Select Case Target.value
    Case "M-20A": M20A
    Case "M-2X20A": M2X20A
    Case "M-20A-SP": M20ASP
End Select
End If

End Sub

Upvotes: -1

SierraOscar
SierraOscar

Reputation: 17637

Use this for your second block instead if you just want to check the existence of those strings:

If Not Intersect(Target, Range("F4:F45")) Is Nothing Then
tempStr = Join([TRANSPOSE[F4:F45)]," ")
    Select Case True
        Case InStr("M-20A", tempStr): M20A
        Case InStr("M-2X20A", tempStr): M2X20A
        Case InStr("M-20A-SP", tempStr): M20ASP
    End Select
End If

If you want to check each cell individually, then you will have to loop through that range:

If Not Intersect(Target, Range("F4:F45")) Is Nothing Then
    For Each cell In Range("F4:F45").Cells
        Select Case cell.Value
            Case "M-20A": M20A
            Case "M-2X20A": M2X20A
            Case "M-20A-SP": M20ASP
        End Select
    Next
End If

Upvotes: 2

Related Questions