VBA Help

[ashani]

Hi,

I'm using the following coding to bring date to the next column. However, I'd also like that if J21:J22 has N/A then it should show N/A in column K rather than a date.

If Not Intersect(Target, Range("J21:J22")) Is Nothing Then
If Target.Count > 1 Then Exit Sub
ActiveSheet.Unprotect Password:="test"
Cells(Target.Row, "K").Value = Format(Now, "dd/mm/yyyy")
ActiveSheet.Protect Password:="test"

THank you

 

[My Aswer Is This]

How is this script activated? Is this a module script or a sheet change event script?
Show us the entire script

 

[ashani]

hi @My Aswer Is This

Thank you for the reply.

Here's the full script :

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("J21:J22")) Is Nothing Then
If Target.Count > 1 Then Exit Sub
ActiveSheet.Unprotect Password:="test"
Cells(Target.Row, "K").Value = Format(Now, "dd/mm/yyyy")
ActiveSheet.Protect Password:="test"
End if

end sub

 

[Fluff]

How about

If Not Intersect(Target, Range("J21:J22")) Is Nothing Then
If Target.Count > 1 Then Exit Sub
ActiveSheet.Unprotect Password:="test"
If Target.Value = "N/A" Then Target.Offset(, 1).Value = "N/A"
Target.Offset(, 1).Value = Format(Date, "dd/mm/yyyy")
ActiveSheet.Protect Password:="test"

 

[ashani]

Thanks @Fluff

It's not working for some reason

 

[Fluff]

In what way?

 

[My Aswer Is This]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  11/27/2021  11:18:25 AM  EST
If Not Intersect(Target, Range("J21:J22")) Is Nothing Then
If Target.Count > 1 Then Exit Sub
ActiveSheet.Unprotect Password:="test"

Select Case Target.Value
    Case "N/A"
        Cells(Target.Row, "K").Value = "NA"
    
    Case Else
        Cells(Target.Row, "K").Value = Format(Now, "dd/mm/yyyy")
ActiveSheet.Protect Password:="test"
End Select
End If

End Sub

 

[AlphaFrog]

If the values in J21:J22 are a result of formulas, the Worksheet Change event procedure does not trigger on formula calculations. It only triggers off of manual changes to the sheet.

You could trigger off of the cells you do change, but test the results of the formulas in J21:J22. In the example below, the code triggers off of changes in I21:I22, tests the result in the corresponding J column, then writes the Date or #N/A to column K.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("I21:I22")) Is Nothing Then
        ActiveSheet.Unprotect Password:="test"
        If Application.IsNA(Cells(Target.Row, "J").Value) Then
            Cells(Target.Row, "K").Value = CVErr(xlErrNA)
        Else
            Cells(Target.Row, "K").Value = Date
            Cells(Target.Row, "K").NumberFormat = "dd/mm/yyyy"
        End If
        ActiveSheet.Protect Password:="test"
    End If
End Sub

 

[ashani]

Thank you for your help everyone. Really appreciate that.

 

[Fluff]

Glad we could help.