creating a counter 1

[xxentric]

ok... lets try this forum... i seem to be having trouble explaining what i need.

I have a workbook "book1" where users will be entering ID's of containers. when they enter it will look like so

12-1
15-5
30-2... and so on (12 maximum for now)

I have another workbook "book2" that im trying to use to keep track of container usage so each time the container hits 10 uses we will know and can clean it.

so the other workbook will have the list of ALL the containers we have and then a count on times it was used

12-1 5
12-2 10
12-3 2


im not sure how to go about this... but each time a user enters the ID's of the containers they are currently using in book1 i need a button macro for it to update the usage count for that container in book2 by +1 ... when a user enters the container ID's there wont be any kind of order, so it needs to somehow find the corresponding container ID on the big list and then update the usage count

any takers?

 

[SkipVought]

Hi,

book1 MUST contain EVERY ID that was ever used to obtain a count.

boo2 is merely a query of book1 sheet1 data doing a count...

Select ID, Count(*) From [Sheet1$]

assuming that is the only data in sheet1 and assuming that column As heading is ID.

faq68-5829.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[xxentric]

arg... crushed again... perhaps this isnt possible lol... im being told to find a way to do it without having to save every ID ever used, mainly because this list would become way too big....

im not een sure how at all, somehow open another workbook and update the count by 1 then save with the updated count ... *slams head on desk*

 

[SkipVought]

You could, every so often, purge every ID that is store an exact multiple of 10.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Loomah]

i really must be missing something here as i don't really see why countif can't be utilised?

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[SkipVought]

Sure, COUNTIF is just another option, but it requres ALL the occurrences of ID.

Could use a PivotTable also.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[xxentric]

wait... now im confused... your saying count if needs ALL the occurrences... doesnt the method you gave me need the same?

 

[SkipVought]

Yes, there are many ways to skin a cat.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[xxentric]

im not sure what i want is possible without vba coding but they told me to ask here....

book1 is open, you enter the Id's... a macro opens Book2 (or with it closed if possible) looks for the ID's that were entered in Book1 and increase the number in the cell next to them (which would contain the number of times we used it) by +1

or maybe im doing it backwards and i need to have book2 read specified cells in book1 for each ID, and it if finds a matching ID, increase the count (in the adjacent cell) by +1

 

[xxentric]

after that it can just save book2 with the updated count .... really i dont even need it to keep track of all the times we used it... just increase a number in a cell by +1 .... the part i cant figure out is how to make it find the correct cell to update

 

[SkipVought]

How would you know what you counted previously and what you have never counted?

Best and accepted practice seldom stores aggregations, but calculates at report time.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[xxentric]

hmm... if this is the case im guessing it not possible ...?

im not actually trying to keep track of all the data... just in the simplest terms say when this macro is run, if you find "this value" in (this range of cells)...that A1 should now = whatever is currently in A1 + 1

 

[SkipVought]

if you find "this value"

So what is going to tell it that "this value" was already counted, when you run the macro the NEXT time?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[xxentric]

If the macro was run it found "this value" and then changed A1 from 1 to 2 and then was saved... next time it opens that workbook and the macro runs, does it need to know if it ran it before? couldnt it just increase A1 by 1 again and make it 3?

most likely there is something that is needed to do this i just don't understand i guess...

 

[SkipVought]

The issue is not that the macro ran before.


The issue is that "this value" may have been counted before or not. How can you know?



Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[xxentric]

but thats fine... count it again... we will be reusing these things over and over... if you find "this value" it adds +1 to the count... its now 2 instead of 1 ... then save... the count is now at 2... next time the workbook opens and that macro is run if it finds "this value" add +1 again... so now the count is at 3

and "this value" would only be showing up 1 time each time it searches so it could only ever find it 1 time each time

if it finds ID "12-1" in cells A1:A12 add +1 to how many times "12-1" was used save...

 

[SkipVought]

Ok.

First a list of unique values in column A of the workbook that will contain the macro.

Column B has a COUNTIF that counts in the other workbook.

Column C has the running count, populated by...

sub NewCounts()
   dim r as range

   for each r in range([A1], [A1].end(xldown))
     with cells(r.row, 3)
        .value = .value + .offset(0,-1).value
     end with
   next
end sub

Copy into a new Module in the VB Editor (alt+F11 toggles between sheet and editor)

Run from Tools > Macro > Macros

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[xxentric]

AH that looks like it could be extremely useful! thank you very much i can't wait to try this...

sorry about all that, like i stated on the very, ive had a hell of a time explaining exactly what i need ... i sure i was making it wayyy harder then it is