Now() copy & paste

[theglenmen]

I am trying to self teach VBA, but before I have got up to speed a request has come in for a script and I am not quite getting it right.

 

We are using a barcode scanner, which then populates then next blank cell in column A - as this populates there are some lookups, and a Now() formula to insert the date and time of the scan - however, this obviously then changes each time a new scan takes place. Therefore I need a macro that when cell A* is populated, it then selects the date and time and pastes as values so that it is not overridden.

 

I think it should be a fairly simple code for you more experienced VBA users to do the select, copy paste and loop it - so if anyone can advise while I continue trying to work it out I would be grateful for the help.

[pcstru]

I'm curious how the barcode scanner populates the next blank row. It suggests that code would be a good candidate to modify to populate the current date/time into the appropriate column.

[theglenmen]

I'm not a programmer, more of a front end data user to I don't know the exact background of it - but the context of it is each student has a barcode in their planner, printed from SIMS that is their Admission number. The scanner reads this and inserts ~000000, then automatically drops to the cell below (assuming you haven't moved the selected cell in between).

The process works fine in our library for speeding up the process of logging books in and out, but now that they want to use it for students arriving late in the morning a date and time stamp is vital and manually typing them in doesn't make the process much quicker than just scribbling them down.

[LosOjos]

Sounds like the spreadsheet is interfacing with the scanner via VBA. Open up the VB editor in Excel (Ctrl+F11) with the spreadsheet open and take a look. It's going to be much simpler to edit that code than try to patch something else on top of it. Have to say though, if you don't really understand what's happening yet, it's going to be difficult for anybody to help you without seeing how it works for themselves.

[pcstru]

Or it could be as simple as pressing return and excel moves the cursor down, but code needs to be checked.

 

If I were stating from scratch, I think I'd look at the Worksheet_change event, which is triggered by any change, to any cell. That would allow the barcode reader to trigger an insert of the date/time. It will all get a bit messy if people mistreat it - one of the disadvantages of trying to use excel as a database.

[LosOjos]

Or it could be as simple as pressing return and excel moves the cursor down, but code needs to be checked.

 

Either way, if the OP doesn't know what's happening, it's going to be damn near impossible for anyone to help without seeing it first hand.

[howartp]

Either way, if the OP doesn't know what's happening, it's going to be damn near impossible for anyone to help without seeing it first hand.

The barcode scanners we used a couple of years ago simply behaved as a keyboard; it reads a number, types it into the active cell, then presses enter (itself), which moves the cursor to the next row (assuming Excel is set to do this).

 

This matches up with what the OP is saying, so I propose we go on that basis. The operator simply scans barcode after barcode after barcode and ends up with a list of student IDs as follows:

 

012345

373482

272489

etc.

 

I've not used events in VBA so I can't help with this aspect of it, but once someone comes up with the right event, getting the VBA to insert date/time into ActiveCell(R[-1],C[+1]) should be easy enough. (Maybe worksheet_change as has been suggested, but I don't know what options there are)

 

Peter