jeudi 30 août 2018

VBA Conditional Statement with Target.Cells.CountLarge Not Working

Desired Behaviour:

I want to change the value of a cell if and only if a cell current value of another cell matches some constant string value (only alpha characters) I have chosen.

Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    With ActiveWindow

        Dim sht As Worksheet
        Set sht = ThisWorkbook.Sheets("Sheet1")

        Dim selection As Long
        selection = Target.Cells.CountLarge
        Dim someString As String
        someString= "something"



        If selection = 1 Then
            If (Target.Column = 4 And Target.Value = someString And IsEmpty(Target) =False) Then
                thisrow = Target.Row
                sht.Cells(thisrow, 5).Value = "N/A"
            End If
        End If


    End With
End Sub

Question: Is there a better way to handle this? The first if statement is necessary to ensure one cell is selected and to avoid error message type mismatch.

Aucun commentaire:

Enregistrer un commentaire