How to change value of the active cell when clicked using VBA, and troubleshooting advice-CodePudding

New to VBA...

I've spent a couple hours on this code but am not even sure whether it's running or has errors, or whether the variables are correct. Followed steps online to check what my variables are, like typing ?variable in the immediate window, checking the locals window, and hovering my mouse over the variable, but nothing comes up. Nothing happens regardless when I go back to the workbook. I believe I could get it working if I at least knew how to find out what's wrong with it. Here's a screenshot:

Included a screenshot because I know the problem might not be just with the code itself, but also with my incompetence using VBA... Here's the code written out:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.address = "C5:I5" Then
        Dim row As Integer
        row = Application.WorksheetFunction.Match(ActiveCell.Offset(0, -1).Value, Range("$n$1:$n$365"), 0)
        Dim address As Long
        address = Application.WorksheetFunction.address(row, 15)
        Range(address).Value = Range(address).Value   1
        ActiveCell.Value = Range(address).Value
    End If
End Sub

It's purpose is just to add 1 to the value of the active cell when clicked. The cell's value will change based on the date in the cell directly above it; the value needs to be tied to the date. I plan to accomplish this using a hidden array of ascending dates and values, located at n1:o365.

CodePudding user response：

(a) Probably your intention is to check if the target cell is within the range "C5:I5" - what your checking is if target has the address "C5:I5" so the if fails. Use for example the function Intersect for that

(b) (Minor thing) Declare row as Long

(c) There is no .WorksheetFunction.address function. A Range has an Address property, eg Target.Address. Note that this will return a String, not a Long. But you don't need this anyhow. Use Cells if you know row and column of a cell.

Note that I haven't checked your logic to find the correct row.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    If Intersect(Target, Range("C5:I5")) Is Nothing Then Exit Sub

    Dim row As Long
    On Error Resume Next
    row = Application.WorksheetFunction.Match(Target.Offset(0, -1).Value, Range("$n$1:$n$365"), 0)
    On Error GoTo 0
    If row = 0 Then Exit Sub    ' Row not found

    Dim cell As Range
    Set cell = Cells(row, 15)
    cell.Value = cell.Value   1
    Target.Value = cell.Value
End Sub