Vlookup then change contents

  • Thread starter Thread starter RDS
  • Start date Start date
R

RDS

Hi,
If I enter a number in sheet 2 A1, i then want to find that number in column
A in sheet 1, then change the data in the cell 10 to the right to the
current time and date.

(Im booking jobs out that are in a database on sheet 1)

Any help appreciated,
Rick
 
Hi Rick
for this you'll need VBA. Put the following code in the worksheet
module of sheet2:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim target_row As Long
Dim wks_target As Worksheet
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
On Error GoTo CleanUp:
With Target
If .Value <> "" Then
Application.EnableEvents = False
Set wks_target = Worksheets("Sheet1")
target_row = Application.WorksheetFunction. _
Match(.Value, wks_target.Range("A1:A100"), 0)
wks_target.Cells(target_row, "C").Value = Now
End If
End With
CleanUp:
Application.EnableEvents = True
End Sub

You may change the target column from 'C' to your requirements
 
Frank Kabel said:
Hi Rick
for this you'll need VBA. Put the following code in the worksheet
module of sheet2:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim target_row As Long
Dim wks_target As Worksheet
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
On Error GoTo CleanUp:
With Target
If .Value <> "" Then
Application.EnableEvents = False
Set wks_target = Worksheets("Sheet1")
target_row = Application.WorksheetFunction. _
Match(.Value, wks_target.Range("A1:A100"), 0)
wks_target.Cells(target_row, "C").Value = Now
End If
End With
CleanUp:
Application.EnableEvents = True
End Sub

You may change the target column from 'C' to your requirements
Click to expand...
Thats precisely what i needed, thanks a bunch.
What would be even more useful would be if i could do this drom an external
workbook, ie book2 changing col C in book1
I had hoped-
Set wks_target = Worksheets("[Book1]Sheet1")
-would do it, but no joy.

Also can it be adapted to run on the changes of a range od cells say A1
through A20?

Many thanks
Rick
 
Hi Rick
if the other workbook is open try
Private Sub Worksheet_Change(ByVal Target As Range)
Dim target_row As Long
Dim wbk_target as workbook
Dim wks_target As Worksheet
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
On Error GoTo CleanUp:
With Target
If .Value <> "" Then
Application.EnableEvents = False
Set wbk_target as workbooks("book1.xls")
Set wks_target = wbk_target.Worksheets("Sheet1")
target_row = Application.WorksheetFunction. _
Match(.Value, wks_target.Range("A1:A100"), 0)
wks_target.Cells(target_row, "C").Value = Now
End If
End With
CleanUp:
Application.EnableEvents = True
End Sub

--
Regards
Frank Kabel
Frankfurt, Germany
Frank Kabel said:
Hi Rick
for this you'll need VBA. Put the following code in the worksheet
module of sheet2:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim target_row As Long
Dim wks_target As Worksheet
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
On Error GoTo CleanUp:
With Target
If .Value <> "" Then
Application.EnableEvents = False
Set wks_target = Worksheets("Sheet1")
target_row = Application.WorksheetFunction. _
Match(.Value, wks_target.Range("A1:A100"), 0)
wks_target.Cells(target_row, "C").Value = Now
End If
End With
CleanUp:
Application.EnableEvents = True
End Sub

You may change the target column from 'C' to your requirements
Click to expand...
Thats precisely what i needed, thanks a bunch.
What would be even more useful would be if i could do this drom an
external workbook, ie book2 changing col C in book1
I had hoped-
Set wks_target = Worksheets("[Book1]Sheet1")
-would do it, but no joy.

Also can it be adapted to run on the changes of a range od cells say
A1 through A20?

Many thanks
Rick
Click to expand...
 
Frank said:
Hi Rick
if the other workbook is open try
Private Sub Worksheet_Change(ByVal Target As Range)
Dim target_row As Long
Dim wbk_target as workbook
Dim wks_target As Worksheet
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
On Error GoTo CleanUp:
With Target
If .Value <> "" Then
Application.EnableEvents = False
Set wbk_target as workbooks("book1.xls")
Set wks_target = wbk_target.Worksheets("Sheet1")
target_row = Application.WorksheetFunction. _
Match(.Value, wks_target.Range("A1:A100"), 0)
wks_target.Cells(target_row, "C").Value = Now
End If
End With
CleanUp:
Application.EnableEvents = True
End Sub
Click to expand...
Hi, that worked a treat, after i changed an 'as' for '=' i guess you're just
making sure i'm paying attention :)

Now can i make this work on target cells A1 through A20, can some sort of
loop be applied?

Many thanks,
Rick
 
Hi Rick
good spot maybe the reason was the time of the day :-). If you want
this apllied for cells A1:A20 just change the line
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub

to
If Intersect(Target, Me.Range("A1:A20")) Is Nothing Then Exit Sub
 
Frank Kabel said:
Hi Rick
good spot maybe the reason was the time of the day :-). If you want
this apllied for cells A1:A20 just change the line
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub

to
If Intersect(Target, Me.Range("A1:A20")) Is Nothing Then Exit Sub
Click to expand...
I assumed it would be more complicated than that, worked a peach.
Thanks again, thats me done for a while.

Rick
 
Back
Top