PDA

View Full Version : Excel VBA to Find Q. for "PARRY'



sswcharlie
26-12-2010, 07:55 PM
Is Parry on this list who has a New Zealand site on Excel ?

Have following question for him, or anyone for that matter.


I am a model railroader and looking to have rfid used on the layout. Where each item has a rfid tag etc
These tags will be read from different location to id where they are. (as a train passes the rfid antennae in each location) Each tag number is transferred to Excel with the antennae number that was use.


Just been looking at Parry's page on VBA Find methods for last row etc.

I want to have several identical sheets (values are different)

Each sheet will be dynamic ranged .

Firstly I want to run a code that deletes all ref in all sheets of the code just transmitted to sheet 1. At this point the id number will not exist in any of the sheets.
Then I want to run a code to Find the value in Sheet1 A1 and place it in a selected sheet (from the ref in Sheet1 B1. Can I use the Named Range reference to do this. Basically to copy sheet 1 A1 and place it in 'Location4' named range. That is I do not have to go to Sheet named 'Location4' and then find the last row to enter the reference. VLOOKUP will then add the balance of info required, eg type of equipment, boxcar, passenger car, etc

e.g.

In sheet 1 I have A1 and B1
A1 is a reference Number (changes regularly after previous reference deleted)
B1 is the sheet reference which value in A1 has to be transferred to. One of several different sheets.

Other sheets: all have named range (same name as tab?)



In plain English:

A rfid reference comes into A1 together with a location id in B1
A 'event' macro is activated:

To lookup all sheets (except sheet1) and delete all reference to the previous entry (probably in a different location.)

Then take the value still in A1 and insert into the sheet that is showing in B1. By using named range

We now have the item listed in the new location that it is at.

Then delete the reference in sheet 1 A1 and B1 ready for the next entry.

Then run a sort of all the id sheets to place in new order and at the same time delete any empty rows.

Note: rfid readings will come in from all antennae (say up to 30) at max of 10 per second.




With the find button you have to put in the actual reference how do I get it to look at, in VBA, the current value of a specific cell, A1 for instance ? something like : select.current sheet(A;1).value

However I may end up with more than 1 entry in A1, while Excel is catching up)


Wow I hope I have not confused too much with this description, but I would like any comments on which way for me to head. Do I use named ranges, or some other way, etc.

Charlie
New Zealand

andrew93
06-01-2011, 10:38 PM
Hi

Yes Parry is the same person at this forum.

Have you solved this problem yet?

Does this have to be in real time in Excel? Could you not collect the rfid data in a batch file and process the information in batches later on?

Just a thought......I didn't read your question too closely because it was too long sorry.

Andrew