I believe this is a formula issue and not a VBA issue.
On my XREF sheet there is a button to run a macro lookup. top box is input box.
Input number is any entry in Column A.
Results should be posted in the STX terminal Box.
so A75 should return C75.
It is working perfectly unless the Countif is 0.
Examples: Cell A75, Cell A442
Columns with formulas are columns B and G
This is a bit confusing. I am rerally not sure which sheet I am meant to be looking at!! My first impression, however, is that your helper in column B is incorrect and it should read (B2):
=IF(A2="","",A2&COUNTIF($A$2:A2,A2))
and your formula in G1 should be:
=IFNA(VLOOKUP($E$1&ROWS(E$1:E1),$B:$C,2,0),"---")
However, as I am not sure which sheet I'm supposed to be looking at (and your sample sheet is MASSIVE... far more thna the 10-20 rows we want to see) and you didn't really tell us what you expected to see.... this is all a bit of a guess.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
Ok, let me take out some rows for you.
I dont know how to further explain what is needed.. it is all right there in the description.
Let's see if this explains it better.
Sheet XREF
Cell E1 is where the Input Value is going (if you dont want to use the Form Control macro "Number Lookup")
Column A has the number to input
Column B is doing the Countif for the numbers in column A
Column C are the matching values.
Column G are all the returned results.
Examples that are not returning a value to column G are: A14, A20, A8
Can anybody help with this???
To repeat:
=A2&COUNTIF($A$2:A2,A2)
and
=IFNA(VLOOKUP($E$1&ROWS(E$1:E1),$B:$C,2,0),"---")
seem to be the correct formulae. Did you try them? If they do not work, why not?
The formulae that you were using seemed to be using the wrong ranges...
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks