Worksheet change to function to add text to cell which initially triggered the change

[mdimiller]

Hello, i have some existing code which is trigerred when anything is input into column c. The code then adds various information in another three columns. One of which pastes a vlookup formulae, and i would like this forumlae pasted into the column c cell which i initialy edited, in order to remove the requirement for one additional column.

The existing code i have is:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim MyText As String
MyText = Environ("username")
If Target.Cells.Column = 3 Then
With Target
If .Value <> "" Then
.Offset(0, 2).Formula = "=VLOOKUP(D:D,'P:\TAOffshore\TAOffshoreTreasuryRecs\General\Commit ID''s for control Sheet - Do not move or delete\[commit ids - DO NOT DELETE OR MOVE.xls]Sheet1'!$A$1:$B$65536,2,0)"
.Offset(0, 3).Value = Format(Now, "MM/DD/YYYY")
.Offset(0, 1).Value = MyText

End If
End With
End If
End Sub

I have tried changing the offset to (0,0) or changing the offset to 'target = ', which does add in the vlookup but then the macro debugs at the 'If .Value <> "" Then' code?

Any suggestions are appreciated

Thanks

Matt

 

[Worf]

Hello Matt
This method will work well if you don´t need to change the same row several times. In this case we should use a flag variable (or cell):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim MyText$, c As Comment


MyText = Environ("username")
Set c = Target.Comment
If Not c Is Nothing Then
    If c.text = "ok" Then Exit Sub  ' cell has formula already
End If
If Target.Cells.Column = 3 Then
    With Target
        If .Value <> "" Then
            .AddComment.text "ok"
            .Formula = "=VLOOKUP(D:D,'P:\TAOffshore\TAOffshoreTreasuryRecs\General\Commit ID''s for control Sheet - Do not move or delete\[commit ids - DO NOT DELETE OR MOVE.xls]Sheet1'!$A$1:$B$65536,2,0)"
            .Offset(0, 2).Value = Format(Now, "MM/DD/YYYY")
            .Offset(0, 1).Value = MyText
        End If
    End With
End If
End Sub

 

[mdimiller]

Hi Worf, thanks for the response and apoligies in tkaing time to come back.

And yes that does work great thanks for your time.

 

[Worf]

You are welcome and thanks for the feedback