Scripts Thread, Now() copy & paste in Coding and Web Development; I am trying to self teach VBA, but before I have got up to speed a request has come in ...
30th November 2012, 01:23 PM #1
- Rep Power
Now() copy & paste
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.
IDG Tech News
1st December 2012, 01:35 PM #2
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.
5th December 2012, 03:41 PM #3
- Rep Power
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.
5th December 2012, 03:46 PM #4
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.
5th December 2012, 04:16 PM #5
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.
5th December 2012, 04:39 PM #6
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.
Originally Posted by pcstru
8th January 2013, 08:01 PM #7
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).
Originally Posted by LosOjos
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:
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)
By ahuxham in forum Office Software
Last Post: 12th November 2010, 04:15 PM
By synaesthesia in forum Office Software
Last Post: 14th January 2009, 11:48 PM
By localzuk in forum Windows
Last Post: 4th December 2008, 12:23 PM
By mikeymike in forum Windows
Last Post: 6th October 2008, 11:19 AM
By ricki in forum General Chat
Last Post: 1st May 2008, 02:48 PM
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)