List of Names

I had this question after viewing Index Match Vlook up for a master file.

Hi,
I have added some code to inform the user if the part does not exist on the master
For Each cell In rng
    If Application.CountIf(sws.Rows(4), cell.Value) > 0 Then
        col = Application.Match(cell.Value, sws.Rows(4), 0)
        r = Application.Match(cell.Offset(0, 2).Value, sws.Columns(col), 0)
       
        If col > 0 Then
            If Err.number = 13 Then
                cell.Offset(0, 3).Value = "Part not in master"
                Err.Clear
            Else
                 cell.Offset(0, 3).Value = sws.Cells(r, myCodeCol)
            End If
        End If
    End If
Next cell

i have been thinking about the first column and the issue getting all the invoices eventually to one sheet. The problem is that none of them put a column showing it is from them. I don't want the user to type in the company name becuase that would just open the door to problems So how about this.  when the user open the invoice spreadsheet, they will run a vba script , and it will ask who the company is:
ZED .. Maple.  etc.  the available companies  would be based upon ROW 4 in the master sheet in (sheet2). They choose the company and it is copied down in col A to the end of the data . I have run a few manual samples and it works good.  So my question is , how do i get that list to show up in VBA ?
rsernowskiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rsernowskiAuthor Commented:
Oops sorry , forgot to attch the file

in SHeet 1 , Col A2 to end of data, I want to fill that column based upon the data in row 4 in the master tab.  So I guess the user would be presented with a list box  and choose the correct company. I can create drop downs user form based upon data in  a col. But I couldn't find anything to show data based upon row , or a name range?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Roy CoxGroup Finance ManagerCommented:
There is still no attachment
rsernowskiAuthor Commented:
Funny , i thought I attached it, here it is
Index_match-VBA-invoices.xlsm
Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

Roy CoxGroup Finance ManagerCommented:
What is "MyCode"?

I can't tell what you re attempting from your code or the example. You mention invoices but where are they?

Explain clearly what you need and I may be able to help.
rsernowskiAuthor Commented:
Hi

The existing macro matchs their part code to My code. the master shows the part numbers they use and MyCode is the part code I require.  (After the match I will pivot on group and qty and it will show me how many parts have been sold)

When I get monthly or qtrly invoice details from vendors , the details don't have a column showing who they are . So I need some way to add in company name. I suppose I could just match their part code to my part code without worrying about adding a company name? would that be easier to adjust the exiting code that way

If I use existing code , I need a way to add in company name so that the spelling is always the same as what is in row 4!  I  would  want the user to choose a company name based upon the names in the master sheet that are on row 4.  

I tried to  create a  list but list always works on column , not row.

the company name needs to be added prior to running the macro find vendor code, so that is why I thought I would just create two different macros.
Roy CoxGroup Finance ManagerCommented:
How can your code find "MyCode", it does not exist anywhere. If it is a variable then it should be declared and given a value. It should not be be surrounded by " in this case.
rsernowskiAuthor Commented:
Ok

It seems to work as is, if I change it will it solve my problem?
Roy CoxGroup Finance ManagerCommented:
I can't see how it can work. How is it finding MyCode. Remove the error handler an see what happens - you should bnever use error handlers until coding is complete and tested.
rsernowskiAuthor Commented:
the error handler is there to give the user a message in case their part code is not on the master. We are sort of off topic. I wanted to create a list based upon data in row 4
Roy CoxGroup Finance ManagerCommented:
Good luck. I am trying to understand your problem and help, but I cannot see how your code works the way it is written.
rsernowskiAuthor Commented:
Nothing real solutions. please close
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
R

From novice to tech pro — start learning today.