find value

[xxentric]

im tryign to find a macro that will look in another workbook for value "A" on range A1:A12 and if it finds the value adds +1 to whatever number is already in cell B4 of "ThisWorkbook"

so in cell b4 of Thisworkbook there is a value of 5

macro searches C:\drive\test.xls for "A" in A1:A12 if it finds the value just add +1 to cell B4 making it now 6 instead of 5

 

[PHV]

What have you tried so far and where in your code are you stuck?
Tip: You have to know the difference between ActiveWorkbook and ThisWorkbook.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[xxentric]

well the last time i asked i was linked to another forum by someone and they are trying to have me use the regular CountIf function... but that method is really not working for me...

the problem is there are alot values that need to be checked for, and im not sure the best way to go about this

lets see if i can explain this good...

there is book1 and book2

book1 users will be entering values in cells A1:A12 and these values would be 1 letter per cell, any 12 letters A - Z in any random order

Book2 will have the whole alphabet A - Z in col. A and in column B next to each letter will be a "counter"

when im in need of is a way that once they enter their 12 letters into book1, a macro button will open book2 check the whole alphabet list for what values the user entered and then and +1 to the "counter" next to that letter

so if book2 originally read

A 2
B 5
C 6
D 9

and the user enters letters B and D into book1 and runs the macro

book2 will then look like this

A 2
B 6
C 6
D 10

 

[Loomah]

hi
as well as what skip and phv have asked you would you clarify that the acceptable input to cell a1 could be "abcdefghijkl"?
in which case you want to search book 2 for each of the 12 letters or can your users only input 1 letter in each cell?

also, i've scanned through some of your other posts and if this is the same problem (container ids?) then you should be careful of over simplifying what you require. looking to match a letter of the alphabet in a list i wouldn't (personally) use the find method but i think that is likely to be what you are after!


;-)

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]

A query would work GREAT!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xxentric]

yea those are my posts before... typically i know what i need and can find examples close enough to figure it out myself... but for this particular problem i couldnt find examples of what i needed even though to me what i wanted it to do seemed simple enough, i couldnt find how to do it... so then i turn to the message boards, and try and try explaining it different ways. the last post with the containers.. i couldnt get the countif to work right because it would only find a value if it was in a matching cell in the other workbook... but i need it to search each cell since there will be no particular order the info would be entered.

so then i was told to use an array ctrl+shift+enter... but that only seemed to work 1 time, if the value it was looking for in the other workbook was deleted and reentered, it no longer worked and i had to go back in edit the formula cell and hit ctrl+shift+enter again... thats no good

and yes... each cell would contain just 1 letter (container ID)

 

[SkipVought]

so then i was told to use an array ctrl+shift+enter... but that only seemed to work 1 time, if the value it was looking for in the other workbook was deleted and reentered, it no longer worked and i had to go back in edit the formula cell and hit ctrl+shift+enter again

HUH!?

Au contraire!

Deleting the SOURCE DATA and re-entering does NOT require the array formula to be reentered!!!

Works EVERY TIME!

What are you doing? You seem to have a habit of holding back siginficant facts. What have you not revealed?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xxentric]

well this is what i tried to use... but its just going crazy on me lol... i mean this is returning a value of 12 ... yet there isn't even anything in the cells in the other workbook

=COUNT(IF('C:\Users\FAM7\Desktop\Jared FC Project\[test.xls]Sheet1'!$A$1:$A$12="12-8",1,0))

 

[SkipVought]

Hit F9

Check Tools > Options: Calculation Tab

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Loomah]

methinks this is getting messy in a 'too many cooks' way so backing out ... but ...

still don't get why countif isn't working

COUNTIF([Book1]Sheet1!$A$1:$A$10000,[Book2]Sheet1!A1)

in the example above
[Book1]Sheet1!$A$1:$A$10000 contins IDs you are looking for

in [Book2]Sheet1! (in my example) column A there is a list of all possible values in book1

the formula above goes in book2 B1 and is copied down as far as necessary.

the result : a list of ids with a count of how many times they appear showing right next to them.

couldnt get the countif to work right because it would only find a value if it was in a matching cell in the other workbook

makes it sound as if you haven't entered the formula correctly?

anyway, off to contemplate the fact i only have to suffer my current job for another 8 working days!!

;-)

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?​

 

[xxentric]

.... i may have to give up for now... this is drivin me crazy, i dunno why nothing is working out

WorkBook1 user enters " 12-1 " in cell A1 runs macro.

macro opens WorkBook2, and looks for " 12-1 " in column A

and when found (say it's located in A8) adds +1 to whatever number is in B8.

save....