Need a little VBA assistance..

[spdavis]

I have a Excel sheet. with the following basic lay out....

[TABLE="width: 786"]
<tbody>[TR]
[TD="width: 87, align: center"] A[/TD]
[TD="width: 123, align: center"] B[/TD]
[TD="width: 145, align: center"]D
[/TD]
[TD="width: 145, align: center"] E[/TD]
[TD="width: 88, align: center"] F[/TD]
[TD="width: 145, align: center"] G[/TD]
[TD="width: 123, align: center"] I[/TD]
[TD="width: 64, align: center"] J[/TD]
[/TR]
[TR]
[TD] 0002841124[/TD]
[TD="align: center"] Jane Doe[/TD]
[TD="align: center"] 0002841109[/TD]
[TD="align: center"] 08 March 2016[/TD]
[TD="align: center"] 22:07:53[/TD]
[TD="align: center"] Class 73[/TD]
[TD="align: center"] Jane Doe[/TD]
[TD="align: center"] 0[/TD]
[/TR]
</tbody>[/TABLE]

To explain. Column 'A' is an input from an RFID card reader. Column 'B' a name assigned to that card. This is my dynamic lookup table.

Column 'D' is now the Card being used by person assigned. Column 'E' gives the date. 'F' the time.
Where 'D' is is used to create 'E' and 'F' by a basic VBA script (below)..

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 4 Then
Cells(Target.Row, 5).Value = Date
Cells(Target.Row, 6).Value = Time
Beep
End If
End Sub

However, I have no idea as to how I can copy into 'G' the name assigned to that card. And as other cards get used, they follow the same process in column format.

At the moment I have a macro =IF(D2="";"";VLOOKUP($D2;$A:$B;2;FALSE)) This reads input from 'D' and looks up a match in 'A' to return name from 'B'. Otherwise show nothing in a call. But this is a dynamic requirement on a manual copy and past of the macro and not what I want happening.

Ideally this would be a VBA solution. I have no idea about.

Column 'I' is meant to mirror column 'B' as it's populated. Or show an empty cell if nothing is found in 'B'..
=IF(D2="";"";VLOOKUP($D2;$A:$B;2;FALSE))

Column 'J' counts the number of times name uses card in the day. Or show nothing if no card is used ..
=IF(D2="";"";VLOOKUP($D2;$A:$B;2;FALSE))

Yes a tad odd in terms of logic. But sort of works. I just need to know to either convert this to VBA or lock the macro from being deleted as they are developed dynamically.

Can any body offer ideas please?
Thanks for any help and time on my behalf.

 

[Meggesto]

If you're already running a Macro to insert the Date and Time into columns could you add a line that would also insert the formulas you need.

For Example, something like.
Cells(Target.Row, 7).Formula = "=IF(D2="","",VLOOKUP($D2;$A:$B;2;FALSE))"

This would insert that formula into the row each time you run the Macro and you wouldn't have to worry about the formula being deleted from the worksheet.

 

[spdavis]

If you're already running a Macro to insert the Date and Time into columns could you add a line that would also insert the formulas you need.

For Example, something like.
Cells(Target.Row, 7).Formula = "=IF(D2="","",VLOOKUP($D2;$A:$B;2;FALSE))"

This would insert that formula into the row each time you run the Macro and you wouldn't have to worry about the formula being deleted from the worksheet.

I did so like this option.. Ty

But using Excel 2003, this... Cells(Target.Row, 7).Formula = "=IF(D2="","",VLOOKUP($D2;$A:$B;2;FALSE))"

Becomes... Cells(Target.Row, 7);Formula = "=IF(D2="";"";VLOOKUP($D2;$A:$B;2;FALSE))"
(which is merely swapping the , for ; )

When run. The macro enters date and time correctly, but places the =IF element into a cell as text (it self). It also doen't travel down as other cards are used.. This said. I really do see future potential in this snippit of education, once I can get this current pain sorted !!!

 

[Meggesto]

Would something like this possibly help?

If Not IsEmpty(Range("D2").Value) Then

Cells(Target.Row, 7).Value = Application.WorksheetFunction. _
VLookup(Target.Value, Range("$A:$H"), 2, False)

End If

I'm just not sure how the Target Range is generated to make this completely dynamic; I think something like this may work. If you're refreshing the spreadsheet manually place it in the code you're currently running or you could link it to a Change Event and have it run when something is updated...