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

mdimiller

New Member
Joined
Nov 5, 2013
Messages
22
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:

Code:
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
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Worf

Well-known Member
Joined
Oct 30, 2011
Messages
4,094
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):

Code:
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

New Member
Joined
Nov 5, 2013
Messages
22
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

Well-known Member
Joined
Oct 30, 2011
Messages
4,094
You are welcome and thanks for the feedback :cool:
 

Watch MrExcel Video

Forum statistics

Threads
1,122,514
Messages
5,596,610
Members
414,080
Latest member
penguin23

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top